用于搜索 JSON 值的 JSON 函数
TiDB 支持大多数 MySQL 8.0 中 用于搜索 JSON 值的 JSON 函数。
JSON_CONTAINS()
JSON_CONTAINS(json_doc, candidate [,path]) 函数通过返回 1 或 0，表示给定的 candidate JSON 文档是否包含在目标 JSON 文档中。
示例：
此处 a 被包含在目标文档中。
SELECT JSON_CONTAINS('["a","b","c"]','"a"');
+--------------------------------------+
| JSON_CONTAINS('["a","b","c"]','"a"') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.00 sec)
此处 e 未被包含在目标文档中。
SELECT JSON_CONTAINS('["a","b","c"]','"e"');
+--------------------------------------+
| JSON_CONTAINS('["a","b","c"]','"e"') |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
此处 {"foo": "bar"} 被包含在目标文档中。
SELECT JSON_CONTAINS('{"foo": "bar", "aaa": 5}','{"foo": "bar"}');
+------------------------------------------------------------+
| JSON_CONTAINS('{"foo": "bar", "aaa": 5}','{"foo": "bar"}') |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
此处 "bar" 未被包含在目标文档的根节点中。
SELECT JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"');
+---------------------------------------------------+
| JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"') |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)
此处 "bar" 被包含在目标文档的 $.foo 属性中。
SELECT JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"', '$.foo');
+------------------------------------------------------------+
| JSON_CONTAINS('{"foo": "bar", "aaa": 5}','"bar"', '$.foo') |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_CONTAINS_PATH()
JSON_CONTAINS_PATH(json_doc, all_or_one, path [,path, ...]) 函数通过返回 0 或 1，表示 JSON 文档在给定路径或多个路径上是否包含数据。
示例：
此处文档包含 $.foo。
SELECT JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo');
+--------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo') |
+--------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
此处文档不包含 $.bar。
SELECT JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.bar');
+--------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.bar') |
+--------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
此处文档同时包含 $.foo 和 $.aaa。
SELECT JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo', '$.aaa');
+-----------------------------------------------------------------------+
| JSON_CONTAINS_PATH('{"foo": "bar", "aaa": 5}','all','$.foo', '$.aaa') |
+-----------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_EXTRACT()
JSON_EXTRACT(json_doc, path[, path] ...) 函数用于从 JSON 文档中提取数据，提取的部分由 path 参数指定。
SELECT JSON_EXTRACT('{"foo": "bar", "aaa": 5}', '$.foo');
+---------------------------------------------------+
| JSON_EXTRACT('{"foo": "bar", "aaa": 5}', '$.foo') |
+---------------------------------------------------+
| "bar" |
+---------------------------------------------------+
1 row in set (0.00 sec)
->
column->path 函数返回 column 中与 path 参数匹配的数据。它是 JSON_EXTRACT() 的别名。
SELECT
j->'$.foo',
JSON_EXTRACT(j, '$.foo')
FROM (
SELECT
'{"foo": "bar", "aaa": 5}' AS j
) AS tbl;
+------------+--------------------------+
| j->'$.foo' | JSON_EXTRACT(j, '$.foo') |
+------------+--------------------------+
| "bar" | "bar" |
+------------+--------------------------+
1 row in set (0.00 sec)
->>
column->>path 函数会对 column 中与 path 参数匹配的数据进行去引号操作。它等价于 JSON_UNQUOTE(JSON_EXTRACT(doc, path_literal))。
SELECT
j->'$.foo',
JSON_EXTRACT(j, '$.foo')
j->>'$.foo',
JSON_UNQUOTE(JSON_EXTRACT(j, '$.foo'))
FROM (
SELECT
'{"foo": "bar", "aaa": 5}' AS j
) AS tbl;
+------------+--------------------------+-------------+----------------------------------------+
| j->'$.foo' | JSON_EXTRACT(j, '$.foo') | j->>'$.foo' | JSON_UNQUOTE(JSON_EXTRACT(j, '$.foo')) |
+------------+--------------------------+-------------+----------------------------------------+
| "bar" | "bar" | bar | bar |
+------------+--------------------------+-------------+----------------------------------------+
1 row in set (0.00 sec)
JSON_KEYS()
JSON_KEYS(json_doc [,path]) 函数以 JSON 数组的形式返回 JSON 对象的顶层键名。如果指定了 path 参数，则返回选定路径下的顶层键名。
示例：
以下示例返回 JSON 文档中的两个顶层键名。
SELECT JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}');
+---------------------------------------------------------------------------+
| JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}') |
+---------------------------------------------------------------------------+
| ["name", "type"] |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
以下示例返回 JSON 文档中 $.name 路径下的顶层键名。
SELECT JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}', '$.name');
+-------------------------------------------------------------------------------------+
| JSON_KEYS('{"name": {"first": "John", "last": "Doe"}, "type": "Person"}', '$.name') |
+-------------------------------------------------------------------------------------+
| ["first", "last"] |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_SEARCH()
JSON_SEARCH(json_doc, one_or_all, str) 函数在 JSON 文档中查找字符串的一个或所有匹配项。
示例：
在以下示例中，你可以查找 cc 的第一个结果，它位于 a 数组的索引 2 位置。
SELECT JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','one','cc');
+------------------------------------------------------------------------+
| JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','one','cc') |
+------------------------------------------------------------------------+
| "$.a[2]" |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
现在你将 one_or_all 设置为 all，可以获取所有结果，而不仅仅是第一个结果。
SELECT JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','all','cc');
+------------------------------------------------------------------------+
| JSON_SEARCH('{"a": ["aa", "bb", "cc"], "b": ["cc", "dd"]}','all','cc') |
+------------------------------------------------------------------------+
| ["$.a[2]", "$.b[0]"] |
+------------------------------------------------------------------------+
1 row in set (0.01 sec)
MEMBER OF()
str MEMBER OF (json_array) 函数用于判断传入的值 str 是否为 json_array 的元素，如果是则返回 1，否则返回 0。如果任一参数为 NULL，则返回 NULL。
SELECT '🍍' MEMBER OF ('["🍍","🥥","🥭"]') AS 'Contains pineapple';
+--------------------+
| Contains pineapple |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
JSON_OVERLAPS()
JSON_OVERLAPS(json_doc, json_doc) 函数用于判断两个 JSON 文档是否有重叠部分。如果有重叠，则返回 1；否则返回 0。如果任一参数为 NULL，则返回 NULL。
示例：
以下示例显示没有重叠，因为数组值的元素数量不同。
SELECT JSON_OVERLAPS(
'{"languages": ["Go","Rust","C#"]}',
'{"languages": ["Go","Rust"]}'
) AS 'Overlaps';
+----------+
| Overlaps |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
以下示例显示两个 JSON 文档完全相同，因此有重叠。
SELECT JSON_OVERLAPS(
'{"languages": ["Go","Rust","C#"]}',
'{"languages": ["Go","Rust","C#"]}'
) AS 'Overlaps';
+----------+
| Overlaps |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
以下示例显示存在重叠，尽管第二个文档有额外的属性。
SELECT JSON_OVERLAPS(
'{"languages": ["Go","Rust","C#"]}',
'{"languages": ["Go","Rust","C#"], "arch": ["arm64"]}'
) AS 'Overlaps';
+----------+
| Overlaps |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)