MySQL8.0 JSON相关函数(一) -创建和查找JSON

原创

月饮沙 2023-08-03 17:00:16 博主文章分类：MySQL ©著作权

文章标签 JSON 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者月饮沙的原创作品，请联系作者获取转载授权，否则将追究法律责任

(目录)

本文涉及函数简介

函数	作用
JSON_ARRAY	生成JSON数组
JSON_OBJECT	生成JSON对象
JSON_CONTAINS	查看JSON文档中是否包含指定内容
JSON_CONTAINS_PATH	查看JSON文档中是否包含指定路径
column->path 和 JSON_EXTRACT()	根据路径查看文档内容（包含转义符）
column->>path 和 JSON_UNQUOTE()	根据路径查看文档内容（去掉括号和转义符）
JSON_SEARCH	根据内容查找路径
JSON_KEYS	查看JSON对象的所有键
value MEMBER OF(json_array)	查看值是否在数组中
JSON_OVERLAPS	比较两个文档是否相同
JSON_VALUE	提取JSON文档中的内容，可以指定返回的类型

创建JSON值的函数

JSON_ARRAY

JSON_ARRAY([val[, val] ...]) 生成JSON数组，数组可以为空

JSON_OBJECT

JSON_OBJECT([key, val[, key, val] ...]) 生成JSON对象，对象可以为空。如果键名错误，或者提供奇数个参数，会报错

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "11:30:24.000000"]   |
+---------------------------------------------+

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

查找JSON值的函数

查看JSON文档中是否包含指定内容或路径

JSON_CONTAINS

JSON_CONTAINS(target, candidate[, path]) 查看JSON文档中是否包含指定内容。如果指定path，只在path中进行查找。如果找到，返回1，否则返回0 目标内容是字符串时，当文档中存在数组时，目标内容是数组的子集就会返回1。要查找JSON对象，需要使用键值对进行查找，当对象的值是数组时，只要目标内容是数组的子集就会返回1

JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) 查看JSON文档中是否包含指定路径。第二个参数可以是one或者all。

one 文档中包含至少一个路径，返回1，否则返回0
all 文档中包含所有路径，返回1，否则返回0

mysql> select value,json_contains(value,'"b"'),json_contains(value,'{"c":5}'),json_contains(value,'[3,6]',"$.c") from test_json\G
*************************** 1. row ***************************
                             value: {"a": 1, "b": 2, "c": [3, 4, 5]}
        json_contains(value,'"b"'): 0
    json_contains(value,'{"c":5}'): 1
json_contains(value,'[3,6]',"$.c"): 0

*************************** 1. row ***************************
                                      value: {"a": 1, "b": 2, "c": [3, 4, 5]}
      json_contains_path(value,'one',"$.c"): 1
json_contains_path(value,'all',"$.b","$.e"): 0
json_contains_path(value,'one',"$.e","$.a"): 1

查看JSON文档的内容或路径

根据path查找文档中的内容，关于路径在上一篇博文：MySQL8.0 JSON数据类型中已经写过了，此处不再赘述。这些函数不止可以用在SELETE中，还可以用在WHERE,HAVING, ORDER BY, GROUP BY子句中

column->path

column->path 等同于 JSON_EXTRACT(json_doc, path[, path] ...) 会将结果用"引起来并且显示其中的转义字符。

column->>path

column->>path 等同于 JSON_UNQUOTE( JSON_EXTRACT(column, path) )和 JSON_UNQUOTE(column -> path) 不会引用，也不显示转义字符。

JSON_VALUE

JSON_VALUE(json_doc, path) 从文档的指定路径中提取内容，可以选择将内容更改为指定的格式。并且可以设置当结果为空或者查询发生错误时如何处理。语法：

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR

参数:

json_doc 要提取内容的文档
path 路径
type 返回结果的类型，默认 VARCHAR(512)
on_empty 当指定路径的内容为空是如何处理
- NULL ON EMPTY 返回NULL（默认）
- DEFAULT value ON EMPTY 返回一个默认的value
- ERROR ON EMPTY 返回错误
on_error
- NULL ON ERROR 返回NULL（默认）
- DEFAULT value ON ERROR 返回一个默认的value
- ERROR ON ERROR 返回错误

示例：

mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname');
+--------------------------------------------------------------+
| JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') |
+--------------------------------------------------------------+
| Joe                                                          |
+--------------------------------------------------------------+

mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
    -> RETURNING DECIMAL(4,2)) AS price;
+-------+
| price |
+-------+
| 49.95 |
+-------+

JSON_SEARCH

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) 返回JSON文档中指定内容的路径。如果找不到，返回NULL

one 在查找到一个包含内容的路径后就停止并返回该路径。查找是无序的
all 查找所有包含内容的路径，并返回所有路径的数组。数组是无序的
search_str中可以包含通配符%和_
如果在search_str中包含%和_，需要使用转义字符，默认的转移字符是\。可以使用escape_char指定转义字符,escape_char只能为空或者是一个字符。

