sql中怎么解析json数据 json数据解析的详细步骤-SQL-PHP中文网

sql中怎么解析json数据 json数据解析的详细步骤

穿越時空

发布： 2025-07-02 12:00:03

原创

867人浏览过

在sql中解析json数据可以通过数据库内置函数实现，mysql使用json_extract()或->操作符提取值，json_set更新，json_remove删除，json_table展开数组；postgresql用->和->>取值，jsonb_set更新，#-删除，jsonb_array_elements展开数组。处理嵌套数据需指定多层路径，优化性能应使用jsonb、创建索引、避免全表扫描及优化sql语句。

sql中怎么解析json数据 json数据解析的详细步骤

在SQL中解析JSON数据，其实就是把原本“一坨”的JSON字符串，拆解成可以被SQL语句直接操作的字段或者表格。这让你可以像操作普通数据库表一样，查询、过滤、排序JSON数据。听起来很酷，对吧？

解决方案

JSON解析在SQL中主要依赖于数据库系统提供的内置函数或者扩展。不同的数据库，方法略有不同，这里以MySQL和PostgreSQL为例，展示一些常见的解析方法。

MySQL:

MySQL 5.7.22及更高版本原生支持JSON数据类型和相关的函数。

提取JSON对象中的值:

使用JSON_EXTRACT()函数，或者更简洁的->操作符。
```
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- 返回 "John"
SELECT '{"name": "John", "age": 30}'->'$.age'; -- 返回 30
```
登录后复制
$表示JSON文档的根节点，.name表示提取name字段的值。
更新JSON对象:

使用JSON_SET()函数。
```
SELECT JSON_SET('{"name": "John", "age": 30}', '$.age', 31); -- 返回 '{"name": "John", "age": 31}'
```
登录后复制
这会创建一个新的JSON文档，age字段的值被更新为31。

删除JSON对象中的键:

使用JSON_REMOVE()函数。

SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age'); -- 返回 '{"name": "John"}'

登录后复制

将JSON数组展开为多行数据:

这个稍微复杂一点，需要配合其他函数。假设你有一个包含JSON数组的字段，你需要将数组中的每个元素提取出来。一种方法是使用JSON_TABLE()函数（MySQL 8.0+）。
```
SELECT *
FROM your_table,
JSON_TABLE(your_table.json_column, '$[*]' COLUMNS (
    id INT PATH '$.id',
    name VARCHAR(255) PATH '$.name'
)) AS jt;
```
登录后复制
这里your_table.json_column是包含JSON数组的字段，'$[*]'表示遍历数组中的所有元素，COLUMNS定义了提取的字段及其数据类型。

PostgreSQL:

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

查看详情

PostgreSQL原生支持JSON和JSONB数据类型（JSONB是JSON的二进制格式，更高效）。

提取JSON对象中的值:

使用->和->>操作符。

SELECT '{"name": "John", "age": 30}'::json -> 'name'; -- 返回 "John" (json类型)
SELECT '{"name": "John", "age": 30}'::json ->> 'name'; -- 返回 John (text类型)

登录后复制

->返回的是JSON类型，->>返回的是文本类型。

更新JSON对象:

使用jsonb_set()函数。
```
SELECT jsonb_set('{"name": "John", "age": 30}'::jsonb, '{age}', '31'::jsonb); -- 返回 '{"name": "John", "age": 31}'
```
登录后复制
{age}表示要更新的键的路径，'31'::jsonb是新的值。

删除JSON对象中的键:

使用#-操作符。

SELECT '{"name": "John", "age": 30}'::jsonb #- '{age}'; -- 返回 '{"name": "John"}'

登录后复制

将JSON数组展开为多行数据:

使用jsonb_array_elements()函数。
```
SELECT value
FROM your_table,
jsonb_array_elements(your_table.json_column);
```
登录后复制
jsonb_array_elements()将JSON数组展开为多行，value列包含数组中的每个元素。进一步提取元素中的字段，可以结合->>操作符。

如何处理嵌套JSON数据？

处理嵌套JSON数据，关键在于正确指定路径。

MySQL: 使用JSON_EXTRACT()或->操作符时，路径可以包含多个层级，例如'$.address.city'。
PostgreSQL: 使用->和->>操作符时，也可以使用多层路径，例如'{"address": {"city": "New York"}}'::json -> 'address' ->> 'city'。