mysql json

JOSON_ARRAY

JSON_LENGTH() 函数

返回 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 将会给出错误。

参考

• sql

exam

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_SEARCH函数

返回一个给定字符串在一个 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:可选的。只能在此路径下进行搜索。

example

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"] |
-- +--------------------------------------------------+