如何在数据库中高效操作与查询 SQL JSON 数据类型？

SQL JSON 数据类型让关系型数据库也能灵活承载变化频繁的业务结构，像用户标签、配置项和日志明细都可以直接落在一个列里。掌握它的写入与查询方式，能减少表结构迁移带来的成本。

一、建表与写入 JSON 数据

在 MySQL 中可以使用 JSON 类型声明列，PostgreSQL 则使用 jsonb 获得更好的查询性能。下面以 MySQL 为例创建一张用户表：

CREATE TABLE user_profile (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  attr JSON
);

INSERT INTO user_profile (name, attr)
VALUES ('张三', '{"age": 28, "tags": ["vip", "active"], "addr": {"city": "北京"}}');

二、基础查询函数

1. 提取字段

使用 JSON_EXTRACT 或运算符 -> 可以取出指定路径的值，路径中 $ 表示当前文档。

SELECT
  name,
  JSON_EXTRACT(attr, '$.age') AS age,
  attr->'$.addr.city' AS city
FROM user_profile;

2. 条件过滤

JSON_CONTAINS 能判断数组或对象是否包含某内容，适合做标签筛选。

SELECT name
FROM user_profile
WHERE JSON_CONTAINS(attr->'$.tags', '"vip"');

三、嵌套结构与数组处理

当 JSON 内部是数组时，可以结合 JSON_TABLE 将元素展开成行，便于关联统计。

SELECT u.name, t.tag
FROM user_profile u,
     JSON_TABLE(u.attr->'$.tags', '$[*]' COLUMNS (tag VARCHAR(20) PATH '$')) t;

四、查询性能优化

直接对 JSON 列做函数查询通常会全表扫描。可以建立生成列并加索引来加速。

ALTER TABLE user_profile
ADD COLUMN age_int INT AS (attr->'$.age') STORED,
ADD INDEX idx_age (age_int);

SELECT name FROM user_profile WHERE age_int = 28;

五、注意事项

• 写入前校验 JSON 格式，避免非法串导致写入失败
• 频繁查询的字段尽量抽出生成列，减少运行期解析
• 过深的嵌套会增加维护难度，建议控制在三层以内

合理运用 SQL JSON 数据类型，既保留关系模型的约束，又兼顾灵活扩展，是多数中型业务系统的务实选择。

SQLJSON数据类型JSON查询JSON函数索引优化修改时间：2026-07-24 20:39:18