SQL Server如何从JSON对象数组中提取error_code与error_message1字段

问题核心说明 #

你当前的代码是将整个errors JSON数组直接作为字符串赋值给@errors变量，没有解析数组内部的字段，需要调整解析逻辑，以下是两种常用适配方案：

方案1：适配errors数组最多只有1个错误对象的场景

直接在WITH子句中通过下标访问数组第一个元素的字段，新增两个变量存储对应错误值即可：

DECLARE @json varchar(max),
        @policy_number varchar(10),
        @error_code varchar(10), -- 新增存储错误码的变量
        @error_message1 varchar(200) -- 新增存储错误信息的变量

SET @json = 
    '{
        "returnMessage": "",
        "policy_number": "12345",
        "documents": {
            "policy_document": "",       
            "tax_invoice_document": ""
        },
        "errors": [
            {
                "error_code": "999", 
                "error_message1": "Error"  
            }
        ]
    }'

SELECT
    @policy_number = policy_number,
    @error_code = error_code,
    @error_message1 = error_message1
FROM 
    OPENJSON(@json) WITH 
                    (
                        policy_number VARCHAR(10) '$.policy_number', 
                        error_code VARCHAR(10) '$.errors[0].error_code', -- 直接取数组第一个元素的error_code
                        error_message1 VARCHAR(200) '$.errors[0].error_message1' -- 直接取数组第一个元素的error_message1
                    )

方案2：适配errors数组可能有多个错误对象的场景

如果数组存在多个错误，需要遍历所有元素，可以通过CROSS APPLY关联第二次OPENJSON解析errors数组：

DECLARE @json varchar(max),
        @policy_number varchar(10),
        @errors varchar(max) -- 用来拼接所有错误信息

SET @json = 
    '{
        "returnMessage": "",
        "policy_number": "12345",
        "documents": {
            "policy_document": "",       
            "tax_invoice_document": ""
        },
        "errors": [
            {
                "error_code": "999", 
                "error_message1": "Error"  
            },
            {
                "error_code": "001", 
                "error_message1": "参数错误"  
            }
        ]
    }'

-- SQL Server 2017及以上版本可以用STRING_AGG直接拼接所有错误
SELECT
    @policy_number = policy_number,
    @errors = STRING_AGG(CONCAT('错误码:', err.error_code, ' 错误信息:', err.error_message1), '; ')
FROM 
    OPENJSON(@json) WITH 
                    (
                        policy_number VARCHAR(10) '$.policy_number', 
                        errors NVARCHAR(MAX) '$.errors' AS JSON -- 标记errors为JSON类型，供后续OPENJSON解析
                    )
CROSS APPLY OPENJSON(errors)
WITH (
    error_code VARCHAR(10) '$.error_code',
    error_message1 VARCHAR(200) '$.error_message1'
) err

如果使用SQL Server 2016版本没有STRING_AGG函数，可以替换为FOR XML PATH的方式实现多错误拼接。

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