MySQL JSON 支持

官方 doc : dev.mysql.com/doc/refman/…

JSON_CONTAIN

• 参数：源json，目标json，搜索path

• 作用：从源json串中通过path搜索目标json,如果找到返回1，没有找到返回0

JSON_CONTAINS_PATH

• 参数：待搜索的JSON文档信息，匹配模式all或者one,搜索的path，可变参数
• 作用：可以指明多个PATH,来判断是否存在指定路径，指定的路径可以有多个，指明'one'或者是'all'匹配的时候可以指明如果有一个路径能找的到就返回1或者是全部存在才能返回1。

set @j = '{"a": 1, "b": 2, "c": {"d": 1}}'
set @j2 = '1'
select json_contains_path(@j,'all','$.a','$.c')
-- $.a与$.c都存在，因此返回1

JSON_EXTRACT

• 参数 json文档，抽取路径,可以填写多个路径
• 作用：从json文档中抽取指定路径下的信息，可以指明多个路径生成在同一个json文档做整合

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
-- [30,40]

column -> path

• 用法：相当于JSON_EXTRACT的简写形式。其中从column指明的是json列，单箭头之后来抽取路径。
• 作用，抽取json文档中的信息。

SELECT c, JSON_EXTRACT(c, "$.id"), g
FROM jemp
WHERE JSON_EXTRACT(c, "$.id") > 1
ORDER BY JSON_EXTRACT(c, "$.name");
-- 等价于
SELECT c, c->"$.id", g
FROM jemp
WHERE c->"$.id" > 1
ORDER BY c->"$.name";

• 这种写法也可以用在其他类型的语句中，如update，alter table,delete等
• 注意和column->>path的区别
  • 同样是抽取，不过是对抽取的结果做了一个JSON_UNQUOTE操作

column ->>path

• 同样是抽取，没有了引号

SELECT c->'$.name' AS name FROM jemp WHERE g > 2;
-- 返回的是
-- +----------+
-- | name     |
-- +----------+
-- | "Barney" |
-- | "Betty"  |
-- +----------+
SELECT JSON_UNQUOTE(c->'$.name') AS name FROM jemp WHERE g > 2;
-- +--------+
-- | name   |
-- +--------+
-- | Barney |
-- | Betty  |
-- +--------+

• 以下三条语句等价

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

JSON_KEYS

• 作用：获取json对象的keys
• 参数：可以只传一个JSON文本，自动提取所有的Key，或者是传一个JSON文本，再传一个path

select JSON_KEYS('{"a": 1, "b": {"c": 30},"d":1}')
-- ["a", "b", "d"]
select JSON_KEYS('{"a": 1, "b": {"c": 30},"d":1}','$.b')
-- ["c"]

JSON_VALUES

• 作用，获取JSON串的值。
• 参数，JSON文档，获取值的path，EMPTY回调,ERROR回调

JSON_OVERLAPS

• 作用 overlaps表示重叠的意思，这里查看元素是否有重叠(有相同的元素)，元素可以是多维数组，可以是对象，如果是标量，就是一个简单的等值判断
• 参数：传入两个json串

-- 数组
SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]"); -- 1
-- 多维数组
SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
-- 对象
SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
-- 标量
SELECT JSON_OVERLAPS('5', '5');

• 注意事项
  • 这个函数在MySQL 8.0.17之后提供支持
  • 这个函数并不支持类型转换，比如两个参数一个是数组，另外一个是标量

SELECT JSON_OVERLAPS('[4,5,"6",7]', '6'); -- 0
SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"'); -- 0