1

The table containing the key-value also has a ParentID column. This column contains the id of its parent. I want to get a nested json using these.

Key-value table:

create table #temp
(
    [Id] int, 
    [Key] nvarchar(100),
    [Value] nvarchar(max),
    [ParentId] int
)


insert into #temp select 1,'help','',null
insert into #temp select 2,'info','',1
insert into #temp select 3,'contact','example',1
insert into #temp select 4,'SSS','',1
insert into #temp select 5,'title','example',2
insert into #temp select 6,'text','example',2
insert into #temp select 7,'title','example',4
insert into #temp select 8,'text','',4
insert into #temp select 9,'0','',8
insert into #temp select 10,'1','',8
insert into #temp select 11,'title','example',9
insert into #temp select 12,'text','example',9
insert into #temp select 13,'title','example',10
insert into #temp select 14,'text','example',10

Desired output:

{
  "help": {
    "info": {
      "title": "example",
      "text": "example"
    },
    "contact": "example",
    "SSS": {
      "title": "example",
      "text": [
        {
          "title": "example",
          "text": "example"
        },
        {
          "title": "example",
          "text": "example"
        }
      ]
    }
  }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

0 Answers0