MySQl Search JSON Values

SET @var_name = expr [, @var_name = expr]
set @j就是对@j进行赋值
$符是当前json对象的意思

方法	描述
JSON_CONTAINS(target, candidate[, path])
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
JSON_EXTRACT(json_doc, path[, path] ...)
JSON_KEYS(json_doc[, path])
JSON_OVERLAPS(json_doc1, json_doc2)
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
alue MEMBER OF(json_array)

1JSON_CONTAINS(target, candidate[, path])
判断target的key 的value 是否为candidate

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SET @j2 = '1'; --用来测试包含字符
SELECT JSON_CONTAINS(@j, @j2, '$.a'); -- 1 //@j中键a,包含@j2
SELECT JSON_CONTAINS(@j, @j2, '$.b'); -- 0 //@j中键B,不包含@j2

SET @j2 = '{"d": 4}'; -- 用来验证包含字典结构
SELECT JSON_CONTAINS(@j, @j2, '$.a'); -- 0 //@j中键a,包含@j2
SELECT JSON_CONTAINS(@j, @j2, '$.c'); -- 1 //@j中键B,不包含@j2

2.JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
判断json_doc是否包含指定节点

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); -- 1 //
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); -- 0
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d'); -- 1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d'); -- 0

3.JSON_EXTRACT(json_doc, path[, path] ...)
取json的某个位置上的元素

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

->是JSON_EXTRACT的简写

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
     > FROM jemp
     > WHERE JSON_EXTRACT(c, "$.id") > 1
     > ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

mysql> SELECT c, c->"$.id", g
     > FROM jemp
     > WHERE c->"$.id" > 1
     > ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c                             | c->"$.id" | g    |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3"       |    3 |
| {"id": "4", "name": "Betty"}  | "4"       |    4 |
| {"id": "2", "name": "Wilma"}  | "2"       |    2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)

column->>path

此三个函数是等价的

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

4.JSON_KEYS(json_doc[, path])

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

5.JSON_OVERLAPS(json_doc1, json_doc2)
两个json中是否有相容的元素

SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");  -- 1
SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]");  -- 1
SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]");  -- 0

6.JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
查询指定值的位置

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'abc');  -- "$[0]"
SELECT JSON_SEARCH(@j, 'all', 'abc');  -- ["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j, 'all', 'ghi');  -- NULL
SELECT JSON_SEARCH(@j, 'all', '10');  -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]'); -- "$[2].x"
SELECT JSON_SEARCH(@j, 'all', '%a%'); -- ["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j, 'all', '%b%'); -- ["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]'); -- "$[0]"
SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]'); -- "$[2].x"
SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');  -- NULL
SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');  -- NULL
SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');  -- "$[3].y"

7.alue MEMBER OF(json_array)
查询一个值是否在json列表

SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]'); -- 1
SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]'); -- 0

参考:
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html