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":"[email protected]","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

Answer 1

You can try it like this:

Declare @ResponseText nvarchar(4000)

set @responseText ='{
    "submissions": [
        {
        "xml_id":"id_x5d94851726b470.68571510",
        "fields": [ 
            {"fieldvalue":"[email protected]","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 [email protected] 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.