如何在SQL Server中提取嵌套于JSON字段内的JSON结构？

要处理你提供的这种嵌套JSON（document字段是独立的JSON字符串），核心思路是先从外层JSON提取出嵌套的JSON字符串，再将其传入内层OPENJSON进行解析，结合CROSS APPLY可以实现多层级的关联解析。以下是具体实现方案：

关键步骤说明 #

• 外层OPENJSON：提取外层JSON的所有字段，重点是获取document字段的JSON字符串内容。
• 内层OPENJSON：将外层提取到的document字符串作为输入，解析其中的对象和字段。
• 数组解析：如果内层JSON包含数组（比如ruleCodeList），需要在WITH子句中用AS JSON标记数组字段，再通过额外的CROSS APPLY和OPENJSON解析数组元素。

完整代码示例 #

1. 使用变量存储JSON的场景

-- 声明外层JSON变量（注意修正了id的前导0，JSON数字不能以0开头）
DECLARE @json NVARCHAR(MAX) = N'{
    "key": {
        "type": "SOURCE_DOCUMENT",
        "label": "2021-03-04T16:31:30.424950"
    },
    "document": "{\"id\":8154711,\"decisionFinding\":{\"decision\":\"OK\",\"actionCode\":\"ACCEPT\",\"ruleCodeList\":[{\"ruleCode\":\"ACCEPT\",\"priority\":0,\"classification\":null,\"description\":\"comment here\"}],\"agentId\":null,\"isTest\":false,\"authentication\":false,\"additionalInfo\":[],\"automatedDecision\":\"ACCEPTED\",\"manualDecision\":null}}",
    "httpStatus": 200
}'

SELECT 
    -- 外层JSON字段
    outerJson.key_type,
    outerJson.key_label,
    outerJson.httpStatus,
    -- 内层document的核心字段
    innerJson.id,
    innerJson.decision,
    innerJson.actionCode,
    innerJson.agentId,
    innerJson.isTest,
    innerJson.automatedDecision,
    -- 内层数组ruleCodeList的字段
    ruleCodes.ruleCode,
    ruleCodes.priority,
    ruleCodes.description
FROM 
    OPENJSON(@json)
    WITH (
        key_type NVARCHAR(100) '$.key.type',
        key_label DATETIME2 '$.key.label',
        document NVARCHAR(MAX) '$.document', -- 提取嵌套的JSON字符串
        httpStatus INT '$.httpStatus'
    ) AS outerJson
-- 关联内层JSON解析
CROSS APPLY 
    OPENJSON(outerJson.document)
    WITH (
        id BIGINT '$.id',
        decision NVARCHAR(10) '$.decisionFinding.decision', -- 直接通过路径提取嵌套对象字段
        actionCode NVARCHAR(20) '$.decisionFinding.actionCode',
        agentId NVARCHAR(50) '$.decisionFinding.agentId',
        isTest BIT '$.decisionFinding.isTest',
        automatedDecision NVARCHAR(20) '$.decisionFinding.automatedDecision',
        ruleCodeList NVARCHAR(MAX) '$.decisionFinding.ruleCodeList' AS JSON -- 标记为JSON类型，用于后续数组解析
    ) AS innerJson
-- 关联数组解析
CROSS APPLY 
    OPENJSON(innerJson.ruleCodeList)
    WITH (
        ruleCode NVARCHAR(20) '$.ruleCode',
        priority INT '$.priority',
        description NVARCHAR(100) '$.description'
    ) AS ruleCodes

2. JSON存储在表列中的场景

如果JSON数据存储在表（比如JsonDataTable）的JsonContent列中，只需替换变量为表列即可：

SELECT 
    outerJson.key_type,
    outerJson.key_label,
    outerJson.httpStatus,
    innerJson.id,
    innerJson.decision,
    ruleCodes.ruleCode,
    ruleCodes.description
FROM 
    JsonDataTable
CROSS APPLY 
    OPENJSON(JsonDataTable.JsonContent)
    WITH (
        key_type NVARCHAR(100) '$.key.type',
        key_label DATETIME2 '$.key.label',
        document NVARCHAR(MAX) '$.document',
        httpStatus INT '$.httpStatus'
    ) AS outerJson
CROSS APPLY 
    OPENJSON(outerJson.document)
    WITH (
        id BIGINT '$.id',
        decision NVARCHAR(10) '$.decisionFinding.decision',
        ruleCodeList NVARCHAR(MAX) '$.decisionFinding.ruleCodeList' AS JSON
    ) AS innerJson
CROSS APPLY 
    OPENJSON(innerJson.ruleCodeList)
    WITH (
        ruleCode NVARCHAR(20) '$.ruleCode',
        description NVARCHAR(100) '$.description'
    ) AS ruleCodes

注意事项 #

• 如果document字段的JSON存在格式问题（比如你示例中id的前导0），SQL Server会解析失败，需要先修正JSON格式，或者将id按字符串类型解析（将id BIGINT改为id NVARCHAR(50)）。
• 若嵌套JSON可能为NULL，可以使用OUTER APPLY替代CROSS APPLY，避免过滤掉外层存在但内层为空的记录。

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