SQL中如何处理JSON数据？SQL处理JSON数据的实现方法有哪些

随着非结构化数据存储需求的增加，JSON格式数据在数据库中的使用频率越来越高，主流关系型数据库大多已经内置了JSON数据处理的支持，开发者可以直接通过SQL语句完成JSON数据的各类操作。

一、JSON数据的存储方式

不同数据库对JSON数据的存储设计不同，主要分为两种类型：

• 原生JSON类型：如MySQL的JSON类型、PostgreSQL的JSON和JSONB类型，数据库会对JSON数据做格式校验和内部优化，查询效率更高。
• 文本类型存储：部分数据库没有原生JSON类型，会将JSON数据以VARCHAR或TEXT类型存储，需要手动保证数据格式正确。

二、常用JSON操作函数

1. 提取JSON字段值

提取JSON中的指定字段是最常用的操作，不同数据库的提取函数存在差异：

MySQL示例

MySQL使用->和->>运算符，或者JSON_EXTRACT函数提取字段：

-- 创建测试表
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    info JSON
);

-- 插入测试数据
INSERT INTO user_info VALUES (1, '{"name": "张三", "age": 25, "hobby": ["篮球", "阅读"]}');

-- 使用->提取字段，返回JSON格式
SELECT info->'$.name' AS json_name FROM user_info WHERE id = 1;

-- 使用->>提取字段，返回字符串格式
SELECT info->>'$.age' AS age FROM user_info WHERE id = 1;

-- 使用JSON_EXTRACT函数提取数组元素
SELECT JSON_EXTRACT(info, '$.hobby[0]') AS first_hobby FROM user_info WHERE id = 1;

PostgreSQL示例

PostgreSQL使用->和->>运算符，或者jsonb_extract_path函数：

-- 创建测试表
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    info JSONB
);

-- 插入测试数据
INSERT INTO user_info VALUES (1, '{"name": "李四", "age": 28, "address": {"city": "北京"}}');

-- 提取顶层字段，返回JSON类型
SELECT info->'name' AS json_name FROM user_info WHERE id = 1;

-- 提取顶层字段，返回文本类型
SELECT info->>'age' AS age FROM user_info WHERE id = 1;

-- 提取嵌套字段
SELECT info->'address'->>'city' AS city FROM user_info WHERE id = 1;

2. 修改JSON数据

部分数据库支持直接修改JSON内部的字段，不需要整体替换整个JSON值：

MySQL修改示例

-- 修改JSON中的age字段
UPDATE user_info 
SET info = JSON_SET(info, '$.age', 26) 
WHERE id = 1;

-- 新增JSON字段
UPDATE user_info 
SET info = JSON_INSERT(info, '$.gender', '男') 
WHERE id = 1;

-- 删除JSON字段
UPDATE user_info 
SET info = JSON_REMOVE(info, '$.hobby') 
WHERE id = 1;

PostgreSQL修改示例

-- 修改JSONB字段
UPDATE user_info 
SET info = info || '{"age": 29}'::JSONB 
WHERE id = 1;

-- 删除JSONB字段
UPDATE user_info 
SET info = info - 'address' 
WHERE id = 1;

3. JSON数据查询条件

可以在WHERE条件中使用JSON字段的值作为过滤条件：

-- MySQL中查询age大于25的用户
SELECT id, info->>'name' AS name 
FROM user_info 
WHERE CAST(info->>'age' AS UNSIGNED) > 25;

-- PostgreSQL中查询city为北京的用户
SELECT id, info->>'name' AS name 
FROM user_info 
WHERE info->'address'->>'city' = '北京';

三、JSON数据与关系数据的转换

有时候需要将JSON数组转换为多行关系数据，或者将多行数据聚合为JSON：

1. JSON数组转多行

MySQL可以使用JSON_TABLE函数实现：

-- 将hobby数组拆分为多行
SELECT u.id, jt.hobby_item
FROM user_info u
CROSS JOIN JSON_TABLE(u.info, '$.hobby[*]' COLUMNS (hobby_item VARCHAR(20) PATH '$')) jt
WHERE u.id = 1;

2. 多行数据聚合为JSON

PostgreSQL可以使用jsonb_agg函数实现：

-- 将所有用户的name和age聚合为JSON数组
SELECT jsonb_agg(jsonb_build_object('name', info->>'name', 'age', info->>'age')) AS user_list
FROM user_info;

四、注意事项

• 存储JSON数据前尽量保证格式正确，避免无效JSON导致函数执行失败。
• 频繁查询的JSON字段可以考虑添加虚拟列并建立索引，提升查询效率。
• 不同数据库的JSON函数差异较大，编写代码前需要确认当前数据库的支持情况。
• 不建议将过长的JSON数据存储在关系型数据库中，复杂场景可以考虑使用文档型数据库。

SQLJSON数据JSON函数数据解析数据查询修改时间：2026-07-12 19:09:28