SQL Server : I want to access Json Array of object

Question

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'
                    )

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Oct 11, 2021 at 15:38
If you want to store raw json in the DB, that's fine, but any field within the json you may need to use with a query you should also provide it's own column in the schema and extract at the time of insert or update, usually in the client code. MUCH better to do the extraction once at record save, rather than every time the table is queried, plus then you can index the field. — Joel Coehoorn
– Joel Coehoorn, Commented Oct 11, 2021 at 16:00

Charlieface · Accepted Answer · 2021-10-11 15:54:57Z

1

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)
    );

answered Oct 11, 2021 at 15:54

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

squillman Over a year ago

@Huzefa This is actually a better approach than mine if if you just want the error_code and error_message values. If you need any of the higher up property values then you would want to start getting into multiple OPENJSON calls as my approach used.

squillman · Accepted Answer · 2021-10-11 15:41:35Z

0

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)
);

answered Oct 11, 2021 at 15:41

squillman

13.7k3 gold badges45 silver badges65 bronze badges

1 Comment

Huzefa Over a year ago

Is work fine if ERROr have multple data so how can in variable put in variable and set @json = '{ "returnMessage": "", "policy_number": "12345", "documents": { "policy_document": "", "tax_invoice_document": "" }, "errors": [ { "error_code": "999", "error_message1": "Error123" }, { "error_code": "8888", "error_message1": "Error1234" }, { "error_code": "7777", "error_message1": "Error1234" } ] }'

Collectives™ on Stack Overflow

SQL Server : I want to access Json Array of object

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related