SQL Server : I want to access Json Array of object-CodePudding

DECLARE @json varchar(max),
        @errors varchar(max),
        @policy_number varchar(10)

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

I want to get Error_code, error_message1

SELECT
    @policy_number = policy_number,
    @errors = errors
FROM 
    OPENJSON(@json) WITH 
                    (
                        policy_number VARCHAR(10) '$.policy_number', 
                        errors VARCHAR(max) '$.errors'
                    )

CodePudding user response：

If you only want data from the errors property, you can go straight to that with a single OPENJSON call

DECLARE @json varchar(max)

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

SELECT
    policy_number = JSON_VALUE(@json, '$.policy_number'),
    error_code,
    error_message1
FROM 
    OPENJSON(@json, '$.errors')
    WITH (
        error_code VARCHAR(100),
        error_message1 VARCHAR(100)
    );

CodePudding user response：

You're close. You need AS JSON in your OPENJSON and then you can use CROSS APPLY to pull your desired values out of errors.

declare @json varchar(max),@errors varchar(max),@policy_number varchar(10)
set @json = 

    '{
        "returnMessage": "",
        "policy_number": "12345",
        "documents": {
            "policy_document": "",       
            "tax_invoice_document": ""
        },
        "errors": [
            {
                "error_code": "999", 
                "error_message1": "Error"  
            }
        ]
    }';

SELECT error_code, error_message1
FROM OPENJSON(@json)
WITH (errors NVARCHAR(MAX) AS JSON) l1
CROSS APPLY OPENJSON(l1.errors)
WITH (
    error_code VARCHAR(100),
    error_message1 VARCHAR(100)
);