OpenJson in SQL Server for nested json data?

Question

I have a json data like this:

[
    {"id": 1}, {"id": 3}, {"id": 2, "children": [{"id": 4}, {"id": 5}]} 
]

Please help me how to parse this data into relational data:

Column: Id     Order/Index    ParentId
---------------------------------------
        1      1              0   
        3      2              0
        2      3              0
        4      4              2
        5      5              2

This is not enough information... Is there 1 object children on the first level only or might there be more and deeper nested children too? In other words: Could id=5 have children itself? — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 14, 2018 at 16:43
yes, in this sample, only first level for children and you're right, in my project have more level & nested data — user3836200
– user3836200, Commented Nov 14, 2018 at 17:48

David Browne - Microsoft · Accepted Answer · 2018-11-14 17:15:37Z

1

There are a couple of non-trivial things in this request. First is to order the resulting rows by the document position, which is not visible when you use OPENJSON … WITH to project the columns. And the second one is that you need a hierarchical query (assuming there could be multiple levels).

Anyway, something like this:

declare @doc nvarchar(max) = N'[{"id":1},{"id":3},{"id":2,"children":[{"id":4},{"id":5}]}]';

with q as
(
    select [key] nodePath, 
           cast(json_value(d.[value],'$.id') as int) Id,
           cast(null as int) ParentId,
           cast(json_query(d.[value],'$.children') as nvarchar(max)) children
    from openjson(@doc) d
    union all
    select q.nodePath + '.' + d.[key] nodePath, 
           cast(json_value(d.[value],'$.id') as int) Id,
           q.id ParentId, 
           cast(json_query(d.[value],'$.children') as nvarchar(max)) children
    from q
    outer apply openjson(q.children) d
    where q.children is not null
)
select Id, row_number() over (order by nodePath) [Order/Index], ParentId
from q
order by [Order/Index]

outputs

Id          Order/Index          ParentId
----------- -------------------- -----------
1           1                    NULL
3           2                    NULL
2           3                    NULL
4           4                    2
5           5                    2

(5 rows affected)

answered Nov 14, 2018 at 17:15

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3836200 Over a year ago

Amazing, Thank you for your support!

Collectives™ on Stack Overflow

OpenJson in SQL Server for nested json data?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related