mysql json类型查询对象和数组等格式

前沿 and 背景

mysql5.7之后就推出了一种json类型的数据结构，其实json我们在很多场景下都会用到，因为json是数据格式是可变性的，比如用户拓展信息就可以方到json里面，因为个人和企业保存的字段可能会不同如营业执照、经营场所等。废话有点多开始主题，大家在后端开发的时候相信查询json格式比较多，像插入和更新不需要json相关函数的直接组装好json字符串操作就好了，本章主要讲解查询类的。

查询简单单层json对象

create table rule
(
  id           int auto_increment comment '自增ID'
    primary key,
  rule_key     varchar(60)                         null comment '规则key',
  rule_value   json                                null comment '规则值',
)
  comment '规则表';

ok后面都围绕着这张表讲解

首先我们插入数据INSERT INTO partyAccount.rule (id, rule_key, rule_value) VALUES (1, 'age', '{"value": 20}');
JSON_CONTAINS(field, value, path)

第一个参数是字段名，第二个参数是查找的值，第三个参数是查找路径

案列一：现在我们想查询年龄是25岁的sql怎么写呢这里有两种方式，一种是使用JSON_CONTAINS函数还一种是使用缩写->$.

JSON_CONTAINS第一种方式，

select *
from rule
where rule_key = 'age' and json_contains(rule_value, '25','$.value');

JSON_CONTAINS() 
语法： JSON_CONTAINS(json_doc, val[, path]) 
说明： 
返回0或者1来表示目标JSON文本中是否包含特定值，或者JSON文本的指定路径下是否包含特定值。
以下情况将返回NULL: 
目标JSON文本或者特定值为NULl
指定路径非目标JSON文本下的路径

json_extract第二种方式:

select * from rule where rule_key='age'
and json_extract(rule_value,'$.value')=25;

JSON_EXTRACT() 
语法： JSON_EXTRACT(json_doc, path[, path] ...) 
说明： 
返回json_doc中与path参数相匹配的数据。当有参数为NULl或者文本中未找到指定path时将返回NULL。当参数不合法时将报错。
返回结果包含所有与path匹配的值。如果返回多个值，则将自动包装为数组，其顺序为匹配顺序；相反则返回单个匹配值。
MySQL5.7.9及之后的版本将支持’->’操作符作为本函数两个参数时的便捷写法。->左边的参数为JSON数据的列名而不是一个表达式，其右边参数JSON数据中的某个路径表达式。详细使用方法将在文末详细阐述。 

第二种缩写->$.方式：

select * from rule where rule_value->'$.value'=25;

案例二：查询姓名是张三的数据

INSERT INTO partyAccount.rule (id, rule_key, rule_value) VALUES (3, 'name', '{"value": "张三"}');

select *
from rule
where rule_key='name' and rule_value->'$.value'='张三';

查询复杂嵌套json对象

INSERT INTO rule (id, rule_key, rule_value) VALUES (4, 'address', '{"province": {"city": "北京市"}}');

INSERT INTO rule (id, rule_key, rule_value) VALUES (4, 'user', '{"info": {"id_no": "2313156151111"}}');

案例一：查询城市是北京市的数据

select *
from rule
where rule_key = 'address'
  and json_extract(rule_value,'$.province.city')='北京市';