mysql中的json函数

Mysql中的JSON系列操作函数
一、方法罗列:

分类 函数 描述
创建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 , ′ .a',456,' .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 这 个 在 j a v a s c r i p t 中 会 出 错 , 因 为 . c 为 n u l l 。 但 是 在 j s o n s e t ( ′ ′ , ′ .c.c=123 这个在javascript中会出错,因为.c为null。 但是在json_set('{}',' .c.c=123javascript.cnulljsonset(,.c.c’,123)中,不存在的路径将直接被忽略。

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

select json_set(’{“a”:456}’,’ [ 1 ] ′ , 123 ) 结 果 [ " a " : 456 , 123 ] 。 目 标 现 被 转 换 成 [ " a " : 456 ] , 然 后 应 用 [1]',123) 结果[{"a":456},123]。目标现被转换成[{"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 , ′ [0]',456,' [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 , ′ .a',456,' .a,456,.b’,‘bbb’)
结果{“a”:1,“s”:“abc”,“b”:“bbb”}

select json_replace(’{“a”:1,“s”:“abc”}’,’ . a ′ , 456 , ′ .a',456,' .a,456,.b’,‘bbb’)
结果{“a”:456,“s”:“abc”}

加上删除节点
JSON_REMOVE(json_doc, path[, path] …)
如果存在则删除对应属性,否则不做任何变动
select json_replace(’{“a”:1,“s”:“abc”}’,’ . a ′ , ′ .a',' .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 ] ′ , ′ 45 6 ′ ) 结 果 [ [ 1 , 456 ] , 2 ] 。 指 定 插 在 [0]','456') 结果[[1,456],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]',4) 结果[1,4,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]',4) 结果[1,[1,4,2,3],3]。在 [1][1],4)[1,[1,4,2,3],3][1]数组的下标1处插入

SELECT JSON_ARRAY_INSERT(’[1,2,3]’,’ [ 0 ] ′ , 4 , ′ [0]',4,' [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 ′ , ′ .a',' .a,.c’)
结果1。只要存在一个

select JSON_CONTAINS_PATH(’{“a”:1,“b”:1}’, ‘all’,’ . a ′ , ′ .a',' .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') 结果[2,4]。通配 .b)[2,4]中所有层次下的属性b的值返回成数组。

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

select id,js->’ . i d ′ f r o m t w h e r e j s − > ′ .id' from t where js->' .idfromtwherejs>.a’=1 order by js->’ . b ′ 等 价 于 s e l e c t i d , j s o n e x t r a c t ( j s , ′ .b' 等价于 select id,json_extract(js,' .bselectid,jsonextract(js,.id’)
from t where json_extract(js,’ . a ′ ) = 1 o r d e r b y j s o n e x t r a c t ( j s , ′ .a')=1 order by json_extract(js,' .a)=1orderbyjsonextract(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 " , " .a"," .a",".b.c"]

select json_search(’{“a”:“abc”,“b”:{“c”:“dad”}}’,‘all’,’%a%’,null,’ . b ′ ) 结 果 [ " .b') 结果[" .b)[".b.c"]。限制查找范围。

select json_search(’{“a”:“abc”,“b”:{“c”:“dad”},“c”:{“b”:“aaa”}}’,‘all’,’%a%’,null,’ ∗ ∗ . b ′ ) 结 果 [ " **.b') 结果[" .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

JSON_DEPTH(json_doc)
返回doc深度

select json_depth(’{}’),json_depth(’[]’),json_depth(‘123’)
结果1,1,1

select json_depth(’[1,2,3,4,5,6]’)
结果2

select json_depth(’{“a”:{“b”:{“c”:1}}}’)
结果4

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值