Search code examples
jsonsql-servert-sqlopen-json

Parse nested JSON in SQL


I have this JSON:

[{
    "id": 1,
    "meta": [{
            "key": "key1",
            "value": "ValueKey1"
        }, {
            "key": "key2",
            "value": "ValueKey2"
        }
    ]
}, 
{
    "id": 2,
    "meta": [{
            "key": "key2",
            "value": "ValueKey2"
        }
    ]
}, 
{
    "id": 3,
    "meta": [{
            "key": "key1",
            "value": "ValueKey1"
        }
    ]
}]

I would like to get result with all ids for key1, those that doesn't have key1 returned value should be null.

I got result without ids that doesn't have key1 or all combinations with all keys.

The result should look like this:

Id   MetaValue 
---------------
1    ValueKey1
2    NULL 
3    ValueKey1

So far I tried this one with and without where clause:

select Id, MetaValue
from openjson('[{"id":1,"meta":[{"key":"key1","value":"ValueKey1"},{"key":"key2","value":"ValueKey2"}]},{"id":2,"meta":[{"key":"key2","value":"ValueKey2"}]},{"id":3,"meta":[{"key":"key1","value":"ValueKey1"}]}]', '$')
with(
    id int '$.id',
    jMeta nvarchar(max) '$.meta' as JSON
    )
outer apply openjson(jMeta)
with(
    cKey varchar(100) '$.key',
    MetaValue varchar(100) '$.value'
    )
where isnull(cKey,'') in ('','Key1')

Which results are:

Id  MetaValue
-------------
1   ValueKey1
3   ValueKey1

and

Id  MetaValue
-------------
1   ValueKey1
1   ValueKey2
2   ValueKey2
3   ValueKey1

Solution

  • Try using GROUP BY like this

    with cte as (
      select Id, cKey, MetaValue
      from openjson('[{"id":1,"meta":[{"key":"key1","value":"ValueKey1"},{"key":"key2","value":"ValueKey2"}]},{"id":2,"meta":[{"key":"key2","value":"ValueKey2"}]},{"id":3,"meta":[{"key":"key1","value":"ValueKey1"}]}]', '$')
      with (
        id int '$.id',
        jMeta nvarchar(max) '$.meta' as JSON
      )
      outer apply openjson(jMeta)
      with (
        cKey varchar(100) '$.key',
        MetaValue varchar(100) '$.value'
      )
    )
    SELECT id, MAX(CASE WHEN cKey = 'key1' THEN MetaValue END) AS MetaValue
    FROM cte
    GROUP BY id