Json input to Sql table

Question

I've a requirement to load the json input into a table which has multiple arrays

declare @json nvarchar(max);

set @json =
'{

            "method":"email",
            "value":"xxx@gmail.com",
            "options":[
            {
                "type":"Regular",
                "alerts":true,
                "subsciptions":true
            },
            {
                "type":"service",
                "alerts":false,
                "subsciptions":false
            }
            ]
            
            
}'

So i wrote the below query to achieve that

SELECT  a.Method,value

            FROM
            OPENJSON(@json)
            WITH
            (
             method varchar(10) N'$.method'
            ,value varchar(100) N'$.value'
            ,options NVARCHAR(MAX) AS JSON
        ) AS a
        CROSS APPLY
        OPENJSON(a.options)
        WITH
        (
            type varchar(100) N'$.type'
            ,alerts INT N'$.alerts'
            ,subscription varchar(10) N'$.subscription'
        ) AS b;

So, I'm getting the output as expected

method, value, type, alerts, subscription

email, xxx@gmail.com, Regular, true, true

email, xxx@gmail.com, service, true, true

Now the requirement has been changed regarding the multiple arrays, so sometimes the options array is available and sometimes they are not available,

I'm trying to write a query which can work for both the logics, so when there is no options array, the output should be like,

method, value, type, alerts, subscription

email, xxx@gmail.com, NULL, NULL, NULL

email, xxx@gmail.com, NULL, NULL, NULL

and when the options array is available, the output should be like,

method, value, type, alerts, subscription

email, xxx@gmail.com, Regular, true, true

email, xxx@gmail.com, service, true, true

Could you please help me write a query to achieve this logic

If you replace `CROSS APPLY` with `OUTER APPLY`, will result be better? — Arvo, Feb 21 '23 at 13:30
Maybe you simply need to replace `CROSS APPLY` with `OUTER APPLY`. — Zhorov, Feb 21 '23 at 13:30
Thanks for taking your time Arvo and Zhorov...It worked for me — vinay kumar, Feb 21 '23 at 13:42

score 0 · Answer 1 · answered Feb 21 '23 at 16:27

Using your demo variable:

SELECT a.[Key], CASE WHEN ISJSON(a.value) = 0 THEN a.value END AS value, b.[Key] AS ArrayKey, c.[Key] AS ArraySubKey, c.Value AS ArraySubValue
 FROM OPENJSON(@json) a
   OUTER APPLY OPENJSON(CASE WHEN ISJSON(a.value) = 1 THEN a.Value END) b
   OUTER APPLY OPENJSON(b.value) c

This produces a table which shows all of the values, with their respective sub keys and values. OUTER APPLY is used because not all values will have sub values. I also used a CASE expression to only attempt to parse the sub values where they are valid JSON.

Key	value	ArrayKey	ArraySubKey	ArraySubValue
method	email	NULL	NULL	NULL
value	xxx@gmail.com	NULL	NULL	NULL
options	NULL	0	type	Regular
options	NULL	0	alerts	true
options	NULL	0	subsciptions	true
options	NULL	1	type	service
options	NULL	1	alerts	false
options	NULL	1	subsciptions	false

Json input to Sql table

1 Answers1

Linked