mysql> select value,JSON_SEARCH(value, 'one', 'abc'),JSON_SEARCH(value, 'all', 'abc'),JSON_SEARCH(value, 'all', 'ghi'),JSON_SEARCH(value, 'all', '10'),JSON_SEARCH(value, 'all', '10', NULL, '$'),JSON_SEARCH(value, 'all', '10', NULL, '$[*]'),JSON_SEARCH(value, 'all', '10', NULL, '$**.k'),JSON_SEARCH(value, 'all', '10', NULL, '$[*][0].k'),JSON_SEARCH(value, 'all', '10', NULL, '$[1]'),JSON_SEARCH(value, 'all', '10', NULL, '$[1][0]'),JSON_SEARCH(value, 'all', 'abc', NULL, '$[2]'),JSON_SEARCH(value, 'all', '%a%'),JSON_SEARCH(value, 'all', '%b%'),JSON_SEARCH(value, 'all', '%b%', NULL, '$[0]'),JSON_SEARCH(value, 'all', '%b%', NULL, '$[2]'),JSON_SEARCH(value, 'all', '%b%', NULL, '$[1]'),JSON_SEARCH(value, 'all', '%b%', '', '$[1]'),JSON_SEARCH(value, 'all', '%b%', '', '$[3]') from test_json\G
*************************** 1. row ***************************
                                             value: ["abc", [{"k": "10"}, "def"], {"x": "abc"}, {"y": "bcd"}]
                  JSON_SEARCH(value, 'one', 'abc'): "$[0]"
                  JSON_SEARCH(value, 'all', 'abc'): ["$[0]", "$[2].x"]
                  JSON_SEARCH(value, 'all', 'ghi'): NULL
                   JSON_SEARCH(value, 'all', '10'): "$[1][0].k"
        JSON_SEARCH(value, 'all', '10', NULL, '$'): "$[1][0].k"
     JSON_SEARCH(value, 'all', '10', NULL, '$[*]'): "$[1][0].k"
    JSON_SEARCH(value, 'all', '10', NULL, '$**.k'): "$[1][0].k"
JSON_SEARCH(value, 'all', '10', NULL, '$[*][0].k'): "$[1][0].k"
     JSON_SEARCH(value, 'all', '10', NULL, '$[1]'): "$[1][0].k"
  JSON_SEARCH(value, 'all', '10', NULL, '$[1][0]'): "$[1][0].k"
    JSON_SEARCH(value, 'all', 'abc', NULL, '$[2]'): "$[2].x"
                  JSON_SEARCH(value, 'all', '%a%'): ["$[0]", "$[2].x"]
                  JSON_SEARCH(value, 'all', '%b%'): ["$[0]", "$[2].x", "$[3].y"]
    JSON_SEARCH(value, 'all', '%b%', NULL, '$[0]'): "$[0]"
    JSON_SEARCH(value, 'all', '%b%', NULL, '$[2]'): "$[2].x"
    JSON_SEARCH(value, 'all', '%b%', NULL, '$[1]'): NULL
      JSON_SEARCH(value, 'all', '%b%', '', '$[1]'): NULL
      JSON_SEARCH(value, 'all', '%b%', '', '$[3]'): "$[3].y"

查询JSON对象的键

JSON_KEYS

JSON_KEYS(json_doc[, path]) 查询JOSN对象的键，并以数组形式返回。 JSON对象中可以嵌套其他子对象，这个函数之后返回顶级对象的键。

mysql> select value,json_keys(value),json_keys(value->"$.d") from test_json\G
*************************** 1. row ***************************
                  value: {"a": 1, "b": 2, "c": [3, 4, 5], "d": {"e": 11, "f": 4}}
       json_keys(value): ["a", "b", "c", "d"]
json_keys(value->"$.d"): ["e", "f"]

JSON文档比较

value MEMBER OF (json_array)

value MEMBER OF(json_array) 检查value是否是json数组的成员。

mysql> select value,17 MEMBER OF (value),"17" MEMBER OF (value),'a' MEMBER OF (value) from test_json\G
*************************** 1. row ***************************
                 value: [23, "abc", 17, "ab", 10]
  17 MEMBER OF (value): 1 -- 17在数组中存在
"17" MEMBER OF (value): 0 -- "17"是字符串，数组中的是数值17，类型不同，返回0
 'a' MEMBER OF (value): 0 -- a在数组中不存在，不能部分匹配

JSON_OVERLAPS

JSON_OVERLAPS(json_doc1, json_doc2) 比较两个文档，如果两个文档存在相同的键值或者数组元素，返回1。对于JSON字符串，将其视为包含该字符串的数组。

mysql>  SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]"),JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]"),JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]")\G
*************************** 1. row ***************************
JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]"): 1 --数组中存在相同元素5、7
JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]"): 1 --数组中存在相同元素57
JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]"): 0 --数组中不存在任何相同的元素

SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]')\G
*************************** 1. row ***************************
JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]'): 0 -- 两个数组中不存在相同的元素
-- 前一个数组的元素为 [1,2] [3,4] 5
-- 后一个数组的元素为 1 [2,3] [4,5] 

mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}'),JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}')\G
*************************** 1. row ***************************
JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}'): 1 -- 存在相同的键值对 "d":10
JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}'): 0 -- 虽然存在相同的键，但是值都不同

mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6'),JSON_OVERLAPS('[4,5,6,7]', '"6"'),JSON_OVERLAPS('[4,5,6,7]', '6')\G
*************************** 1. row ***************************
JSON_OVERLAPS('[4,5,"6",7]', '6'): 0 -- '6' 等同于[6]，是数值格式，但是数组中的元素是"6" 是字符串格式 所以两个元素不相等
JSON_OVERLAPS('[4,5,6,7]', '"6"'): 0 -- '"6"' 等同于JSON字符串6
  JSON_OVERLAPS('[4,5,6,7]', '6'): 1

上一篇：MySQL8.0 JSON数据类型

下一篇：MySQL8.0 JSON相关函数(二) -更改JSON值

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