17370845950

SQL中JSON函数支持直接解析、查询和构造JSON数据，需掌握字段类型、提取操作（→/>>）、数组展开、构造修改等核心功能及索引优化技巧。

SQL 中的 JSON 函数让数据库能直接解析、查询和构造 JSON 数据，无需在应用层反复序列化/反序列化。关键在于理解各函数用途、输入输出格式及常见陷阱。

JSON 字段的创建与验证

PostgreSQL 和 MySQL（8.0+）都支持 JSON 或 JSONB 类型字段。建表时指定类型可自动校验合法性：

• MySQL：CREATE TABLE logs (id INT, data JSON); 插入非法 JSON（如缺少引号）会报错
• PostgreSQL：data JSONB 更高效，支持索引；用 jsonb_valid('{"a":1}') 可手动校验字符串
• 注意：字符串字面量必须用单引号包裹，内部双引号需转义或使用 $$...$$ 界定符（PG）

提取 JSON 中的值（→ 和 ->>）

这是最常用操作。区别在于返回类型：

• -> 返回 JSON 类型（保留结构，可用于嵌套提取）
• ->> 返回 TEXT 类型（自动去掉引号，适合直接参与比较或拼接）
• 示例（PostgreSQL）：SELECT payload->'user'->>'name' FROM events; 提取 user 对象下的 name 字符串
• MySQL 对应为 JSON_EXTRACT(data, '$.user.name') 和 JSON_UNQUOTE(JSON_EXTRACT(...))

查询含 JSON 数组的数据

数组需先展开再过滤。PostgreSQL 用 jsonb_array_elements()，MySQL 用 JSON_TABLE() 或 JSON_CONTAINS()：

• PG 示例：查 tags 包含 "sql" 的记录
  SELECT * FROM posts WHERE 'sql' = ANY(SELECT jsonb_array_elements_text(tags));
• MySQL 示例：SELECT * FROM products WHERE JSON_CONTAINS(features, '"premium"', '$');
• 避免全表扫描：对频繁查询的 JSON 数组字段建生成列 + 索引（如 PostgreSQL 的 GIN 索引）

构造与修改 JSON 数据

动态组装 JSON 常用于日志、配置或 API 响应生成：

• PostgreSQL：jsonb_build_object('id', id, 'status', status) 构造对象；jsonb_set(data, '{user,name}', '"Alice"') 更新字段
• MySQL：JSON_OBJECT('id', id, 'status', status)；JSON_SET(data, '$.user.name', 'Alice')
• 注意 null 处理：PG 中 jsonb_build_object() 会跳过 null 值；MySQL 中需显式用 IFNULL() 避免插入 NULL 导致整个 JSON 为 NULL

基本上就这些。用熟几个核心函数（提取、展开、构造），配合类型约束和索引，JSON 就不再是“黑盒”，而是可查、可算、可优化的一等数据公民。