SQL 中如何处理 JSON 数据？

推荐答案

在 SQL 中处理 JSON 数据通常涉及以下几个关键操作：

1. 解析 JSON 数据：使用 JSON_EXTRACT 或 -> 操作符从 JSON 字符串中提取特定字段的值。
2. 生成 JSON 数据：使用 JSON_OBJECT 或 JSON_ARRAY 函数将 SQL 数据转换为 JSON 格式。
3. 查询 JSON 数据：使用 JSON_CONTAINS 或 JSON_SEARCH 函数在 JSON 数据中查找特定值或路径。
4. 修改 JSON 数据：使用 JSON_SET、JSON_REPLACE 或 JSON_REMOVE 函数来更新或删除 JSON 数据中的特定字段。

示例代码

// www.javascriptcn.com code example
-- 解析 JSON 数据
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') AS name;

-- 生成 JSON 数据
SELECT JSON_OBJECT('name', 'John', 'age', 30) AS json_data;

-- 查询 JSON 数据
SELECT JSON_CONTAINS('{"name": "John", "age": 30}', '30', '$.age') AS contains_age;

-- 修改 JSON 数据
SELECT JSON_SET('{"name": "John", "age": 30}', '$.age', 31) AS updated_json;

本题详细解读

1. 解析 JSON 数据

在 SQL 中，JSON_EXTRACT 函数用于从 JSON 字符串中提取特定字段的值。-> 操作符是 JSON_EXTRACT 的简写形式，通常用于更简洁的代码。

• JSON_EXTRACT(json_doc, path)：从 json_doc 中提取 path 指定的值。
• -> 操作符：json_doc->path 等同于 JSON_EXTRACT(json_doc, path)。

2. 生成 JSON 数据

JSON_OBJECT 和 JSON_ARRAY 函数用于将 SQL 数据转换为 JSON 格式。

• JSON_OBJECT(key1, value1, key2, value2, ...)：生成一个 JSON 对象，包含指定的键值对。
• JSON_ARRAY(value1, value2, ...)：生成一个 JSON 数组，包含指定的值。

3. 查询 JSON 数据

JSON_CONTAINS 和 JSON_SEARCH 函数用于在 JSON 数据中查找特定值或路径。

• JSON_CONTAINS(json_doc, value[, path])：检查 json_doc 中是否包含指定的 value，可选地在 path 指定的路径下查找。
• JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])：在 json_doc 中查找与 search_str 匹配的路径。

4. 修改 JSON 数据

JSON_SET、JSON_REPLACE 和 JSON_REMOVE 函数用于更新或删除 JSON 数据中的特定字段。

• JSON_SET(json_doc, path, value[, path, value] ...)：在 json_doc 中设置或更新 path 指定的字段为 value。
• JSON_REPLACE(json_doc, path, value[, path, value] ...)：替换 json_doc 中 path 指定的字段为 value。
• JSON_REMOVE(json_doc, path[, path] ...)：从 json_doc 中删除 path 指定的字段。

通过这些函数和操作符，SQL 可以有效地处理 JSON 数据，满足现代应用程序中对半结构化数据的需求。