Mysql中的JSON系列操作函数

Bertha 。 2023-02-16 04:24 26阅读 0赞

**一、方法罗列：**

分类 函数 描述  
创建json  
json\_array 创建json数组  
json\_object 创建json对象  
json\_quote 将json转成json字符串类型  
查询json   
json\_contains 判断是否包含某个json值  
json\_contains\_path 判断某个路径下是否包json值  
json\_extract 提取json值  
column->path        json\_extract的简洁写法，MySQL 5.7.9开始支持  
column->>path      json\_unquote(column -> path)的简洁写法  
json\_keys 提取json中的键值为json数组  
json\_search 按给定字符串关键字搜索json，返回匹配的路径  
修改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\_set 设置值（替换旧值，并插入不存在的新值）  
json\_unquote 去除json字符串的引号，将值转成string类型  
返回json属性   
json\_depth 返回json文档的最大深度  
json\_length 返回json文档的长度  
json\_type 返回json值得类型  
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"\}

path中$就代表整个doc,然后可以用[JavaScript][]的方式指定对象属性或者数组下标等.  
执行效果，类似json的语法  
$.a=456  
$.b="bbb"

存在就修改,不存在就设置.

$.c.c=123  
这个在javascript中会出错，因为.c为null。  
但是在json\_set('\{\}','$.c.c',123)中，不存在的路径将直接被忽略。

特殊的对于数组，如果目标doc不是数组则会被转换成\[doc\]，然后再执行set，  
如果set的下标超过数组长度，只会添加到数组结尾。

select json\_set('\{"a":456\}','$\[1\]',123)  
结果\[\{"a":456\},123\]。目标现被转换成\[\{"a":456\}\],然后应用$\[1\]=123。

select json\_set('"abc"','$\[999\]',123)  
结果\["abc",123\]。

再举几个例子  
select json\_set('\[1,2,3\]','$\[0\]',456,'$\[3\]','bbb')  
结果\[456,2,3,'bbb'\]

注意:  
对于javascript中  
var a=\[1,2,3\]  
a.a='abc'  
是合法的,但是一旦a转成json字符串,a.a就丢失了。

而在mysql中，这种算作路径不存在，因此  
select json\_set('\[1,2,3\]','$.a',456)  
结果还是\[1,2,3\]

然后还有另外两个版本  
JSON\_INSERT(json\_doc, path, val\[, path, val\] ...)  
如果不存在对应属性则插入,否则不做任何变动

JSON\_REPLACE(json\_doc, path, val\[, path, val\] ...)  
如果存在则替换,否则不做任何变动

这两个操作倒是没有javascript直接对应的操作  
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\_replace('\{"a":1,"s":"abc"\}','$.a','$.b')  
结果\{"s":"abc"\}

涉及数组时，三个函数与json\_set基本一样  
select json\_insert('\{"a":1\}','$\[0\]',456)  
结果不变，认为0元素已经存在了，注意这里结果不是\[\{"a":1\}\]

select json\_insert('\{"a":1\}','$\[999\]',456)  
结果追加到数组结尾\[\{"a":1\}，456\]

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\]','$','456')  
结果\[1,2,456\]

select json\_Array\_append('\[1,2\]','$\[0\]','456')  
结果\[\[1,456\],2\]。指定插在$\[0\]这个节点，这个节点非数组，所以等效为  
select json\_Array\_append('\[\[1\],2\]','$\[0\]','456')

JSON\_ARRAY\_INSERT(json\_doc, path, val\[, path, val\] ...)  
在数组的指定下标处插入元素

SELECT JSON\_ARRAY\_INSERT('\[1,2,3\]','$\[1\]',4)  
结果\[1,4,2,3\]。在$数组的下标1处插入

SELECT JSON\_ARRAY\_INSERT('\[1,\[1,2,3\],3\]','$\[1\]\[1\]',4)  
结果\[1,\[1,4,2,3\],3\]。在$\[1\]数组的下标1处插入

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":1\}')  
结果1

select json\_contains('\{"a":2,"b":1\}','\{"a":1\}')  
结果0

select json\_contains('\{"a":\[1,2,3\],"b":1\}','\[1,2\]','$.a')  
结果1。数组包含则需要所有元素都存在。

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\}\}\}', 'one','$.a.c.d')  
结果0。

JSON\_EXTRACT(json\_doc, path\[, path\] ...)  
获得doc中某个或多个节点的值。

select json\_extract('\{"a":1,"b":2\}','$.a')  
结果1

select json\_extract('\{"a":\[1,2,3\],"b":2\}','$.a\[1\]')  
结果2

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\}','$\*\*.b')  
结果\[2,4\]。通配$中所有层次下的属性b的值返回成数组。

mysql5.7.9开始增加了一种简写方式：column->path

select id,js->'$.id' from t where js->'$.a'=1 order by js->'$.b'  
等价于  
select id,json\_extract(js,'$.id')   
from t where json\_extract(js,'$.a')=1  
order by json\_extract(js,'$.b')

JSON\_SEARCH(json\_doc, one\_or\_all, search\_str\[, escape\_char\[, path\] ...\])  
强大的查询函数，用于在doc中返回符合条件的节点，select则是在表中返回符合要求的纪录。

select json\_search('\{"a":"abc","b":\{"c":"dad"\}\}','one','%a%')

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

select json\_search('\{"a":"abc","b":\{"c":"dad"\}\}','all','%a%')  
结果\["$.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","$.c.b"\]。查找范围还可使用通配符！在每个匹配节点和其下查找。

注意，只有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

可再跟path参数  
select json\_length('\{"a":1,"b":\[1,2,3\]\}','$.b')  
结果3