SQL怎么从json串中取数据

谷歌浏览器谷歌浏览器谷歌浏览器下载谷歌浏览器下载谷歌浏览器谷歌浏览器谷歌浏览器下载谷歌浏览器下载

SQL怎么从JSON串中取数据：实用指南与代码示例

在现代数据库应用中,JSON（JavaScript Object Notation）因轻量、灵活的特性，常作为数据交换格式存储在数据库字段中，无论是用户配置、动态属性还是日志数据，JSON串都能高效存储半结构化数据，但如何从这些JSON串中精准提取所需数据？本文将结合主流数据库（MySQL、PostgreSQL、SQL Server、Oracle），详细介绍SQL操作JSON数据的实用方法。

从JSON串中提取数据的核心方法

不同数据库对JSON的支持略有差异,但核心思路一致：通过“路径定位”或“函数解析”从JSON结构中提取目标值，以下是通用方法及具体实现。

MySQL：JSON函数全解析

MySQL 5.7+ 和 MariaDB 10.2+ 提供了丰富的JSON函数，支持提取标量值、数组、嵌套对象等。

（1）提取JSON中的标量值（字符串、数字、布尔值）

假设有表user_config，字段config存储JSON串：

{"name":"张三","age":25,"is_active":true,"address":{"city":"北京","district":"朝阳区"}}

提取顶层字段：使用->>（提取并转为JSON类型）或->（提取并保留JSON格式）

-- 提取name（返回字符串）
SELECT config->>'$.name' AS name FROM user_config;
-- 结果："张三"
-- 提取age（返回数字，注意JSON数字会被转为字符串，需用CAST转换）
SELECT CAST(config->>'$.age' AS UNSIGNED) AS age FROM user_config;
-- 结果：25

提取嵌套字段：通过表示根对象，用访问子对象

-- 提取城市
SELECT config->>'$.address.city' AS city FROM user_config;
-- 结果："北京"

（2）提取JSON数组元素

若JSON串包含数组,如tags字段：["技术","编程","数据库"]，可通过JSON_EXTRACT结合索引提取：

-- 提取第一个标签（索引从0开始）
SELECT JSON_EXTRACT(tags, '$[0]') AS first_tag FROM user_config;
-- 结果："技术"
-- 提取所有标签（转为MySQL表函数，需搭配JSON_TABLE）
SELECT * FROM user_config, JSON_TABLE(
    tags, '$[*]' COLUMNS(tag VARCHAR(50) PATH '$')
) AS tags;
-- 结果：
-- tag
-- 技术
-- 编程
-- 数据库

（3）修改JSON字段（若需更新后提取）

使用JSON_SET、JSON_MODIFY（MySQL 8.0+）等函数修改JSON后，再提取：

-- 修改年龄为26，再提取
SELECT JSON_SET(config, '$.age', 26)->>'$.age' AS new_age FROM user_config;
-- 结果：26

PostgreSQL：原生JSON/JSONB支持

PostgreSQL对JSON的支持更强大,JSON（存储原始文本）和JSONB（二进制存储，支持索引）类型可直接用#>、#>>等操作符提取数据。

（1）基本提取操作

同样以user_config表为例，config字段为JSONB类型：

-- 提取顶层字段（#>>：转为文本；#>：保留JSONB）
SELECT config#>>'{name}' AS name FROM user_config;
-- 结果："张三"
-- 提取嵌套字段（路径用数组表示）
SELECT config#>>'{address,city}' AS city FROM user_config;
-- 结果："北京"

（2）提取JSON数组与动态字段

PostgreSQL的jsonb_array_elements可展开数组：

-- 提取tags数组所有元素
SELECT value AS tag FROM user_config, jsonb_array_elements(config->'tags') AS value;
-- 结果：
-- tag
-- "技术"
-- "编程"
-- "数据库"

（3）复杂查询：结合WHERE条件筛选JSON数据

直接在WHERE子句中提取JSON字段并筛选：

-- 查询年龄大于25的用户
SELECT * FROM user_config WHERE (config->>'age')::int > 25;
-- 查询地址为“北京”的用户
SELECT * FROM user_config WHERE config#>>'{address,city}' = '北京';

