mysq json 主要有JSON 对象(json object )和JSON 数组(json array )两种类型

$表示整个json对象,在索引数据时用下标(对于json array,从0开始)或键值(对于json object,含有特殊字符的key要用”括起来,比如$.“my name”)。

1、mysql 相关JSON函数

分类函数描述
创建jsonjson_array创建json数组 (json_array(数组元素))
json_object创建json对象(json_object(key,value,key,value…))
修改jsonjson_remove删除元素(json_remove(json,path))
json_mergejson合并(json_object同路径值合并成数组)
json_array_append在json数组的某个下标对应元素中添加值
json_array_insert为json数组添加元素(json_array_insert(json,path,value))
查询jsonjson_quote将json转成json字符串类型
json_contains判断是否包含某个json值
json_contains_path判断某个路径下是否包json值
json_extract提取json值
column->pathjson_extract的简洁写法,MYSQL 5.7.9开始支持
column->>pathjson_unquote(column -> path)的简洁写法
json_keys提取json中的键值jison数组
json_search按给定字符串关键字搜索
其他json_validjson格式校验(校验通过返回1,否则返回0)
json_typejson_type(返回OBJECT或ARRAY,当json格式错误,报错)
json_depthjson深度(到value最大深度)
json_lengthjson长度(顶层元素个数)
json_prettyjson格式化

1.1 查询JSON

注: $表示整个json对象,

 $.*为整个json文件下; $**.key表示JSON中所有key的值
1.1.1 json_contains

​ json_contains 判断是否包含某个,返回值0或1,0代表存在该值,1代表不存在该值。

​ 规则: json_contains(json, 查询值, 查询路径),如果任何参数为NULL或路径参数未标识目标文档的某个部分,则返回NULL;

例1:

select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.b'); -- 既在json中 b这个key的路径下存在为2的value,返回1

例2:

select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.a');--在json中 $.a 这个key不存在2的这个vaule,因此返回0.($代表整个JSON,$.a获取数据为 {"a":1,"b":2} 这个JSON对象,而不存在单独的 2 这个值,从而返回0)

select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.a.a');-当取到正确的路径下时,返回1

1.2 json_contains_path

​ json_contains_path 判断JSON文档是都包含给定路径上的数据

​ 规则:json_contains_path(json, one_or_all, path(可多个)),如果任何参数为NULL,则返回NULL;如果JSON无法解析、或 one_or_all 不为 ‘one’ 或则 ‘all’、或任何路径参数都不是有效的路径表达式 ,则报错。

​ 1)‘one’:如果文档中至少存在一个路径,则为1,否则为0。

​ 2)‘all’:如果文档中存在所有路径,则为1,否则为0。

例1:

select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.a') -- 在JSON中存在一个'$.a'的路径,返回1

例2:

select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.e') -- 在JSON中不存在一个'$.e'的路径,返回0

例3:

select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.a','$.e') -- 'one'表示在JSON中,存在'$.a','$.e'任意一路径,则返回1。

例4:

select json_contains_path('{"a": {"a":2,"b":1},"b":2}','all','$.a','$.e') -- 'all'表示后续路径('$.a','$.e')都要存在,才会返回1,否则返回0('$.e'不存在)。

1.3 json_extract

json_extract 返回匹配路径下的所有值,如果任何参数为NULL或没有在文档中找到该路径,则返回NULL;若如果JSON无法解析、或路径错误,则报错。

​ 规则:json_extract(json,path(可多个)),json可为 object 或 array 两种类型,当json为object类型 时 路径为 ‘$.key’ ,当json为array 类型时路径为数组下标(从0开始)。

​ 注:返回值由路径参数匹配的所有值组成。如果这些参数可能返回多个值,则匹配的值将自动包装为一个数组,其顺序与生成它们的路径相对应。否则,返回值为单个匹配值。

例1:

select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a') -- '$.a' 下对应值是个json对象,则将整个对应对象返回

例2:

select json_extract('{"a": {"a":2,"b":1},"b":2}','$.b') -- '$.a' 下对应值是单个值,则返回对应值

例3:

select json_extract('{"a": {"a":2,"b":1},"b":2}','$.*') -- $.* 代表整个json的任意路径,即返回了$.a的数据,又返回了$.b 的数据,返回了多个值,自动包装成数组

例4:

select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a','$.c') --可写多个路径,当路径都不存在时,返回null

例5:

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') --'$[1]'获取下标为1的数据(起始下标为0)

例6:

select JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]')-- path可为多个路径,返回值与path的排序相同

例6:

select JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') -- $[index][index]...[index] 可多层数组下标查找数据,[*]表所有数据

例7:

select JSON_EXTRACT('[10, [20,[60]], [30, [40,50]]]', '$[*][1][0]') --[*] 可以放在任意位置作为通配表所有,'$[*][1][0]' 中 $[*]表示第一层查所有,第二层取第二个数,第三层取第一个数。可用于查询最大层级数

例8:

