在SQL Server中对JSON的各种处理

在 SQL Server 中，可以使用内置的 JSON 函数来解析和查询 JSON 数据。SQL Server 从 2016 版本开始支持 JSON 功能。以下是解析 JSON 数据的详细方法和示例：

---

1. 检查 JSON 数据

ISJSON()

ISJSON 函数用于检查字符串是否为有效的 JSON。

DECLARE @json NVARCHAR(MAX) = '{"name": "Alice", "age": 25}';
SELECT ISJSON(@json) AS is_valid_json;

输出：

is_valid_json
1

如果返回 1，表示字符串是有效的 JSON；返回 0，表示无效。

---

2. 解析 JSON 对象

JSON_VALUE()

JSON_VALUE 用于从 JSON 中提取标量值（如字符串、数字）。

DECLARE @json NVARCHAR(MAX) = '{"name": "Alice", "age": 25}';
SELECT 
    JSON_VALUE(@json, '$.name') AS name,
    JSON_VALUE(@json, '$.age') AS age;

输出：

name	age
Alice	25

---

3. 解析 JSON 数组

JSON_QUERY()

JSON_QUERY 用于从 JSON 中提取数组或对象。

DECLARE @json NVARCHAR(MAX) = '{"name": "Alice", "hobbies": ["reading", "swimming"]}';
SELECT 
    JSON_QUERY(@json, '$.hobbies') AS hobbies;

输出：

hobbies
["reading", "swimming"]

---

4. 解析嵌套 JSON

JSON_VALUE() 和 JSON_QUERY()

对于嵌套的 JSON，可以组合使用 JSON_VALUE 和 JSON_QUERY。

DECLARE @json NVARCHAR(MAX) = '{"user": {"name": "Alice", "age": 25}}';
SELECT 
    JSON_VALUE(@json, '$.user.name') AS name,
    JSON_VALUE(@json, '$.user.age') AS age;

输出：

name	age
Alice	25

---

5. 解析 JSON 数组中的元素

使用 OPENJSON 函数将 JSON 数组解析为表格形式。

DECLARE @json NVARCHAR(MAX) = '[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]';
SELECT 
    *
FROM 
    OPENJSON(@json)
WITH (
    name NVARCHAR(50) '$.name',
    age INT '$.age'
);

输出：

name	age
Alice	25
Bob	30

---

6. 解析嵌套 JSON 数组

对于嵌套的 JSON 数组，可以多次调用 OPENJSON。

DECLARE @json NVARCHAR(MAX) = '{"user": {"name": "Alice", "hobbies": ["reading", "swimming"]}}';

SELECT 
    JSON_VALUE(@json, '$.user.name') AS name,
    value AS hobby
FROM 
    OPENJSON(JSON_QUERY(@json, '$.user.hobbies'));

输出：

name	hobby
Alice	reading
Alice	swimming

---

7. 查询 JSON 中的数据

可以通过 WHERE 子句筛选 JSON 数据。

DECLARE @json NVARCHAR(MAX) = '[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]';
SELECT 
    name, 
    age
FROM 
    OPENJSON(@json)
WITH (
    name NVARCHAR(50) '$.name',
    age INT '$.age'
)
WHERE 
    age > 25;

输出：

name	age
Bob	30

---

8. 将 JSON 数据插入到表中

使用 OPENJSON 将 JSON 数据插入到表中。

DECLARE @json NVARCHAR(MAX) = '[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]';

INSERT INTO users (name, age)
SELECT 
    name, 
    age
FROM 
    OPENJSON(@json)
WITH (
    name NVARCHAR(50) '$.name',
    age INT '$.age'
);

---

9. 将表数据转换为 JSON

使用 FOR JSON 将表数据转换为 JSON 格式。

SELECT 
    name, 
    age
FROM 
    users
FOR JSON AUTO;

输出：

[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}]

---

10. 处理 NULL 值

默认情况下，JSON_VALUE 和 OPENJSON 会忽略 NULL 值。如果需要保留 NULL，可以使用 ISNULL 或 COALESCE。

DECLARE @json NVARCHAR(MAX) = '{"name": "Alice", "age": null}';
SELECT 
    JSON_VALUE(@json, '$.name') AS name,
    ISNULL(JSON_VALUE(@json, '$.age'), 'N/A') AS age;

输出：

name	age
Alice	N/A

---

11. 实战操作

公司的一个用餐消费记录使用JSON保存在数据库中，如下图： 这一列的类型是text,然而在使用JSON_VALUE函数解析JSON的时候，参数需要时varchar才行。所以我这里使用 CAST(CONVERT 函数 也可以)将 TEXT 转换为 NVARCHAR(MAX)，然后再对JSON进行查询。具体的查询

SELECT 
JSON_VALUE(CAST(JOB_DATA AS NVARCHAR(MAX)), '$.cost_id') as cost_id,
JSON_VALUE(CAST(JOB_DATA AS NVARCHAR(MAX)), '$.cost_fee') as cost_fee,
JSON_VALUE(CAST(JOB_DATA AS NVARCHAR(MAX)), '$.cost_device') as cost_device,
JSON_VALUE(CAST(JOB_DATA AS NVARCHAR(MAX)), '$.cost_user') as "cost_user",
JSON_VALUE(CAST(JOB_DATA AS NVARCHAR(MAX)), '$.cost_type') as cost_type,
JSON_VALUE(CAST(JOB_DATA AS NVARCHAR(MAX)), '$.cost_time') as cost_time
FROM XIAN_Tools.dbo.T_M_MEALFEE_JOB t
WHERE t.CREATE_TIME > '2025-02-11 00:00:00' AND t.CREATE_TIME < '2025-02-11 23:59:59';

输出：

cost_id	cost_fee	cost_device	cost_user	cost_type	cost_time
717bb31a5c2b4090a39dbdb7ca1125f2	20	30001101124240064	H22080273	中餐	2025-02-11 11:00:05
a85ee6ead6c44a3e90068543d45f8ad6	3	500172340006	H22080273	中餐	2025-02-11 11:00:31
424b05591e1b411883ce2a81b20cb705	13	30001101124360094	H24090022	中餐	2025-02-11 11:02:48
e8d009dc53714e0eb696a92739762b5a	14	500172340006	H24020094	中餐	2025-02-11 11:03:01
894fddf535944c36bfc97c8df43be5fa	12	30001101124240043	H24090022	中餐	2025-02-11 11:04:00
...	...	...	...	...	...

知识扩展:使用 TEXT 和 NVARCHAR(MAX) 的主要区别

特性	TEXT	NVARCHAR(MAX)
最大长度	2GB	2GB
是否支持大量操作	部分支持（需要特殊处理）	完全支持
是否支持索引	不支持	支持（某些情况下）
性能	较低	较高
未来兼容性	已弃用	推荐使用

---