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

来源地址：https://blog.csdn.net/qq_38782460/article/details/129257318