返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度
JSON_LENGTH(json)
JSON_LENGTH(json, path)
如果指定了 path, JSON_LENGTH() 函数返回 JSON 文档中由路径指定的值的长度,否则返回 JSON 文档的长度。 JSON_LENGTH() 函数按照如下规则计算 JSON 文档的长度:
- 纯值的长度是 1。比如, 1, '"x"', true, false, null 的长度都是 1。
- 数组的长度是数组元素的数量。 比如, [1, 2] 的长度是 2。
- 对象的长度是对象成员的数量。 比如, {"x": 1} 的长度是 1。
- 内嵌的数组或对象不参与计算长度。 比如, {"x": [1, 2]} 的长度是 1。
如果存在以下的情况,JSON_LENGTH() 函数将返回 NULL:
- 如果 JSON 文档中不存在指定的路径。
- 如果任意一个参数为 NULL。
JSON_LENGTH() 函数将在以下情况下返回错误:
- 如果参数 json 不是有效的 JSON 文档,MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
- 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。
- 在 MySQL 8.0.26 之前,如果参数 path 中包含 * 或 **, MySQL 将会给出错误。
SELECT
JSON_LENGTH('1') as `1`,
JSON_LENGTH('true') as `true`,
JSON_LENGTH('false') as `false`,
JSON_LENGTH('null') as `null`,
JSON_LENGTH('"abc"') as `"abc"`;
-- +------+------+-------+------+-------+
-- | 1 | true | false | null | "abc" |
-- +------+------+-------+------+-------+
-- | 1 | 1 | 1 | 1 | 1 |
-- +------+------+-------+------+-------+
-- 数组
SELECT
JSON_LENGTH('[]') as `[]`,
JSON_LENGTH('[1, 2]') as `[1, 2]`,
JSON_LENGTH('[1, {"x": 2}]') as `[1, {"x": 2}]`;
-- +------+--------+---------------+
-- | [] | [1, 2] | [1, {"x": 2}] |
-- +------+--------+---------------+
-- | 0 | 2 | 2 |
-- +------+--------+---------------+
-- 对象
SELECT
JSON_LENGTH('{}') as `[]`,
JSON_LENGTH('{"x": 1, "y": 2}') as `{"x": 1, "y": 2}`,
JSON_LENGTH('{"x": 1, "y": {"z" : 2}}') as `{"x": 1, "y": {"z" : 2}}`;
-- +------+------------------+--------------------------+
-- | [] | {"x": 1, "y": 2} | {"x": 1, "y": {"z" : 2}} |
-- +------+------------------+--------------------------+
-- | 0 | 2 | 2 |
-- +------+------------------+--------------------------+
-- 路径
SELECT JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y');
-- 这里, 路径表达式 $.y 对应的值是 [1, 2],[1, 2] 的长度为 2。
-- +---------------------------------------------+
-- | JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y') |
-- +---------------------------------------------+
-- | 2 |
-- +---------------------------------------------+
-- 这相当先使用 JSON_EXTRACT() 函数提取路径匹配的部分,再计算长度,如下:
SELECT JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y'));
-- +-----------------------------------------------------------+
-- | JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y')) |
-- +-----------------------------------------------------------+
-- | 2 |
-- +-----------------------------------------------------------+
返回一个给定字符串在一个 JSON 文档中的路径(它返回一个路径字符串或者由多个路径组成的数组。)
JSON_SEARCH(json, one_or_all, search_str)
JSON_SEARCH(json, one_or_all, search_str, escape_char)
JSON_SEARCH(json, one_or_all, search_str, escape_char, path)
- json 必需的。一个 JSON 文档。
- one_or_all 必需的。可用值:'one', 'all'。 JSON_SEARCH() 根据 one_or_all 参数决定是否返回所有匹配的路径: 如果是 'one',JSON_SEARCH() 函数将返回第一个匹配的路径。 如果是 'all',JSON_SEARCH() 函数将返回所有匹配的路径。所有的路径会包装在一个数组内返回。
- search_str 必需的。被搜索的字符串。 可以在 search_str 参数中使用'%'和'_'通配符,就像 LIKE 一样 % 匹配任意数量的任意字符。 _ 匹配一个任意字符。
- escape_char: 可选的。 如果 search_str 中包含 % 和 _,需要在他们之前添加转移字符。默认是 \。
- path:可选的。只能在此路径下进行搜索。
SET @json = '[
{
"name": "Tim",
"age": 20,
"hobbies": [
{ "name": "Car", "weight": 10 },
{ "name": "Sports", "weight": 20 }
]
},
{
"name": "Tom",
"age": 20,
"hobbies": [
{ "name": "Reading", "weight": 10 },
{ "name": "Sports", "weight": 20 }
]
}
]';
-- 搜索字符串
SELECT JSON_SEARCH(@json, 'one', 'Tim');
-- +----------------------------------+
-- | JSON_SEARCH(@json, 'one', 'Tim') |
-- +----------------------------------+
-- | "$[0].name" |
-- +----------------------------------+
-- one all
SELECT
JSON_SEARCH(@json, 'one', 'Sports'),
JSON_SEARCH(@json, 'all', 'Sports');
-- +-------------------------------------+--------------------------------------------------+
-- | JSON_SEARCH(@json, 'one', 'Sports') | JSON_SEARCH(@json, 'all', 'Sports') |
-- +-------------------------------------+--------------------------------------------------+
-- | "$[0].hobbies[1].name" | ["$[0].hobbies[1].name", "$[1].hobbies[1].name"] |
-- +-------------------------------------+--------------------------------------------------+
-- 通配符
SELECT JSON_SEARCH(@json, 'all', 'S%');
-- +--------------------------------------------------+
-- | JSON_SEARCH(@json, 'all', 'S%') |
-- +--------------------------------------------------+
-- | ["$[0].hobbies[1].name", "$[1].hobbies[1].name"] |
-- +--------------------------------------------------+