I have a hierarchical table category as below where root_id is the parent id.
Means here root_id is the primary key of the table (which is ID here).
category1, category2, category2_ge belongs to questionnare1.
subcategory1, subcategory_ge1, subcategory2, subcategory_ge2 belongs to category1
subcategory3 belongs to category2, subcategory4 belongs to category2_ge
ID name root_id level_id description language_id
-----------------------------------------------------------------------
1 questionnaire 0 1 desc1 1
2 category1 1 2 desc1 1
3 subcategory1 2 3 desc1 1
4 subcategory_ge1 2 3 desc1 2
5 subcategory2 2 3 desc2 1
6 subcategory_ge2 2 3 desc2 2
7 category2 1 2 desc2 1
8 category2_ge 1 2 desc2 2
9 subcategory3 7 3 desc3 1
10 subcategory4 8 3 desc4 1
The Create and Insert queries are below, and I want to get the data like below using FOR JSON
CREATE TABLE categories
(
ID int,
name varchar(255),
root_id int,
level_id int,
description nvarchar(max),
language_id int
);
INSERT INTO categories (ID, name, root_id, level_id, description, language_id)
VALUES (1, 'questionnaire', 0, 1, 'desc1', 1);
VALUES (2, 'category1', 1, 2, 'desc1', 1);
VALUES (3, 'subcategory1', 2, 3, 'desc1', 1);
VALUES (4, 'subcategory_ge1', 2, 3, 'desc1', 2);
VALUES (5, 'subcategory2', 2, 3, 'desc2', 1);
VALUES (6, 'subcategory_ge2', 2, 3, 'desc2', 2);
VALUES (7, 'category2', 1, 2, 'desc2', 1);
VALUES (8, 'category2_ge', 1, 2, 'desc2', 2);
VALUES (9, 'subcategory3', 7, 3, 'desc3', 1);
VALUES (10, 'subcategory4', 8, 3, 'desc4', 1);
The desired data is below
ID name root_id level_id questionnare_data
-------------------------------------------------------------------
1 questionnaire 1 0 {"questionnaire":{"ID":1,"name":"questionnaire", "level_id":1},"categories":{{"ID:2,"name":"category1","level_id":2,"subcategories":{{"ID":3,"name":"subcategory1","level_id":3},{"ID":4,"name":"subcategory_ge1","level_id":3},{"ID":5,"name":"subcategory2","level_id":3},{"ID":6,"name":"subcategory_ge2","level_id":3}}},{"ID":7",name":"category2","level_id":2,"subcategories":{{"ID":9,"name":"subcategory3","level_id":3}}},{"ID":8",name":"category2_ge","level_id":2,"subcategories":{{"ID":10,"name":"subcategory4","level_id":3}}}}}
To simplify the JSON that I require, I have simplified like below
{"questionnaire":{"ID":1,"name":"questionnaire", "level_id":1},
"categories":{
{"ID:2,"name":"category1","level_id":2,
"subcategories":{
{"ID":3,"name":"subcategory1","level_id":3},
{"ID":4,"name":"subcategory_ge1","level_id":3},
{"ID":5,"name":"subcategory2","level_id":3},
{"ID":6,"name":"subcategory_ge2","level_id":3}
}
},
{"ID":7",name":"category2","level_id":2,
"subcategories":{
{"ID":9,"name":"subcategory3","level_id":3}
}
},
{"ID":8",name":"category2_ge","level_id":2,
"subcategories":{
{"ID":10,"name":"subcategory4","level_id":3}
}
}
}
}
How do I achieve this?
My SQL version is Microsoft SQL Server 2016
I tried to use the solution which I found in Select all hierarchy level and below SQL Server, but that is a different scenario
