MySQL json类型操作

一、方法罗列

分类         函数        描述

1. 创建json

json_array  创建json数组

json_object 创建json对象

json_quote  将json转为json字符串类型

2. 查询json

json_contains  判断是否包含某个json值

json_contains_path 判断某个路径是否包含json值

json_extract  提取json值

column->path  json_extract 的简写，mysql 5.7.9 开始支持

json_keys 提取json中的键值为json数组

json_search 按给定字符串关键字搜索json，返回匹配的路劲

3.修改json

json_append 废弃，mysql 5.7.9开始改名为json_array_append

json_array_append 末尾添加数组元素，如果原有值的数值或json对象，则转成数组后，再添加元素

json_array_insert 插入数组元素

json_insert 插入值（插入新值，但不替换已经存在的旧值）

json_merge 合并json数组或对象

json_remove 删除json数据

json_replace 替换值（只替换已存在的旧值）

json_unquote  去除json字符串的引号，将值转成string类型

4. 返回json属性

json_depth  返回json文档的最大深度

json_length 返回json文档的长度

json_type 返回值的类型

json_valid 判断是否为合法json文档

二、使用举例

MySQL 5.7.8开始支持json类型

create table t (

　　id  int,

　　js  json,

　　primary key(id)　

)

插入数据

insert into t values(1,'{"a":1,"s":"abc"}')
insert into t values(2,'[1,2,{"a":123}]')
insert into t values(3,'"str"')
insert into t values(4,'123')

直接提供字符串即可。还可以用JSON_Array和JSON_Object函数来构造
insert into t values(5,JSON_Object('key1',v1,'key2',v2))
insert into t values(4,JSON_Array(v1,v2,v3))

JSON_OBJECT([key, val[, key, val] ...])
JSON_ARRAY([val[, val] ...])

JSON_SET(json_doc, path, val[, path, val] ...)

修改数据

update t set js=json_set('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb') where id=1

结果js={"a":456,"s":"abc","b":"bbb"}

如果set的下标超过数组长度，指挥添加到数组结尾

select json_set('[1,2,3]','$[0]',456,'$[3]','bbb')

结果[456,2,3,'bbb']

JSON_INSERT(json_doc, path, val[, path, val] ...)
如果不存在对应属性则插入,否则不做任何变动

JSON_REPLACE(json_doc, path, val[, path, val] ...)
如果存在则替换,否则不做任何变动

select json_insert('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb')
结果{"a":1,"s":"abc","b":"bbb"}

select json_replace('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb')
结果{"a":456,"s":"abc"}

 

删除数据

JSON_REMOVE(json_doc, path[, path] ...)
如果存在则删除对应属性,否则不做任何变动

select JSON_REMOVE('{"a":456,"b":"789"}','$.a');

结果：{"b": "789"}

 涉及数组时，三个函数与json_set基本一样

特别注意：$.a  与  $[0] 的区别，json数组与对象的问题

SELECT JSON_INSERT('[{"a": 1, "b": 456}]','$[1]',888)

结果 ： [{"a": 1, "b": 456}, 888]

SELECT JSON_INSERT('[{"a": 1, "b": 456}]','$[0]',888)

结果 ： [{"a": 1, "b": 456}] //结果不变，认为0索引已经存在了，注意这里结果不是[{"a":1}]

select json_replace('{"a":1}','$[0]',456)

结果 ：456   // 而非 [456]

select json_replace('{"a":1}','$[1]',456)

结果不变

其实对于 json_insert 和 json_replace 来说一般情况没必要针对数组使用。 

select json_remove('{"a":1}','$[0]')

结果不变

select json_remove('[{"a":1}]','$[0]')

结果 ： []

涉及数组的时候要小心。

JSON_MERGE(json_doc, json_doc[, json_doc] ...)    将多个doc合并

select json_merge('[1,2,3]','[4,5]')

结果 ： [1, 2, 3, 4, 5]  // 数组简单扩展

select json_merge('{"a":1}','{"b":2}')

结果 ： {"a": 1, "b": 2} // 两个对象直接合并

特殊还在数组：

select json_merge('123','45')

结果 ： [123, 45]  //两个常量变为数组

select json_merge('{"a":1}','[1,2]') 

结果 ： [{"a": 1}, 1, 2]  // 目标碰到数组，先转换为[doc]

select json_merge('[1,2]','{"a":1}')

