sql怎么查询json数据

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

正文

sql怎么查询json数据