SELECT JSON_EXTRACT('{"a": {"a":2,"b":1},"b":2,"c":1}', '$**.a'); -- '$**.a' 表示搜索整个JSON中的key为a的值,第一层{"a": 2, "b": 1},第二层取{"a":2,"b":1}中a的值2,组合包装成数组返回

1.4 json_quote

json_quote 通过用双引号字符 包装 字符串 并转义内部引号和其他字符,返回结果为utf8mb4字符串。

例:

select json_quote('{"a": {"a":2,"b":1},"b":2}')-- 外层'转换成" ,内层字符转义,如"转\"

1.5 json_unquote
 json_unquote 转义解析,与json_quote 相反(当启用 NO_BACKSLASH_ESCAPESSQL模式时,会将反斜杠当作普通字符,导致无法正确解析,例\n 一般情况下表示换行,但是 启用 NO_BACKSLASH_ESCAPES时,会被解析成n) 

​ 注:SELECT @@sql_mode; – 查看当前模式

例:

select json_unquote("{\"a\": {\"a\":2,\"b\":1},\"b\":2}")

1.6 column->path 和column->>path

​ 5.7.9 版本之后,加入 -> 和 ->> 语法糖: ->相当于 JSON_EXTRACT(column,path),存在转义字符; ->> 相当于 json_unquote(JSON_EXTRACT(column,path ))。 注:column为表列,不能直接写死值,若想以某值做测试,请子查询作为临时表

例1:

例2:

select t.co ->>'$.a' from (select '{"a": {"a":2,"b":1},"b":2}' ->> as 'co') t--注 select '{"a": {"a":2,"b":1},"b":2}' ->>'$.a' 会报错

1.7 json_keys

​ json_keys 将JSON对象的顶层值中的键作为JSON数组返回,如果给定了路径参数,则返回所选路径中的顶层键(及第一层)。

​ 规则:json_keys(json) 或json_keys(json,path) 。json_keys(json) 返回整个JSON的顶层键,json_keys(json,path) 返回对应path下的顶层键。

​ 1) 当json格式错误,或path中包含*或**时报错。

​ 2) 当path下级不包含key时,返回null

​ 3) json 必须是object 类型

例1:

select json_keys('{"a": {"a":2,"b":1},"b":2}','$.b') -- '$.b' 下不包含key,返回null

例2:

select json_keys('{"a": {"a":2,"b":1},"b":2}') --不写path 返回整个JSON顶层键

1.8 json_search为空

json_search 返回JSON中给定字符串的路径。若json为空、或search_str为空、或search_str在json中不存在、或path参数为NULL、或path不存在,则返回NULL;当json格式错误、one_or_all不是“one”或“all”、路径表达式错误、escape_char不是常量表达式,则报错

​ 规则:JSON_SEARCH(json_doc,one_or_all,search_str [,escape_char [,path] …])

1.8.1 one_or_all

​ 为all时,返回所有路径匹配值,若多个路径,则包装成数组返回(数组元素的顺序未定义),多个相同路径只显示一个。

​ 为one时,搜索在第一次匹配后终止,并返回一个路径字符串(先匹配哪个路径是不能确定的)。

​ 例:

select json_search('{"a": {"a":2,"b":"1"},"b":"1"}','all','1')-- all返回所有匹配路径

select json_search('{"a": {"a":2,"b":"1"},"b":"1"}','one','1') -- one 返回一个vule为1的路径

select json_search('{"a": {"a":"2","b":"1"},"b":"1"}','all','2',null,'$.a','$') -- '$'和'$.a' 返回路径是同一个,不重复显示

1.8.2 json中的值必须为字符串,否则找不到对应路径,返回null

​ 例1:

select json_search('{"a": {"a":2,"b":1},"b":21}','all','21')--当json中值不为char类型,无法匹配,返回null

例2:

select json_search('{"a": {"a":2,"b":1},"b":"21"}','all','21')-- json中存在的值为21的数据,返回其路径

1.8.3 模糊匹配

​ 与sql中like用法相似,’% ‘表示匹配0~n个字符,’_ ‘表示匹配单个字符(因此在json中若需要匹配%或_的字符串,需提前转义(编译时就得转)加\反斜杠)。

例1:

select json_search('{"a": {"a":"123","b":"345"},"b":"12"}','all','12%')--%匹配

例2:

select json_search('{"a": {"a":"123","b":"345"},"b":"12"}','all','12_')

例3:

select json_search('{"a": {"a":"123","b":"345"},"b":"12%"}','all','%\%%') -- \% 模糊查找%

1.8.4 其他

​ 带路径匹配:

例1:

SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[2]'); -- $[2]表示下标为2的,同理,也可将'$[2]' 替换成key

例2:

SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL, '$');-- '$' 表示整个json

例3:

SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$[*]');-- '$[*]'表示整个数组 *表示通配

例4:

SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$**.k');-- 路径最后一级 key 为k 且值为10的 路径

例5:

SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$[*][0].k'); --'$[*][0].k' 数组中第1个员素下key为k