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;

输出：

如果返回 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;

输出：

3. 解析 JSON 数组

JSON_QUERY()

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

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

输出：

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;

输出：

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'
);

输出：

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'));

输出：

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;

输出：

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;

输出：

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';

输出：

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