SQL中如何处理JSON数据？常用JSON函数使用示例详解

在关系型数据库中，JSON类型字段的引入让结构化数据和非结构化数据的存储更加灵活，开发者可以直接将JSON格式的数据存入数据库表，无需提前拆分字段。不过要高效操作这些JSON数据，就需要掌握对应的SQL JSON函数。

常见SQL JSON函数分类

不同数据库的JSON函数语法略有差异，但核心功能可以分为以下几类：

• 提取类函数：从JSON数据中获取指定路径的值
• 修改类函数：更新JSON数据中的指定内容
• 判断类函数：检查JSON数据是否包含指定路径或值
• 转换类函数：将JSON数据转换为其他格式，或把其他格式转为JSON

MySQL中JSON函数使用示例

提取JSON字段内容

假设有一张用户表user_info，其中ext_info是JSON类型字段，存储用户的额外信息，结构如下：

-- 创建测试表
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    ext_info JSON
);

-- 插入测试数据
INSERT INTO user_info VALUES 
(1, '张三', '{"age": 25, "hobby": ["篮球", "阅读"], "address": {"city": "北京", "district": "海淀区"}}'),
(2, '李四', '{"age": 30, "hobby": ["足球"], "address": {"city": "上海", "district": "浦东新区"}}');

要提取用户的年龄，可以使用JSON_EXTRACT函数，也可以使用->和->>运算符，后者会自动去除结果的引号：

-- 使用JSON_EXTRACT提取年龄
SELECT id, name, JSON_EXTRACT(ext_info, '$.age') AS age FROM user_info;

-- 使用->运算符提取年龄（返回带引号的JSON值）
SELECT id, name, ext_info->'$.age' AS age FROM user_info;

-- 使用->>运算符提取年龄（返回普通字符串/数值）
SELECT id, name, ext_info->>'$.age' AS age FROM user_info;

-- 提取嵌套的地址城市
SELECT id, name, ext_info->>'$.address.city' AS city FROM user_info;

修改JSON字段内容

如果需要更新用户的年龄，或者新增一个手机号字段，可以使用JSON_SET函数：

-- 更新id为1的用户年龄为26，新增手机号字段
UPDATE user_info 
SET ext_info = JSON_SET(ext_info, '$.age', 26, '$.phone', '13800138000') 
WHERE id = 1;

-- 查看更新后的结果
SELECT id, ext_info->>'$.age' AS age, ext_info->>'$.phone' AS phone FROM user_info WHERE id = 1;

判断JSON是否包含指定内容

使用JSON_CONTAINS函数可以判断JSON数组中是否包含指定值：

-- 查询爱好包含篮球的用户
SELECT id, name FROM user_info 
WHERE JSON_CONTAINS(ext_info->'$.hobby', '"篮球"');

PostgreSQL中JSON函数使用示例

PostgreSQL支持json和jsonb两种JSON类型，jsonb存储效率更高，查询速度更快，以下示例基于jsonb类型：

-- 创建测试表
CREATE TABLE user_info_pg (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    ext_info JSONB
);

-- 插入测试数据
INSERT INTO user_info_pg VALUES 
(1, '张三', '{"age": 25, "hobby": ["篮球", "阅读"], "address": {"city": "北京", "district": "海淀区"}}'),
(2, '李四', '{"age": 30, "hobby": ["足球"], "address": {"city": "上海", "district": "浦东新区"}}');

提取JSON内容

PostgreSQL使用->获取JSON对象，->>获取文本值，路径操作符#>和#>>支持更深层的路径提取：

-- 提取用户的年龄（返回jsonb类型）
SELECT id, name, ext_info->'age' AS age FROM user_info_pg;

-- 提取用户的年龄（返回文本类型）
SELECT id, name, ext_info->>'age' AS age FROM user_info_pg;

-- 提取嵌套的城市信息
SELECT id, name, ext_info#>'{address,city}' AS city FROM user_info_pg;
SELECT id, name, ext_info#>>'{address,city}' AS city FROM user_info_pg;

修改JSON内容

使用jsonb_set函数修改JSON内容：

-- 更新id为1的用户年龄为26，新增手机号
UPDATE user_info_pg 
SET ext_info = jsonb_set(ext_info, '{age}', '26') || '{"phone": "13800138000"}' 
WHERE id = 1;

-- 查看更新结果
SELECT id, ext_info->>'age' AS age, ext_info->>'phone' AS phone FROM user_info_pg WHERE id = 1;

JSON数据查询

PostgreSQL支持直接对JSON字段进行条件查询，也可以使用@>包含运算符判断：

-- 查询年龄为25的用户
SELECT id, name FROM user_info_pg WHERE ext_info->>'age' = '25';

-- 查询地址在上海的用户
SELECT id, name FROM user_info_pg WHERE ext_info@>'{"address": {"city": "上海"}}';

使用注意事项

• 不同数据库的JSON函数语法差异较大，编写代码前需要确认对应数据库的官方文档
• 尽量避免对JSON字段做复杂的嵌套查询，会影响查询性能，高频查询的字段可以冗余为普通列并建立索引
• 存储JSON数据时，尽量保持结构统一，方便后续的数据提取和维护
• 对JSONB类型字段可以创建GIN索引，提升JSON内容的查询效率

SQLJSONJSON函数数据解析修改时间：2026-06-22 21:57:58