SQL Server：JSON_VALUE与OPENJSON

SQL Server 2016+ 开始支持JSON，核心函数为JSON_VALUE（提取标量值）和OPENJSON（提取表格式数据）。

（1）提取标量值：JSON_VALUE

-- 提取name（返回nvarchar）
SELECT JSON_VALUE(config, '$.name') AS name FROM user_config;
-- 结果：张三
-- 提取嵌套字段
SELECT JSON_VALUE(config, '$.address.city') AS city FROM user_config;
-- 结果：北京
-- 提取数字并计算（注意JSON_VALUE返回nvarchar，需转换类型）
SELECT CAST(JSON_VALUE(config, '$.age') AS int) + 1 AS next_age FROM user_config;
-- 结果：26

（2）提取复杂结构：OPENJSON

若需提取数组或嵌套对象,用OPENJSON：

-- 提取tags数组（需先配置JSON路径）
SELECT value AS tag FROM user_config
CROSS APPLY OPENJSON(config, '$.tags') AS tags;
-- 结果：
-- tag
-- 技术
-- 编程
-- 数据库
-- 提取嵌套对象为表
SELECT city, district FROM user_config
CROSS APPLY OPENJSON(config, '$.address') WITH (
    city nvarchar(50) '$.city',
    district nvarchar(50) '$.district'
) AS address;
-- 结果：
-- city     district
-- 北京     朝阳区

（3）JSON数据修改与插入

-- 修改JSON字段中的age
UPDATE user_config SET config = JSON_MODIFY(config, '$.age', 26);
-- 插入包含JSON的新数据
INSERT INTO user_config (id, config) VALUES (1, '{"name":"李四","age":30,"tags":["阅读","旅行"]}');

Oracle：JSON_TABLE与JSON_VALUE

Oracle 12c+ 提供了完整的JSON支持，通过JSON_TABLE（将JSON转为关系表）和JSON_VALUE（提取标量值）操作。

（1）提取标量值：JSON_VALUE

-- 提取name（返回VARCHAR2）
SELECT JSON_VALUE(config, '$.name') AS name FROM user_config;
-- 结果：张三
-- 提取嵌套字段（Oracle路径语法支持点号）
SELECT JSON_VALUE(config, '$.address.city') AS city FROM user_config;
-- 结果：北京

（2）提取JSON数组/对象：JSON_TABLE

-- 提取tags数组所有元素
SELECT tag.value AS tag FROM user_config, 
JSON_TABLE(
    config, '$.tags[*]' COLUMNS(tag VARCHAR2(50) PATH '$')
) AS tags;
-- 结果：
-- tag
-- 技术
-- 编程
-- 数据库
-- 提取嵌套对象并转为列
SELECT address.city, address.district FROM user_config, 
JSON_TABLE(
    config, '$.address' COLUMNS(
        city VARCHAR2(50) PATH '$.city',
        district VARCHAR2(50) PATH '$.district'
    )
) AS address;
-- 结果：
-- city     district
-- 北京     朝阳区

（3）JSON数据更新

-- 修改age字段
UPDATE user_config SET config = JSON_TRANSFORM(config, SET '$.age' = 26);
-- 插入JSON数据
INSERT INTO user_config (id, config) VALUES (1, '{"name":"王五","age":28,"address":{"city":"上海","district":"浦东"}}');

跨数据库通用技巧与注意事项

路径语法规范

根对象：用表示。
字段访问：（如$.name）或['field']（如$['address']['city']，适用于字段名含特殊字符）。
数组访问：$[index]（索引从0开始），$[*]（所有元素）。

数据类型转换

JSON字段提取后通常为字符串类型（如MySQL的->>、SQL Server的JSON_VALUE），需根据业务需求转换为数字、日期等，

MySQL: CAST(config->>'$.age' AS UNSIGNED)
PostgreSQL: (config->>'age')::int
SQL Server: CAST(JSON_VALUE(config, '$.age') AS int)

性能优化

索引JSON字段：PostgreSQL的JSONB支持GIN索引，MySQL 8.0+

正文

SQL怎么从json串中取数据