MySQL JSON_SEARCH() 函数使用指南

MySQL JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径。

JSON_SEARCH() 语法

这里是 MySQL JSON_SEARCH() 的语法：

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

    可选的。只能在此路径下进行搜索。

返回值

JSON_SEARCH() 函数返回一个给定字符串在一个 JSON 文档中的路径。它返回一个路径字符串或者由多个路径组成的数组。

JSON_SEARCH() 函数将在以下情况下返回 NULL：

  • 未搜索到指定的字符串
  • JSON 文档中不存在指定的 path
  • 任意一个参数为 NULL

JSON_SEARCH() 函数将在以下情况下返回错误：

  • 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
  • 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_SEARCH() 示例

让我们先创建一个 JSON 文档以供以下的例子使用：

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 }
    ]
  }
]';

这里， 我们创建了一个 JSON 数组，它包含两个用户信息。

示例: 搜索字符串

SELECT JSON_SEARCH(@json, 'one', 'Tim');

+----------------------------------+
| JSON_SEARCH(@json, 'one', 'Tim') |
+----------------------------------+
| "$[0].name"                      |
+----------------------------------+

示例: one vs 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"] |
+--------------------------------------------------+