JSON 字段处理

现代数据库中，许多支持存储和操作 JSON 数据格式的字段类型，使得处理结构化和半结构化数据变得方便。以下这些知识点涵盖 JSON 字段的存储、查询、更新和常用函数。

JSON 的存储

1. 存储方式：
   • 原生 JSON 类型：如 MySQL 的 JSON 或 PostgreSQL 的 JSONB，提供高效存储和索引。
   • 字符串存储：JSON 数据可以存储为 TEXT 或 VARCHAR 字段，但查询和性能有限。
2. JSONB（PostgreSQL 专有）：
   • 二进制格式存储，性能优于普通 JSON。
   • 支持索引优化和快速查询。
   • 无序存储（键值对顺序可能变化）。

JSON 字段的操作

1. 插入 JSON 数据

-- MySQL
INSERT INTO my_table (json_column) VALUES ('{"name": "Alice", "age": 25}');
-- PostgreSQL
INSERT INTO my_table (json_column) VALUES ('{"name": "Alice", "age": 25}'::jsonb);

---

2. 查询 JSON 数据

MySQL 常用 JSON 函数

函数/操作	描述	示例
JSON_EXTRACT	提取 JSON 数据的特定路径	SELECT JSON_EXTRACT(json_column, '$.name')
->	提取 JSON 中的键值，返回 JSON 格式	SELECT json_column->'$.name'
->>	提取 JSON 中的键值，返回字符串	SELECT json_column->>'$.name'
JSON_CONTAINS	判断 JSON 是否包含特定的键值	SELECT JSON_CONTAINS(json_column, '"Alice"', '$.name')
JSON_KEYS	获取 JSON 对象的所有键	SELECT JSON_KEYS(json_column)
JSON_ARRAY	创建 JSON 数组	SELECT JSON_ARRAY('Alice', 'Bob')
JSON_OBJECT	创建 JSON 对象	SELECT JSON_OBJECT('name', 'Alice')

PostgreSQL 常用 JSON 函数

函数/操作	描述	示例
->	提取 JSON 对象或数组中的子元素，返回 JSON 格式	SELECT json_column->'name'
->>	提取 JSON 对象或数组中的子元素，返回文本格式	SELECT json_column->>'name'
#>	根据路径提取嵌套 JSON 数据，返回 JSON 格式	SELECT json_column#>'{address, city}'
#>>	根据路径提取嵌套 JSON 数据，返回文本格式	SELECT json_column#>>'{address, city}'
jsonb_array_elements	将 JSON 数组的每个元素拆分为独立行	SELECT jsonb_array_elements(json_column)
jsonb_set	更新 JSON 数据中的某个键值	SELECT jsonb_set(json_column, '{age}', '30')
jsonb_each	将 JSON 对象的每个键值对拆分为独立行	SELECT * FROM jsonb_each(json_column)

---

3. 更新 JSON 数据

MySQL

-- 更新 JSON 字段中的特定键值
UPDATE my_table
SET json_column = JSON_SET(json_column, '$.name', 'Bob')
WHERE id = 1;

PostgreSQL

-- 使用 jsonb_set 更新字段
UPDATE my_table
SET json_column = jsonb_set(json_column, '{name}', '"Bob"')
WHERE id = 1;

---

4. 删除 JSON 数据中的键值

MySQL

-- 删除 JSON 中的键
UPDATE my_table
SET json_column = JSON_REMOVE(json_column, '$.age')
WHERE id = 1;

PostgreSQL

-- 删除 JSONB 中的键
UPDATE my_table
SET json_column = json_column - 'age'
WHERE id = 1;

---

5. JSON 索引

MySQL

• 虚拟列索引：

  ALTER TABLE my_table
  ADD COLUMN name_virtual VARCHAR(255) GENERATED ALWAYS AS (json_column->>"$.name") STORED,
  ADD INDEX idx_name_virtual (name_virtual);
  

PostgreSQL

• GIN 索引（适用于 JSONB）：

  CREATE INDEX idx_jsonb ON my_table USING GIN (json_column);
  

JSON 数据的应用场景

1. 存储半结构化数据：
   • 如日志信息、配置文件、第三方 API 返回结果等。
2. 动态字段：
   • 适用于字段结构不固定的场景，比如用户自定义配置。
3. 嵌套结构：
   • 如订单详情、用户信息等复杂嵌套数据的存储。
4. 全文搜索和过滤：
   • 结合 JSON 索引进行复杂的过滤和全文搜索。

注意事项

1. 选择合适的 JSON 数据类型：
   • 如果频繁查询、更新，优先选择 JSONB（如 PostgreSQL）。
   • 如果仅存储少量 JSON 数据，TEXT 类型可能更合适。
2. 性能问题：
   • JSON 查询性能可能低于传统的关系型表设计。
   • 索引优化是关键，特别是对嵌套结构的访问。
3. 数据验证：
   • 数据插入前需进行 JSON 格式验证（如使用 IS JSON 检查）。
4. 分离大字段：
   • 对于超大 JSON 数据，可以考虑单独拆表存储，提高主表性能。

总结

JSON 数据类型扩展了关系型数据库的功能，能够灵活存储和操作半结构化数据。熟练掌握常用函数（如提取、更新、删除）以及索引优化技巧，可以显著提高处理 JSON 数据的效率。

相关处理函数

MySQL 提供了一些 json 类型的处理函数，包括：

1. json_array()：创建一个 json 数组。
2. json_object()：创建一个 json 对象。
3. json_insert()：将值插入到 json 文档中。
4. json_replace()：替换 json 文档中的值。
5. json_remove()：从 json 文档中删除值。
6. json_set()：在 json 文档中设置一个值。
7. json_contains()：如果 json 文档包含特定值，返回 1，否则返回 0。
8. json_contains_path()：如果 json 文档包含在指定路径上的数据，返回 1，否则返回 0。
9. json_extract() 或 ->：从 json 文档中提取一个值。（结果值会带有 引号）
   • SELECT JSON_EXTRACT('{"name":"John"}', '$.name');
   • SELECT JSON_EXTRACT('{"name":"John"}', '$.name');
   • 获取结果为 "John"
10. json_unquote()：删除引号。
    • 执行 SELECT JSON_UNQUOTE('{"name":"John"}'->'$.name'); 获取结果为 John
11. json_keys()：从 json 对象中抽取所有的键作为一个 json 数组。
12. json_length()：返回 json 文档中的元素个数。
13. json_depth()：返回 json 文档的最大深度。
14. json_search()：在 json 文档中搜索给定的字符串。
15. json_valid()：如果一个 json 字符串格式正确，则返回 1，否则返回 0。
16. json_type()：返回一个 json 值的类型，如对象、数组、字符串、数字等等。

这些函数提供了对 json 的基本操作，如插入、替换、删除、设置和提取值等。你可以根据你的具体需求，选择使用这些函数。