SQL Server - recursively get into nested JSON array using cross apply or openjson or anything

Question

I've been reading as many questions and answers as possible of the same problem but I guess my question would require more creative approach.

So I have a JSON string here:

declare @json nvarchar(max) =
'{
    "propertyObjects": [{
        "propertyID": 1
        , "title": "foo"
        , "class": ""
        , "typeid": 150
        , "value": "bar"
        , "children": [{}]
    }, {
        "propertyID": 2
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{}]
    }, {
        "propertyID": 3
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{
            "propertyID": 4
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{}]
        }, {
            "propertyID": 5
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{}]
        }, {
            "propertyID": 6
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{
                "propertyID": 7
                , "title": "foo"
                , "class": ""
                , "typeid": 128
                , "value": "bar"
                , "children": [{
                    "propertyID": 8
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{}]
                }]
            }]
        }]
    }]
}'

It's crazy at first sight, but think of it this way:
there's an array called propertyObjects which contains multiple objects in parent-child structure.
In each level, only one object can be parent. As you can see object 3 has children inside.

What I want here is to list these objects in a table while we specify a parentID for each of them, so object 4 has a parent with ID 3 and object 3 itself got parent 0 because it's basically on top level.

So far I tried a few approaches like Common Table Expression to make a recursive call but I failed:

;with cte 
as
(
    -- anchor member definition
    select p.propertyID
        , 0 as parentID
        , p.title
        , p.typeid
        , p.[value]
        , p.children
    from openjson(@json, '$.propertyObjects')
    with (
        propertyID int
        , title nvarchar(100)
        , typeid int
        , [value] nvarchar(1000)
        , children nvarchar(max) as JSON
    ) as p

    UNION ALL

    -- recursive member definition
    select 0 as propertyID
        , 0 as parentID
        , '' as title
        , 0 typeid
        , '' as [value]
        , '' as children
    /** child should be bound to parent **/
)
select * from cte

Here's where I failed, I don't know how to make it recursively find objects through children. Plus, I have no idea how to specify parentID of each children!

propertyID    parentID    title    typeid    value    children
----------------------------------------------------------------------------
1             0           foo      150       bar      [{}]
2             0           foo      128       bar      [{}]
3             0           foo      128       bar      [{ "propertyID" : 4 ...
0             0                    0

I also tried using cross apply:

select *
from 
    openjson(@json, '$.propertyObjects')
    with (
        propertyID int
        , title nvarchar(100)
        , typeid int
        , [value] nvarchar(1000)
        , children nvarchar(max) as JSON
    ) as p
cross apply
    openjson(p.children)
    with (
        propertyID int
        , title nvarchar(100)
        , typeid int
        , [value] nvarchar(1000)
        , children nvarchar(max) as JSON
    ) as r

But not a chance, I don't know how deep these children will go in JSON string. besides, results from cross apply will append columns not rows which causes a giant table in result, and in this approach I couldn't even consider specifying parentIDs.

This is totally a failure, any idea on how to get all children in rows?

Desired Table

propertyID    parentID    title    typeid    value
--------------------------------------------------
1             0           foo      150       bar
2             0           foo      128       bar
3             0           foo      128       bar
4             3           foo      128       bar
5             3           foo      128       bar
6             3           foo      128       bar
7             6           foo      128       bar
8             7           foo      128       bar

I'm not sure about this, but are you trying to import data from a JSON file? If so, you might want to explore other options like using PowerShell. I guess you've read this page: learn.microsoft.com/en-us/sql/relational-databases/json/… , but just to be sure, I'm mentioning it here. — ksauter
– ksauter, Commented Jan 26, 2018 at 22:45
ksauter thanks for the reminding, yep I read all the pages already but reading this link reminded me of using memory optimised tables which has nothing to do with my question but it would help me to use the answer in a more optimised way. The answer is going to be a lot more complex than just using built-in functions — Brian Salehi
– Brian Salehi, Commented Jan 27, 2018 at 4:01
Does it have to be a stored procedure? Is using SSIS an option? — ksauter
– ksauter, Commented Jan 27, 2018 at 20:17
SSIS is not an option for me, but stored procedures can be used, anything to solve this problem — Brian Salehi
– Brian Salehi, Commented Jan 27, 2018 at 21:14

roman · Accepted Answer · 2019-04-05 20:57:19Z

10

You was actually pretty close - you just need to use CROSS APPLY together with OPENJSON:

with cte as (
    select
        p.propertyID,
        0 as parentID,
        p.title,
        p.typeid,
        p.[value],
        p.children
    from openjson(@json, '$.propertyObjects') with (
        propertyID int,
        title nvarchar(100),
        typeid int,
        [value] nvarchar(1000),
        children nvarchar(max) as json
    ) as p

    union all

    select
        p.propertyID,
        c.propertyID,
        p.title,
        p.typeid,
        p.[value],
        p.children  
    from cte as c
        cross apply openjson(c.children) with (
            propertyID int,
            title nvarchar(100),
            typeid int,
            [value] nvarchar(1000),
            children nvarchar(max) as json
        ) as p
    where
        c.children <> '[{}]'
)
select
    c.propertyID,
    c.parentID,
    c.title,
    c.typeid,
    c.value
from cte as c

sql fiddle demo

answered Apr 5, 2019 at 20:57

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

Brian Salehi Over a year ago

wow it's been a long time since I used to work with Microsoft SQL server :)) but I checked and it's working! thank you for your brilliant answer.

Brian Salehi · Accepted Answer · 2018-03-06 11:15:07Z

0

As far as I learned about this topic at work, this is almost impossible to dive into unknown nested JSON levels while not having poor performance.

What I did was to avoid nested levels, I created JSON string in one level and simply returned all objects with their key-value pairs inside them, and also changed the data model in database.

Having a valid and reasonable data structure is way more important than having skills to do complex coding.

answered Mar 6, 2018 at 11:15

Brian Salehi

4142 gold badges10 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Server - recursively get into nested JSON array using cross apply or openjson or anything

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related