How to generate nested nth level JSON object in T-SQL?

Question

I have below data

against below query

declare @t table
(
    Id int identity,
    name varchar(50),
    rootid int,
    level int
);


insert into @t(name, rootid, level)
values
('Home', 0, 0)
,('Transaction', 0, 0)
, ('Settings', 0, 0)
,('Purchase Request', 2, 1)
,('Purchase Order', 2, 1)
,('Inventory', 2, 1)
,('Payment Advice', 2, 1)
,('Setup', 3, 1)
,('Budget', 3, 1)
,('CRC', 3, 1)
,('Create PR', 4, 3);

select * from @t;

Desire output:

[{
    "Id": 1,
    "name": "Home",
    "rootid": 0,
    "level": 0
}, {
    "Id": 2,
    "name": "Transaction",
    "rootid": 0,
    "level": 0,
    "children": [{
        "Id": 4,
        "name": "Purchase Request",
        "rootid": 2,
        "level": 1,
        "children": [{
            "Id": 11,
            "name": "Create PR",
            "rootid": 4,
            "level": 3
        }]
    }, {
        "Id": 5,
        "name": "Purchase Order",
        "rootid": 2,
        "level": 1
    }, {
        "Id": 6,
        "name": "Inventory",
        "rootid": 2,
        "level": 1
    }, {
        "Id": 7,
        "name": "Payment Advice",
        "rootid": 2,
        "level": 1
    }]
}, {
    "Id": 3,
    "name": "Settings",
    "rootid": 0,
    "level": 0,
    "children": [{
        "Id": 8,
        "name": "Setup",
        "rootid": 3,
        "level": 1
    }, {
        "Id": 9,
        "name": "Budget",
        "rootid": 3,
        "level": 1
    }, {
        "Id": 10,
        "name": "CRC",
        "rootid": 3,
        "level": 1
    }]
}]

Also Tried @Iptr answer:

;WITH result (id, name, rootId, parent, Level) AS
(
    SELECT  id, 
            name,
            RootId,
            Id as Parent,
            0 as Level
    FROM    @t
    WHERE   RootId= 0
    UNION ALL
    SELECT  t.id, 
            t.Name,
            t.RootId,
            r.Parent,
            r.Level + 1
    FROM @t t
    INNER JOIN result r ON r.id = t.RootId 
)
SELECT  t.*, json_query(nullif(c.children, '[{}]')) as children
FROM @t as t
outer apply (
select 
(
select r.*
from result as r
where r.parent = t.Id
and r.level > 0
order by r.id
for json auto
) as children
) as c
where t.level = 0
order by t.Level
for json auto;

Output

[{
    "Id": 1,
    "name": "Home",
    "rootid": 0,
    "level": 0
}, {
    "Id": 2,
    "name": "Transaction",
    "rootid": 0,
    "level": 0,
    "children": [{
        "id": 4,
        "name": "Purchase Request",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 5,
        "name": "Purchase Order",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 6,
        "name": "Inventory",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 7,
        "name": "Payment Advice",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 11,
        "name": "Create PR",
        "rootId": 4,
        "parent": 2,
        "Level": 2
    }]
}, {
    "Id": 3,
    "name": "Settings",
    "rootid": 0,
    "level": 0,
    "children": [{
        "id": 8,
        "name": "Setup",
        "rootId": 3,
        "parent": 3,
        "Level": 1
    }, {
        "id": 9,
        "name": "Budget",
        "rootId": 3,
        "parent": 3,
        "Level": 1
    }, {
        "id": 10,
        "name": "CRC",
        "rootId": 3,
        "parent": 3,
        "Level": 1
    }]
}]

Above query is not returning nth json child objects, let say if I have nth level of menu items, Parent have multiple Childs and Childs have multiple Childs like treeview.

Tried @Naveen Arora answer:

select ID,name,'' as id,'' as name from Navigations where id not in (select rootid from Navigations) and rootid=0
union
select B.id,B.name,A.id,A.name from Navigations A join Navigations B on A.rootid=B.id
FOR JSON AUTO;

But output

[{
"ID": 1,
"name": "Home",
"id": 0,
"name": ""
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 4,
    "name": "Create PR"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 5,
    "name": "Generate PO"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 6,
    "name": "Create Receipt"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 7,
    "name": "Create Issue Request"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 8,
    "name": "Create Issue Note"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 9,
    "name": "Approve Payment Advice"
}, {
    "ID": 3,
    "name": "Settings",
    "id": 11,
    "name": "Navigation Management"
}, {
    "ID": 11,
    "name": "Navigation Management",
    "id": 12,
    "name": "Navigation & Form Mapping"
}]

Above output it's not include Childs node. Like in Settings I have Navigation Management -> Navigation & Form Mapping

