mysql json 基础查询

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

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

1、mysql 相关JSON函数

分类 函数 描述
创建json json_array 创建json数组 (json_array(数组元素))
json_object 创建json对象(json_object(key,value,key,value…))
修改json json_remove 删除元素(json_remove(json,path))
json_merge json合并(json_object同路径值合并成数组)
json_array_append 在json数组的某个下标对应元素中添加值
json_array_insert 为json数组添加元素(json_array_insert(json,path,value))
查询json json_quote 将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中的键值jison数组
json_search 按给定字符串关键字搜索
其他 json_valid json格式校验(校验通过返回1,否则返回0)
json_type json_type(返回OBJECT或ARRAY,当json格式错误,报错)
json_depth json深度(到value最大深度)
json_length json长度(顶层元素个数)
json_pretty json格式化

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_ESCAPES  SQL模式时,会将反斜杠当作普通字符,导致无法正确解析,例\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
转载请说明出处内容投诉
CSS教程_站长资源网 » mysql json 基础查询

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买