如何用OPENJSON解析嵌套JSON并提取多列数据插入SQL表

假设的嵌套JSON变量示例 #

先定义一个典型的嵌套结构@response（实际结构可根据你的真实数据调整）：

DECLARE @response NVARCHAR(MAX) = N'{
    "request": "GET /api/data",
    "type": "query",
    "results": [
        {
            "results_type": "user",
            "results_id": 101
        },
        {
            "results_type": "order",
            "results_id": 202
        }
    ]
}'

数组嵌套场景的解析代码 #

如果你的JSON包含数组型嵌套结果，用以下语句一次性提取所有目标字段：

SELECT 
    JSON_VALUE(@response, '$.request') AS request,
    JSON_VALUE(@response, '$.type') AS type,
    j.results_type,
    j.results_id
FROM OPENJSON(@response, '$.results')
WITH (
    results_type NVARCHAR(50) '$.results_type',
    results_id INT '$.results_id'
) AS j

单层嵌套场景的解析代码 #

如果results_type和results_id是顶层直接嵌套的字段（非数组），直接用OPENJSON配合WITH子句定义所有列：

-- 对应单层结构的@response示例
DECLARE @response NVARCHAR(MAX) = N'{
    "request": "POST /api/create",
    "type": "create",
    "results_type": "user",
    "results_id": 303
}'

-- 解析语句
SELECT *
FROM OPENJSON(@response)
WITH (
    request NVARCHAR(100) '$.request',
    type NVARCHAR(50) '$.type',
    results_type NVARCHAR(50) '$.results_type',
    results_id INT '$.results_id'
)

关键说明 #

• JSON_VALUE用于提取JSON顶层的单个字段，路径格式为$.字段名
• OPENJSON配合WITH子句可以精准映射嵌套字段，路径需对应字段在JSON中的层级
• 字段类型要与JSON中的数据类型匹配，比如results_id是字符串就用NVARCHAR，数字就用INT
• 若存在更深层级的嵌套，可嵌套使用OPENJSON实现多层解析

内容的提问来源于stack exchange，提问作者user254060