How can I read below Json column stored in SQL Server using openjson?

Question

Declare @ResponseText nvarchar(4000)

set @responseText ='{
    "submissions": [
        {
        "xml_id":"id_x5d94851726b470.68571510",
        "fields": [ 
            {"fieldvalue":"customerEmail@xyzdomain.com","fieldid":"57282490"},
            {"fieldvalue":"123","fieldid":"57282423"},
            {"fieldvalue":"12345-678900","fieldid":"57282500"}, 
            {"fieldvalue":"Test Message here ","fieldid":"57282564"}
         ]
         }
    ]
 }'

 SELECT  *
 FROM OPENJSON (@ResponseText, '$.submissions') WITH (
    ID NVARCHAR(100) '$.xml_id',
    $.fields.field NVARCHAR(100) ...
 )

etc rest of all the record? I got "NULL" for the rest fields under fields array

Can you add the desired results to your question? – David Browne - Microsoft Oct 02 '19 at 17:24 — David Browne - Microsoft, Oct 02 '19 at 17:24

score 0 · Accepted Answer · answered Oct 03 '19 at 07:54

You can try it like this:

Declare @ResponseText nvarchar(4000)

set @responseText ='{
    "submissions": [
        {
        "xml_id":"id_x5d94851726b470.68571510",
        "fields": [ 
            {"fieldvalue":"customerEmail@xyzdomain.com","fieldid":"57282490"},
            {"fieldvalue":"123","fieldid":"57282423"},
            {"fieldvalue":"12345-678900","fieldid":"57282500"}, 
            {"fieldvalue":"Test Message here ","fieldid":"57282564"}
         ]
         }
    ]
 }'

--The query

 SELECT A.ID
       ,B.*
 FROM OPENJSON (@ResponseText, '$.submissions') 
      WITH (ID NVARCHAR(100) '$.xml_id'
           ,fields NVARCHAR(MAX) AS JSON) A
 OUTER APPLY OPENJSON(a.fields)
             WITH(fieldvalue NVARCHAR(150)
                 ,fieldid BIGINT) B;

The result

ID                          fieldvalue                  fieldid
id_x5d94851726b470.68571510 customerEmail@xyzdomain.com 57282490
id_x5d94851726b470.68571510 123                         57282423
id_x5d94851726b470.68571510 12345-678900                57282500
id_x5d94851726b470.68571510 Test Message here           57282564

The idea in short:

You started correctly using the WITH-clause to read the xml_id. The property fields is nothing else than another element on the same level. But we return it AS JSON. This will allow to add another APPLY OPENJSON(), pass in the fragment we got from $.fields and use another WITH-clause to get the two properties of the objects within the array.

Thank you Shnugo for correct answer , it worked out by you kind support and helpful example and explanations as well. I am greatly appreciated . — Dia Rashid, Oct 04 '19 at 14:21

How can I read below Json column stored in SQL Server using openjson?

1 Answers1