Question 296936 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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
Tags:

2

Answers


  1. 0

    You need to include the surrounding { } braces around the JSON. Also that array of items needs to belong to an array property:

    {
    "items": [
        {
            "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"
                }
            ]
        }
    ]
}
    Login or Signup to reply.
  2. 0

    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 )

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search