Mysql-5.7-json测试

/* 
create table json_table  (
id        int unsigned primary key auto_increment comment '主键',
json_info json comment 'json数据',
json_id   int generated always as (json_info -> '$.id') comment 'json数据的虚拟字段',
index json_info_id_idx (json_id)
) comment 'json示例表';
*/
CREATE TABLE `json_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `json_info` json DEFAULT NULL COMMENT 'json数据',
  `json_id` int(11) GENERATED ALWAYS AS (json_extract(`json_info`,'$.id')) VIRTUAL COMMENT 'json数据的虚拟字段',
  PRIMARY KEY (`id`),
  KEY `json_info_id_idx` (`json_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='json示例表';

insert into json_table(json_info) values ('{"id": 1, "name": "张三", "age": 18, "sister": [{"name": "张大姐", "age": 30}, {"name": "张二姐", "age": 20}]}');
insert into json_table(json_info)
values (JSON_OBJECT('id', 2, 'name', '李四', 'age', 18, 'sister', JSON_ARRAY(JSON_OBJECT('name', '李大姐', 'age', 28), JSON_OBJECT('name', '李二姐', 'age', 25))));
insert into json_table(json_info)
values ('{"id": 3, "name": "小明", "age": 18, "sister": [{"name": "小明大姐", "age": 25, "friend": [{"name": "大姐朋友一", "age": 25}, {"name": "大姐朋友二", "age": 25}]}, {"name": "小明二姐", "age": 20, "friend": [{"name": "二姐朋友一", "age": 22}, {"name": "二姐朋友二", "age": 21}]}]}');
select * from json_table\G

# 基于虚拟id查询:json_id
select * from json_table where json_id=2;

# 基于json字段查询
select * from json_table where json_info -> '$.name' = '李四';

# JSON_TYPE 函数判断JSON数据类型
select JSON_TYPE(json_info)               as info_type,
       JSON_TYPE(json_info -> '$.age')    as age_type,
       JSON_TYPE(json_info -> '$.name')   as name_type,
       JSON_TYPE(json_info -> '$.sister') as sister_type
from json_table;

# 查询姓名以及他们的年龄(>=5.7.9支持->和->>)
select json_info -> '$.name' as name, json_info -> '$.age' as age from json_table;
select json_info -> '$**.name' as name, json_info -> '$**.age' as age from json_table;

# -> 等价于 JSON_EXTRACT(column, path)
select JSON_EXTRACT(json_info, '$.name') as name, JSON_EXTRACT(json_info, '$.age') as age from json_table;

# 去掉双引号(->>)
select json_info ->> '$.name' as name, json_info -> '$.age' as age from json_table;

# ->> 等价于 JSON_UNQUOTE(JSON_EXTRACT(column, path))
select JSON_UNQUOTE(JSON_EXTRACT(json_info, '$.name')) as name, JSON_EXTRACT(json_info, '$.age') as age from json_table;

# 查询姓名和他们的姐姐
select json_info ->> '$.name' as name, json_info ->> '$.sister[*].name' as sisters from json_table;

# 查询姓名和他们的姐姐以及朋友
select json_info ->> '$.name' as name, json_info ->> '$.sister[*].name' as sisters, json_info -> '$**.friend[*].name' as friends from json_table;

# 查询key
# JSON_KEYS(json_doc[, path])
select JSON_KEYS(json_info) from json_table;
select JSON_KEYS(json_info, '$.sister[1]') from json_table;

# 查询名字是否存在
# JSON_CONTAINS(target, candidate[, path])
select JSON_CONTAINS(json_info, '"张三"', '$.name') from json_table;

# 查询是否包含路径
# JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
# one 至少存在一个路径
select JSON_CONTAINS_PATH(json_info,'one', '$.name', '$.xxx') from json_table;

# all 所有路径都存在
select JSON_CONTAINS_PATH(json_info,'all', '$.name', '$**.friend') from json_table;

# 查询字符串的路径
# JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
# one 只匹配第一个
select JSON_SEARCH(json_info,'one', '张三') from json_table;
select JSON_SEARCH(json_info,'one', '%朋友%', null, '$**.name') from json_table;

# all 找到所有的路径
select JSON_SEARCH(json_info,'all', '%朋友_', '朋', '$**.name') from json_table;