insert json object under parentid (Parent and child data)

Question

Hi,

Json object insert under ID =4

declare @ParentID INT=4;
declare @json varchar(max);
SET @json ='
{
"Name": "Proj-I",
"Type": "Group",
"Children": [
{
"Name": "Expense",
"Type": "Group",
"Children": [
{
"Name": "LabourCost",
"Type": "Item"
},
{
"Name": "OtherExp",
"Type": "Item"
}
]
},
{
"Name": "Income",
"Type": "Group",
"Children": [
{
"Name": "SalesInv",
"Type": "Item"
}
]
}
]
}'

create table #temp (ID INT ,Description VARCHAR(50),Type VARCHAR(20),ParentID INT)
INSERT INTO #temp (ID,Description,Type,ParentID) VALUES (1,'ALL PROJECTS','Group',0)
,(2,'MAIN PROJECTS','Group',1),(3,'MAIN PROJECTS','Item',2),(4,'SUB PROJECTS','Group',1)

------Here insert JSon object under parentID=4

--INSERT INTO #temp
--SELECT @json

SELECT * FROM #temp
DROP TABLE #temp

Answer 1

Try simplifying this statement:

;
with Q as
(
    select cast(0x as varbinary(max)) as pp, cast(0x as varbinary(max)) as p,
        cast(null as VARCHAR(50)) as Description, 
        cast(null as varchar(20)) as Type, 
        json_query(concat('[', @Json, ']')) as Children
    union all
    select Q.p, cast(concat(Q.p, cast(d.[key] as tinyint)) as varbinary(max)), c.*
    from Q 
    outer apply openjson( Q.Children ) d 
    outer apply openjson( d.value ) with 
        (
            Description varchar(50) '$.Name',
            Type varchar(20) '$.Type',
            Children nvarchar(max) '$.Children' as json
        ) c
    where Q.Children is not null
), 
N as
(
    select *, (select max(id) from #Temp) + (row_number() over (order by p)) as id
    from Q where p <> 0x
)
insert #temp (id, Description, Type, ParentID)
select n1.id, n1.Description, n1.Type, isnull(n2.id, @ParentID) as parentId
from N n1
left join N n2 on n2.p = n1.pp

select * from #temp

/*

ID   Description      Type     ParentID
---- ---------------- -------- -----------
1    ALL PROJECTS     Group    0
2    MAIN PROJECTS    Group    1
3    MAIN PROJECTS    Item     2
4    SUB PROJECTS     Group    1
5    Proj-I           Group    4
6    Expense          Group    5
9    Income           Group    5
7    LabourCost       Item     6
8    OtherExp         Item     6
10   SalesInv         Item     9

*/