Extract text from JSON in SQL Server-CodePudding

I am trying to extract a specific value from a JSON column in SQL Server. Unfortunately I have read several posts on this topic but still cannot figure out how to translate their solutions to what I need. I am looking to extract "foo testing" but simply do not understand how to get at this with a nested JSON. Can someone please advise?

The structure of the JSON column is:

{
    "values": [
        {
            "id": "x01",
            "status": "STATUS1",
            "subStatus": "SubStatus1",
            "values": [
                {
                    "key": "dropdown",
                    "value": "",
                    "optionType": null
                }
            ]
        },
        ...
        {
            "id": "x03",
            "status": "STATUS3",
            "subStatus": "SubStatus3",
            "values": [
                {
                    "key": "dropdown",
                    "value": "",
                    "optionType": null
                },
                {
                    "key": "textInput",
                    "value": null,
                    "optionType": null
                },
                {
                    "key": "checkbox1",
                    "value": true,
                    "optionType": null
                },
                {
                    "key": "textInput2",
                    "value": "foo testing",
                    "optionType": null
                }
            ]
        }
    ]
}

CodePudding user response：

The statement depends on the structure of the parsed JSON, in your case you need to use two nested OPENJSON() calls and additinal APPLY operators. Note, that you need to use AS JSON in a "values" column definition to specify that the referenced property contains an inner JSON array and the type of that column must be nvarchar(max).

Test table:

DECLARE @json varchar(max) = '
{
    "values": [
        {
            "id": "x01",
            "status": "STATUS1",
            "subStatus": "SubStatus1",
            "values": [
                {"key": "dropdown", "value": "", "optionType": null}
            ]
        },
        {
            "id": "x03",
            "status": "STATUS3",
            "subStatus": "SubStatus3",
            "values": [
                {"key": "dropdown", "value": "", "optionType": null},
                {"key": "textInput", "value": null, "optionType": null},
                {"key": "checkbox1", "value": true, "optionType": null},
                {"key": "textInput2", "value": "foo testing", "optionType": null}
            ]
        }
    ]
}
'
SELECT JsonColumn
INTO JsonTable
FROM (VALUES (@json)) v (JsonColumn)

Statement:

SELECT j1.[id], j2.[key], j2.[value] -- or add all columns
FROM JsonTable t
CROSS APPLY OPENJSON(t.JsonColumn, '$.values') WITH (
   [id] varchar(3) '$.id',
   [status] varchar(30) '$.status',
   [subStatus] varchar(30) '$.subStatus',
   [values] nvarchar(max) '$.values' AS JSON
) j1
CROSS APPLY OPENJSON(j1.[values], '$') WITH (
   [key] varchar(50) '$.key',
   [value] varchar(50) '$.value',
   [optionType] varchar(50) '$.optionType'
) j2

Result:

id  key        value
---------------------------
x01 dropdown   
x03 dropdown   
x03 textInput  
x03 checkbox1  true
x03 textInput2 foo testing

CodePudding user response：

you can use following query

;with summery as(
   SELECT * 
   FROM OPENJSON((SELECT value FROM OPENJSON(@json)))
   WITH (
    id NVARCHAR(50) 'strict $.id',
    status NVARCHAR(50) '$.status',
    subStatus NVARCHAR(50) '$.subStatus',
    [values] NVARCHAR(max) '$.values' AS JSON
  )
) 
select id,status,subStatus,[key],value,optionType from summery 
CROSS APPLY OPENJSON(summery.[values])
   WITH (
      [key] NVARCHAR(50) '$.key',
      [value] NVARCHAR(50) '$.value',
      [optionType] NVARCHAR(50) '$.optionType'
);

demo in db<>fiddle