Parse nested JSON in SQL

Question

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

Answer 1

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

Answer 2

Instead of the WHERE clause, we can apply LEFT JOIN to a dataset containing the key you are looking for :

with raw_data 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'
  )
),
cte as (
  select c.id, s.cKey as cKey, c.MetaValue, max(s.cKey) over (partition by id) as max_cKey
  from raw_data c
  left join (select 'key1' as cKey) as s on s.cKey = c.cKey
)
select id, case when cKey = max_cKey then MetaValue else null end as MetaValue
from cte
where cKey = max_cKey or (cKey is null and max_cKey is null );

Results :

id  MetaValue
1   ValueKey1
2   null
3   ValueKey1

Demo here