SQL怎么查询JSON数据:全面指南与实用技巧
在现代数据库应用中,JSON(JavaScript Object Notation)已成为数据交换的通用格式,因其灵活性和可读性被广泛用于存储半结构化数据,无论是MySQL、PostgreSQL、SQL Server还是Oracle等主流数据库,都逐渐增强了JSON数据的查询能力,本文将详细介绍SQL中查询JSON数据的常用方法、函数及实战案例,帮助 you 快速这一技能。
为什么要在SQL中查询JSON数据?
传统关系型数据库中,数据通常以结构化表存储(如users表包含id、name、email等字段),但在实际场景中,我们常遇到以下需求:
- 灵活扩展字段:用户可能动态添加属性(如
address、preferences等),若新增表字段需修改表结构,而JSON可直接存储动态属性; - 存储嵌套数据:如订单信息包含商品列表、收货地址等嵌套结构,用JSON可避免多表关联的复杂性;
- API数据交互:许多现代API返回JSON格式数据,直接存入数据库后需高效解析和查询。
SQL查询JSON数据的能力,能显著提升数据处理效率和灵活性。
主流数据库的JSON查询语法
不同数据库对JSON的支持略有差异,但核心逻辑类似:通过路径表达式定位JSON中的字段,再使用内置函数提取或过滤数据,下面以最常用的MySQL(8.0+)、PostgreSQL、SQL Server为例展开说明。
(一)MySQL 8.0+:JSON函数与路径表达式
MySQL提供了丰富的JSON函数,其中最常用的是JSON_EXTRACT()、->(简写)、->>(提取并转义为文本)、JSON_CONTAINS()、JSON_TABLE()等。
基本语法:提取JSON字段
假设有一张users表,结构如下:
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
profile JSON -- 存储用户扩展信息,如地址、偏好等
);
插入测试数据:
INSERT INTO users VALUES
(1, 'Alice', '{"age": 25, "address": {"city": "Beijing", "district": "Haidian"}, "hobbies": ["reading", "swimming"]}'),
(2, 'Bob', '{"age": 30, "address": {"city": "Shanghai", "district": "Pudong"}, "hobbies": ["coding", "gaming"]}'),
(3, 'Charlie', '{"age": 28, "address": {"city": "Guangzhou", "district": "Tianhe"}, "hobbies": ["sports", "music"]}');
常用函数示例
(1)提取JSON字段的值
- 使用
JSON_EXTRACT()(标准语法,需指定路径):
-- 提取age字段 SELECT id, name, JSON_EXTRACT(profile, '$.age') AS age FROM users;
- 使用
->(简写,返回JSON对象):
-- 提取address对象(JSON格式) SELECT id, name, profile->'$.address' AS address FROM users;
- 使用
->>(提取并转为文本,适合简单值):
-- 提取city字段(文本格式) SELECT id, name, profile->>'$.address.city' AS city FROM users;
输出结果: | id | name | address | city | |----|---------|-----------------------------|----------| | 1 | Alice | {"city": "Beijing", ...} | Beijing | | 2 | Bob | {"city": "Shanghai", ...} | Shanghai | | 3 | Charlie | {"city": "Guangzhou", ...} | Guangzhou|
(2)查询JSON数组中的元素
若JSON字段包含数组(如hobbies),可通过$[*]或$[index]提取:
-- 提取所有hobbies数组 SELECT id, name, profile->'$.hobbies' AS hobbies FROM users; -- 提取第一个hobby(索引从0开始) SELECT id, name, profile->'$.hobbies[0]' AS first_hobby FROM users;
(3)条件查询:JSON_CONTAINS()
判断JSON是否包含特定值:
-- 查询hobbies包含"reading"的用户 SELECT * FROM users WHERE JSON_CONTAINS(profile, '"reading"', '$.hobbies');
注意:第三个参数'$.hobbies'指定了搜索路径,若省略则默认在整个JSON中搜索。
(4)复杂查询:JSON_TABLE()(将JSON转为虚拟表)
若需对JSON中的数组或嵌套对象进行多表关联查询,JSON_TABLE()是利器:
-- 将hobbies数组转为虚拟表,查询每个用户的爱好
SELECT u.id, u.name, h.hobby
FROM users u,
JSON_TABLE(
profile,
'$.hobbies[*]' COLUMNS(hobby VARCHAR(50) PATH '$')
) AS h;
输出结果: | id | name | hobby | |----|---------|------------| | 1 | Alice | reading | | 1 | Alice | swimming | | 2 | Bob | coding | | 2 | Bob | gaming | | 3 | Charlie | sports | | 3 | Charlie | music |
(二)PostgreSQL:原生JSONB与路径查询
PostgreSQL对JSON的支持更强大,提供了JSON(存储原始JSON)和JSONB(二进制存储,支持索引)两种类型,推荐使用JSONB,其核心语法是->、->>、#>(嵌套路径)等。
表结构与数据
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_no VARCHAR(20),
details JSONB -- 存储订单详情,如商品、价格等
);
INSERT INTO orders (order_no, details) VALUES
('ORD001', '{"customer": "Alice", "items": [{"name": "Laptop", "price": 5999}, {"name": "Mouse", "price": 99}], "total": 6098}'),
('ORD002', '{"customer": "Bob", "items": [{"name": "Keyboard", "price": 299}], "total": 299}');
常用查询示例
(1)提取字段值
-- 提取customer(文本) SELECT order_no, details->>'customer' AS customer FROM orders; -- 提取第一个商品名称(嵌套路径) SELECT order_no, details->'items'->0->>'name' AS first_item_name FROM orders;
(2)条件查询:@>(包含)、<@(被包含)
-- 查询details包含"price": 5999的订单
SELECT * FROM orders WHERE details @> '{"price": 5999}';
-- 查询details被{"customer": "Alice"}包含的订单
SELECT * FROM orders WHERE details <@ '{"customer": "Alice"}';
(3)查询JSON数组:jsonb_array_elements()
-- 展开items数组,查询每个商品详情 SELECT order_no, item->>'name' AS item_name, item->>'price' AS item_price FROM orders, LATERAL jsonb_array_elements(details->'items') AS item;
说明:LATERAL允许子查询引用父查询的列,类似JOIN。
(三)SQL Server:JSON_VALUE()与OPENJSON()
SQL Server 2016+开始支持JSON,主要通过JSON_VALUE()(提取单值)、JSON_QUERY()(提取JSON对象/数组)、OPENJSON()(转为表)函数实现。
表结构与数据
CREATE TABLE products (
id INT PRIMARY KEY,
name NVARCHAR(100),
attributes NVARCHAR(MAX) -- 存储JSON格式的属性
);
INSERT INTO products VALUES
(1, 'Laptop', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}, "warranty": 2}'),
(2, 'Phone', '{"brand": "Apple", "specs": {"cpu": "A15", "ram": "8GB"}, "warranty": 1}');
常用查询示例
(1)提取单值:JSON_VALUE()
-- 提取brand字段 SELECT id, name, JSON_VALUE(attributes, '$.brand') AS brand FROM products; -- 提取嵌套的cpu字段 SELECT id, name, JSON_VALUE(attributes, '$.specs.cpu') AS cpu FROM products;
(2)提取JSON对象/数组:JSON_QUERY()
-- 提取specs对象(JSON格式) SELECT id



还没有评论,来说两句吧...