一、方法罗列
分类 函数 描述
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 深度