Please read the first couple of paragraphs in the tsql tag info and edit your question accordingly. — Zohar Peled
– Zohar Peled, Commented Mar 13, 2020 at 6:50
It's not the tags that was the problem, it's the sample data... — Zohar Peled
– Zohar Peled, Commented Mar 13, 2020 at 9:36

Naveen Kumar · Accepted Answer · 2020-03-13 09:29:06Z

0

If the sql server version is 2016 or newer than 2016 then you can use FOR JSON PATH.

Assuming that results are stored in test table. This is just to give you an idea how you can do this, may not give you the exact output but you can change it as per your requirement.

SELECT
t.Id AS 'Id',
t.Name AS 'Name',
children = (
    SELECT A.id,A.name from test A join test B on A.rootid=B.id
    FOR JSON PATH
)
FROM Test t
FOR JSON PATH;

And if it is older than 2016 then you may refer this.

edited Mar 13, 2020 at 9:29

answered Mar 13, 2020 at 7:40

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

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

3 Comments

Ahmer Ali Ahsan Over a year ago

I've tried your code to, Kindly check my updated question. Your code didn't get child nodes of parent.

Naveen Kumar Over a year ago

Then you can use FOR JSON PATH for Nested Object. Refer: learn.microsoft.com/en-us/sql/relational-databases/json/…. Also, check my updated answer.

Ahmer Ali Ahsan Over a year ago

Kindly check my updated question title and code what I have tried yet.

lptr · Accepted Answer · 2020-03-13 10:47:25Z

0

declare @t table
(
    Id int identity,
    name varchar(50),
    rootid int,
    level int
);


insert into @t(name, rootid, level)
values
('Home', 0, 0),('Transaction', 0, 0), ('Settings', 0, 0),
('Create PR', 2, 1), ('Generate PO', 2, 1), ('Create Receipt', 2, 1), ('Create Issue Request', 2, 1), ('Create Issue Note', 2, 1), ('Approve Payment Advice', 2, 1),
('Navigation Management', 3, 1), ('Navigation & Form Mapping', 3, 1);

select * from @t;

;WITH result (id, name, rootId, parent, Level) AS
(
    SELECT  id, 
            name,
            RootId,
            Id as Parent,
            0 as Level
    FROM    @t
    WHERE   RootId= 0
    UNION ALL
    SELECT  t.id, 
            t.Name,
            t.RootId,
            r.Parent,
            r.Level + 1
    FROM @t t
    INNER JOIN result r ON r.id = t.RootId 
)
SELECT  t.*, json_query(nullif(c.children, '[{}]')) as children
FROM @t as t
outer apply (
select 
(
select r.*
from result as r
where r.parent = t.Id
and r.level > 0
order by r.id
for json auto
) as children
) as c
where t.level = 0
order by t.Level
for json auto;



SELECT  t.*, json_query(nullif(c.children, '[{}]')) as children
FROM @t as t
outer apply (
select 
(
select r.*
from @t as r
where r.rootid = t.Id
and r.level > 0
order by r.id
for json auto
) as children
) as c
where t.level = 0
order by t.Level
for json auto;

answered Mar 13, 2020 at 10:47

lptr

6,8582 gold badges10 silver badges16 bronze badges

1 Comment

Ahmer Ali Ahsan Over a year ago

Appreciate your kind efforts on my question, I've tried your code and update my question title and description according to your code. Kindly have a look on heading ** Desire Output **.

marc_s · Accepted Answer · 2020-03-28 09:53:31Z

My apology for late posting my answer. But I really appreciate the efforts of @Iptr and @NaveenArora answer on my post. After I do some brain storming on my case I've finally found the way to do it.

Create this function:

create function [dbo].[fnUDFCreateJSON](@currentId int) 
returns varchar(max)
begin 
    declare @json nvarchar(max) 

    IF @currentId <> 0
    BEGIN
        set @json =  
            (
                select [ID], [Name], CSSClass, RouteURL, json_query(dbo.fnUDFCreateJSON([ID])) as SubNavigation 
                from dbo.Navigations   
                where RootId = @currentId 
                for json auto
            );
    END
    ELSE
    BEGIN
        set @json =  
                (
                    select [ID], [Name], CSSClass, RouteURL, '' as SubNavigation from dbo.Navigations where RootId = 0 
                    for json auto
                );
    END
    return @json  
end

and call it by using stored procedure:

CREATE PROCEDURE [dbo].[spGetStartupNavigations]
AS
BEGIN
    SELECT 
        (SELECT 
             ID, Name, CSSClass, RouteURL,  
             JSON_QUERY (dbo.fnUDFCreateJSON(ID)) AS SubNavigation 
         FROM
             dbo.Navigations 
         WHERE
             RootId = 0 
         FOR JSON AUTO) AS Navigation
END

That's it.

Collectives™ on Stack Overflow

How to generate nested nth level JSON object in T-SQL?

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related