结果 ： [1, 2, {"a": 1}]   // 非数组追加到数组后面

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)   给指定的节点，添加元素，如果节点不是数组，则先转换成[doc]

select json_Array_append('[1,2]','$[1]','456')

结果 ： [1, [2, "456"]]  

这里由于索引 1 位置为 2 ，不是数组，所以先转为数组，在添加到其尾部

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)  在数组的指定下标处插入元素

SELECT JSON_ARRAY_INSERT('[1,2,3]','$[1]',4)

结果 ： [1, 4, 2, 3] 

SELECT JSON_ARRAY_INSERT('[1,[1,2,3],3]','$[1][1]',4)

结果 ： [1, [1, 4, 2, 3], 3]

SELECT JSON_ARRAY_INSERT('[1,2,3]','$[0]',4,'$[1]',5)

结果 ： [4, 5, 1, 2, 3]  // 注意后续插入是在前面插入基础上的，而非[4,1,5,2,3]

提取json信息的函数

JSON_KEYS(json_doc[, path])   返回指定path的key

select json_keys('{"a":1,"b":2}')

结果 ["a","b"]

select json_keys('{"a":1,"b":[1,2,3]}','$.b')

结果 null  //数组没有key

JSON_CONTAINS(json_doc, val[, path])  是否包含子文档

select json_contains('{"a":1,"b":4}','{"a":0}')

结果 ： 0

select json_contains('{"a":1,"b":4}','{"a":1}')

结果 ： 1  // 包含


select json_contains('{"a":[1,2,3],"b":1}','[1,2]','$.a')

结果 ： 1  // 数组包含则需要所有元素都存在

select json_contains('{"a":[1,2,3],"b":1}','[1,2,4]','$.a')

结果 ： 0

select json_contains('{"a":[1,2,3],"b":1}','1','$.a')

结果 ： 1  //元素存在数组元素中

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) 检查路径是否存在

select JSON_CONTAINS_PATH('{"a":1,"b":1}', 'one','$.a','$.c')

结果 ： 1  // 只要存在一个

select JSON_CONTAINS_PATH('{"a":1,"b":1}', 'all','$.a','$.c')

结果 ： 0  // 必须全部存在

select JSON_CONTAINS_PATH('{"a":1,"b":{"c":{"d":1}}}', 'one','$.b.c.d')

结果 ： 1  

select JSON_CONTAINS_PATH('{"a":1,"b":{"c":{"d":1}}}', 'all','$.b.c.d')

结果 ： 1 

 

JSON_EXTRACT(json_doc, path[, path] ...)   获得doc中某个或多个节点的值。

select json_extract('{"a":81,"b":29}','$.a')

结果 ： 81 

select json_extract('{"a":[111,222,333],"b":232}','$.a[1]')

结果 ： 222

select json_extract('{"a":{"a":1,"b":2,"c":3},"b":2}','$.a.*')

结果 ： [1, 2, 3]  // a.* 通配a所有属性的值返回成数组。

select json_extract('{"a":{"a":1,"b":2,"c":3},"b":4}','$**.a')

结果 ： [{"a": 1, "b": 2, "c": 3}, 1]  //通配$中所有层次下的属性b的值返回成数组

JSON_SEARCH(json_doc, one_or_all, search_str[,escape_char[,path]..])

select json_search('{"a":"xyzf","b":{"c":"sdf"}}','one','%f%')

结果 ： "$.a"   //和like一样可以用%和_匹配，在所有节点的值中匹配，one只返回一个

select json_search('{"a":"xyzf","b":{"c":"sdf"}}','all','%f%')

结果 ： ["$.a", "$.b.c"]  


select json_search('{"a":"abc","b":{"c":"dad"}}','all','%a%',null,'$.b')

结果 ： "$.b.c"  //限制查找范围

select json_search('{"a":"abc","b":{"c":"dad"},"c":{"b":"aaa"}}','all','%a%',null,'$**.b')

结果 ： "$.b.c"  // 查找范围还可使用通配符！在每个匹配节点和其下查找

注意，只有json_extract和json_search中的path才支持通配，其他json_set,json_insert等都不支持。

JSON_LENGTH(json_doc[,path])  返回数组的长度，如果是object则是属性个数，常量为1 

select json_length('[1,2,3]')

结果 ： 3

select json_length('123')

结果 ： 1

select json_length('{"a":1,"b":2}')

结果 ： 2

JSON_DEPTH(json_doc)  返回doc 深度