MySQL查询JSON字段匹配

1、数据准备

-- 创建表
CREATE TABLE `t_json_demo` (
  `f_id` INT NOT NULL AUTO_INCREMENT,
  `f_arrays` JSON NULL,
  `f_object` JSON NULL,
  PRIMARY KEY (`f_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- 插入模拟数据
insert into t_json_demo (f_arrays, f_object) values 
(JSON_ARRAY("1", "2", "3", "4"), JSON_OBJECT("name", "Anna", "age", 16)),
(JSON_ARRAY("2", "4", "5", "6"), JSON_OBJECT("name", "Bob", "age", 17)),
(JSON_ARRAY("9", "6"), JSON_OBJECT("name", "Candy", "age", 18)),
(JSON_ARRAY("11", "22", "33", 'Elden "Lord"'), JSON_OBJECT("name", "Davied", "age", 20)),
(JSON_ARRAY("99"), JSON_OBJECT("name", "Eric", "age", 19));

数据库表和数据都已经准备好了，分别是数组类型的JSON和对象类型的JSON

1. JSON_EXTRACT(column, path)

左侧参数column是列名，右侧参数path是 JSON 路径（字符串文字）

数组类型JSON查询：

select * from t_json_demo where JSON_EXTRACT(f_arrays, '$[0]') = '1';

2. column->path 和 column->>path

这是一种写法增强，用法与JSON_EXTRACT(column, path)一致
column->path 相当于 JSON_EXTRACT(column, path)，其查询结果存在引号和转义
column->>path 则相当于 JSON_UNQUOTE(JSON_EXTRACT(column, path))，也相当于 JSON_UNQUOTE(column->path)，查询结果中去除了引号和其转义

数组类型JSON查询：

select * from t_json_demo where f_arrays -> '$[1]' = '22';

对象类型JSON查询

select * from t_json_demo where f_object -> '$.age' > 18;

 

上面我们说到column->path查询结果会存在转义的情况，那么是不是column->>path这种方式就不会转义了呢？
下面执行两条查询语句对比一下

 

select f_arrays -> '$[3]' from t_json_demo;

 

select f_arrays ->> '$[3]' from t_json_demo;

 

由此可见两种查询的区别，如果是需要将查询出结果导出到Excel文件的需求场景，那么肯定不希望查询的结果中存在转义字符，则可以使用第二种写法。

参考文章：

https://blog.csdn.net/Jorkey_go/article/details/125322706