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