MySQL：json字段查询：数组、对象、成员检查

环境准备

mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

建表

CREATE TABLE `tb_book` (
  `id` int primary key AUTO_INCREMENT COMMENT '主键id',
  `title` varchar(20) not null COMMENT '书名',
  `persons` json not null COMMENT '主要人物列表',
  `attrs` json not null COMMENT '属性列表',
  `info` json not null COMMENT '详细信息'
) ENGINE=InnoDB COMMENT='图书表';

测试数据

insert into tb_book (title, persons, attrs, info) values (
'红楼梦',
'["贾宝玉", "林黛玉"]', 
'[{"label": "朝代", "value": "清代"}]', 
'{"author": "曹雪芹"}'
);

insert into tb_book (title, persons, attrs, info) values (
'水浒传',
'["宋江", "李逵"]', 
'[{"label": "朝代", "value": "宋代"}]', 
'{"author": "施耐庵"}'
);

insert into tb_book (title, persons, attrs, info) values (
'三国演义',
'["曹操", "孙权", "刘备"]', 
'[{"label": "朝代", "value": "汉代"}]', 
'{"author": "罗贯中"}'
);

insert into tb_book (title, persons, attrs, info) values (
'西游记',
'["孙悟空", "唐僧", "猪八戒", "沙悟净"]', 
'[{"label": "朝代", "value": "明代"}]', 
'{"author": "吴承恩"}'
);

查看测试数据

mysql> select * from tb_book;
+----+--------------+--------------+---------+-----------+
| id | title  | persons  | attrs  | info  |
+----+--------------+--------------+---------+-----------+
|  1 | 红楼梦  | ["贾宝玉", "林黛玉"]| [{"label": "朝代", "value": "清代"}]| {"author": "曹雪芹"}|
|  2 | 水浒传  | ["宋江", "李逵"]| [{"label": "朝代", "value": "宋代"}]| {"author": "施耐庵"}|
|  3 | 三国演义| ["曹操", "孙权", "刘备"] | [{"label": "朝代", "value": "汉代"}]| {"author": "罗贯中"}|
|  4 | 西游记  | ["孙悟空", "唐僧", "猪八戒", "沙悟净"]| [{"label": "朝代", "value": "明代"}] | {"author": "吴承恩"}|
+----+--------------+--------------+---------+-----------+

查询示例

对象查询

mysql> select id, title, info  from tb_book where info->'$.author' ='吴承恩';

# 或者
mysql> select id, title, info  from tb_book where JSON_EXTRACT(info, '$.author') ='吴承恩';

+----+-----------+-------------------------+
| id | title     | info                    |
+----+-----------+-------------------------+
|  4 | 西游记    | {"author": "吴承恩"}    |
+----+-----------+-------------------------+
1 row in set (0.00 sec)

普通列表查询

mysql> select id, title, persons from tb_book where '宋江' MEMBER OF(persons);
+----+-----------+----------------------+
| id | title     | persons              |
+----+-----------+----------------------+
|  2 | 水浒传    | ["宋江", "李逵"]     |
+----+-----------+----------------------+
1 row in set (0.00 sec)

对象列表查询

mysql> select id, title, attrs from tb_book where JSON_CONTAINS(attrs, JSON_OBJECT('value', '汉代'));
+----+--------------+------------------------------------------+
| id | title        | attrs                                    |
+----+--------------+------------------------------------------+
|  3 | 三国演义     | [{"label": "朝代", "value": "汉代"}]     |
+----+--------------+------------------------------------------+
1 row in set (0.00 sec)

参考
MySQL：JSON字段List成员检查
mysql json类型数据查询