SQL to JSON parent/child relationship

Question

I have a table in my Microsoft SQL Server 2017 that looks like this:

+----+-------+----------+-------+-----------+
| ID | Level | ParentID | IsEnd | SomeText  |
+----+-------+----------+-------+-----------+
|  1 |     1 |    null  |     1 |   abc     |
|  2 |     1 |    null  |     1 |   asd     |
|  3 |     2 |    1     |     1 |   weqweq  |
|  4 |     2 |    1     |     0 |   lkjlkje |
|  5 |     3 |    4     |     1 |   noonwqe |
|  6 |     3 |    4     |     0 |   wet4t4  |
+----+-------+----------+-------+-----------+

And I would like to output a json string:

[{  ID: 1,
SomeText: 'abc',
Child2: [{
        ID: 3,
        SomeText: 'weqweq'
    }, {
        ID: 4,
        SomeText: 'lkjlkje',
        Child3: [{
                ID: 5,
                SomeText: 'noonwqe'
            }, {
                ID: 6,
                SomeText: 'wet4t4'
            }
        ]}
        ] 
}]

IsEnd is a flag to know where you reached the last level.

Answer 1

You can use a recursive scalar UDF (User Defined Function) that builds the hierarchy starting from the root.

Here is the stub of an UDF you can start from:

create function dbo.udf_create_json_tree(@currentId int) 
    returns varchar(max)
begin 
    declare @json nvarchar(max) 
    declare @id int, @parentId int, @someText varchar(50)

    select @id =[ID], @parentId = ParentID, @someText = SomeText
    from dbo.tmp 
    where [ID] = @currentId

    set @json =  
        (
            select [ID], SomeText, json_query(dbo.udf_create_json_tree([ID])) as Child 
            from dbo.tmp   
            where ParentID = @currentId 
            for json auto
        );

    if(@parentId is null) 
       set @json = concat(
                          '[{"ID":' + cast (@id as nvarchar(50)) ,
                          ',"SomeText":"' , @someText , 
                          '","Child":' , cast(@json as nvarchar(max)) ,
                          '}]'
                          )
    return @json  
end 

Populate a table with your input values:

create table tmp ([ID] int, [Level] int, ParentID int, IsEnd bit, SomeText varchar(50))
insert into tmp values
 (1, 1, null,1, 'abc'    )
,(2, 1, null,1, 'asd'    )
,(3, 2, 1   ,1, 'weqweq' )
,(4, 2, 1   ,0, 'lkjlkje')
,(5, 3, 4   ,1, 'noonwqe')
,(6, 3, 4   ,0, 'wet4t4' )

Now you can call the UDF on the first node (with ID=1):

select dbo.udf_create_json_tree(1)

Json result:

Formatted json result:

[{
    "ID": 1,
    "SomeText": "abc",
    "Child": [{
        "ID": 3,
        "SomeText": "weqweq"
    },
    {
        "ID": 4,
        "SomeText": "lkjlkje",
        "Child": [{
            "ID": 5,
            "SomeText": "noonwqe"
        },
        {
            "ID": 6,
            "SomeText": "wet4t4"
        }]
    }]
}]

If you really need to name each child node with the level number (Child2, Childx and so on) you'll probably want to implement a replace logic on "Child" string.