MariaDB JSON_SEARCH() 函数的基础用法与实例

MariaDB 的 JSON_SEARCH() 函数可以在 JSON 文档中搜索给定的值，并返回包含该字符串的路径。

发布于

在处理 JSON 数据时，经常需要在复杂的 JSON 结构中查找特定的值。MariaDB 的 JSON_SEARCH() 函数提供了这样的功能，它可以在 JSON 文档中搜索给定字符串，并返回包含该字符串的路径。本文将详细介绍 JSON_SEARCH() 函数的语法和用法，并通过实例演示如何在实际应用中使用它。

语法

JSON_SEARCH() 函数的基本语法如下：

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
  • json_doc 是一个 JSON 格式的字符串。
  • one_or_all 参数可以是 'one' 或 'all'，指定是返回找到的第一个匹配项还是所有匹配项的路径。
  • search_str 是要搜索的字符串。
  • escape_char 是一个可选的字符，用于转义特殊字符。
  • path 是一个可选的参数，用于限制搜索范围。

函数返回找到的路径。如果没有找到匹配项，返回 NULL。

实例

搜索 JSON 对象中的值

SET @json = '{"name": "Alice", "age": 25, "city": "Alice Springs"}';
SELECT JSON_SEARCH(@json, 'one', 'Alice');

输出结果为：

+------------------------------------+
| JSON_SEARCH(@json, 'one', 'Alice') |
+------------------------------------+
| "$.name"                           |
+------------------------------------+

搜索 JSON 数组中的值

SET @json = '["apple", "banana", "cherry"]';
SELECT JSON_SEARCH(@json, 'all', 'apple');

输出结果为：

+------------------------------------+
| JSON_SEARCH(@json, 'all', 'apple') |
+------------------------------------+
| "$[0]"                             |
+------------------------------------+

搜索具有特定路径的 JSON 对象

SET @json = '{"person": {"name": "Alice", "age": 25}, "city": "Alice Springs"}';
SELECT JSON_SEARCH(@json, 'one', 'Alice', NULL, '$.person');

输出结果为：

+------------------------------------------------------+
| JSON_SEARCH(@json, 'one', 'Alice', NULL, '$.person') |
+------------------------------------------------------+
| "$.person.name"                                      |
+------------------------------------------------------+

搜索包含转义字符的 JSON 对象

SET @json = '{"query": "SELECT * FROM users WHERE name LIKE \'%Alice%\'"}';
SELECT JSON_SEARCH(@json, 'one', '%Alice%', '\\');

输出结果为：

+--------------------------------------------+
| JSON_SEARCH(@json, 'one', '%Alice%', '\\') |
+--------------------------------------------+
| "$.query"                                  |
+--------------------------------------------+

从表中查询并搜索 JSON 值

首先，我们创建一个表并插入数据。

CREATE TABLE user_profiles (id INT, profile JSON);
INSERT INTO user_profiles VALUES (1, '{"name": "Alice", "age": 25}');

然后，我们可以搜索表中的 JSON 数据。

SELECT id, JSON_SEARCH(profile, 'one', 'Alice') AS path FROM user_profiles;

输出结果为：

+------+----------+
| id   | path     |
+------+----------+
|    1 | "$.name" |
+------+----------+

相关函数

结论

JSON_SEARCH() 函数是处理 JSON 数据时非常有用的工具，特别是在需要在复杂的 JSON 结构中查找特定值时。通过上述实例，我们可以看到它在实际应用中的灵活性和便捷性。无论是简单的值搜索，还是复杂的路径和模式匹配，JSON_SEARCH() 都能提供有效的解决方案。在处理 JSON 数据时，它是一个不可或缺的函数。