SQL Server解析数组内嵌套JSON对象遇阻，求解决方案

嘿，我懂你现在的困扰——嵌套JSON数组的解析确实容易卡壳，尤其是像你这种外层requests数组里还套着custom_fields数组的结构。我看了你给出的JSON示例和部分代码，这就给你梳理出可行的解决方案。

首先先明确你的原始数据和待完善的代码：

DECLARE @json VARCHAR(MAX) = '{ 
    "requests": [ 
        { 
            "ownertype": "admin", 
            "ownerid": "111", 
            "custom_fields": [ 
                { "orderid": "1" }, 
                { "requestorid": "5000" }, 
                { "LOE": "week" } 
            ] 
        }, 
        { 
            "ownertype": "user", 
            "ownerid": "222", 
            "custom_fields": [ 
                { "orderid": "5" }, 
                { "requestorid": "6000" }, 
                { "LOE": "month" } 
            ] 
        } 
    ] 
}'

-- 你之前写的部分查询
-- select request...

方案1：将嵌套数组展平为多行 #

如果你希望把每个custom_fields里的键值对都单独成行展示，可以用OPENJSON配合CROSS APPLY逐层解析：

DECLARE @json VARCHAR(MAX) = '{ 
    "requests": [ 
        { 
            "ownertype": "admin", 
            "ownerid": "111", 
            "custom_fields": [ 
                { "orderid": "1" }, 
                { "requestorid": "5000" }, 
                { "LOE": "week" } 
            ] 
        }, 
        { 
            "ownertype": "user", 
            "ownerid": "222", 
            "custom_fields": [ 
                { "orderid": "5" }, 
                { "requestorid": "6000" }, 
                { "LOE": "month" } 
            ] 
        } 
    ] 
}'

SELECT 
    req.ownertype,
    req.ownerid,
    JSON_VALUE(cf.value, '$.orderid') AS orderid,
    JSON_VALUE(cf.value, '$.requestorid') AS requestorid,
    JSON_VALUE(cf.value, '$.LOE') AS LOE
FROM 
    OPENJSON(@json, '$.requests')
    WITH (
        ownertype VARCHAR(50) '$.ownertype',
        ownerid VARCHAR(50) '$.ownerid',
        custom_fields NVARCHAR(MAX) '$.custom_fields' AS JSON -- 标记为JSON类型，方便后续解析
    ) AS req
CROSS APPLY 
    OPENJSON(req.custom_fields) AS cf

代码说明： #

• 先用OPENJSON解析外层的requests数组，通过WITH子句提取外层的ownertype和ownerid，同时把custom_fields标记为JSON类型（AS JSON），避免被当成普通字符串。
• 再用CROSS APPLY把每个request对应的custom_fields数组展开成单独的行。
• 最后用JSON_VALUE从每个展开的custom_fields元素中提取对应字段，提取不到的字段会返回NULL。

方案2：将嵌套字段转为同行列（每行对应一个request） #

如果你希望每个request作为一行，把custom_fields里的所有字段都放在同一行展示，可以结合CASE和聚合函数实现：

DECLARE @json VARCHAR(MAX) = '{ 
    "requests": [ 
        { 
            "ownertype": "admin", 
            "ownerid": "111", 
            "custom_fields": [ 
                { "orderid": "1" }, 
                { "requestorid": "5000" }, 
                { "LOE": "week" } 
            ] 
        }, 
        { 
            "ownertype": "user", 
            "ownerid": "222", 
            "custom_fields": [ 
                { "orderid": "5" }, 
                { "requestorid": "6000" }, 
                { "LOE": "month" } 
            ] 
        } 
    ] 
}'

SELECT 
    ownertype,
    ownerid,
    MAX(CASE WHEN cf.field_key = 'orderid' THEN cf.field_value END) AS orderid,
    MAX(CASE WHEN cf.field_key = 'requestorid' THEN cf.field_value END) AS requestorid,
    MAX(CASE WHEN cf.field_key = 'LOE' THEN cf.field_value END) AS LOE
FROM 
    OPENJSON(@json, '$.requests')
    WITH (
        ownertype VARCHAR(50) '$.ownertype',
        ownerid VARCHAR(50) '$.ownerid',
        custom_fields NVARCHAR(MAX) '$.custom_fields' AS JSON
    ) AS req
CROSS APPLY (
    SELECT 
        [key] AS field_key,
        [value] AS field_value
    FROM OPENJSON(req.custom_fields)
    WITH (
        [key] VARCHAR(50) '$',
        [value] VARCHAR(50) '$'
    )
) AS cf
GROUP BY 
    ownertype, ownerid

代码说明： #

• 内层的OPENJSON直接提取custom_fields里每个键值对的key和value。
• 用CASE语句根据field_key匹配对应的字段，再通过MAX聚合把同一request的多个行合并成一行，确保每个字段只保留对应的值。

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