SQL script to form the nested JSON as a output

Question

Consider I have below data,

create table #Temp(PropertyID nvarchar(255),BuildingID nvarchar(255),UnitID nvarchar(255),TenantName nvarchar(255),FieldName nvarchar(255),CurrentValue nvarchar(255),PreviousValue nvarchar(255))

insert into #Temp Values
('p1','B1','5','Spencer','Lease_EndDate','01/01/2021','03/01/2021'),
('p1','B1','5','Spencer','MonthlyBaseRent','3232','3000'),
('p1','B1','5','BCR','MonthlyBaseRent','1000','1100'),
('p1','B1','6','EA','MonthlyBaseRent','5000','5100'),
('p1','B2','5','VR','MonthlyBaseRent','3232','3000'),*

I need output as below nested JSON format, but I am getting flat JSON like [{},{},{}]

[
    {
        "PropertyID": "p1",
        "Building": [
            {
                "BuildingID": "B1",
                "Unit": [
                    {
                        "UnitID": "5",
                        "Tenant": [
                            {
                                "TenantName": "Spencer",
                                "Lease_EndDate": {
                                    "CurrentValue": "01/01/2021",
                                    "PreviousValue": "03/01/2021"
                                },
                                "MonthlyBaseRent": {
                                    "CurrentValue": "3232",
                                    "PreviousValue": "3000"
                                }
                            },
                            {
                                "TenantName": "BCR",
                                "MonthlyBaseRent": {
                                    "CurrentValue": "1000",
                                    "PreviousValue": "1100"
                                }
                            }
                        ]
                    },
                    {
                        "UnitID": "6",
                        "Tenant": [
                            {
                                "TenantName": "EA",
                                "MonthlyBaseRent": {
                                    "CurrentValue": "5000",
                                    "PreviousValue": "5100"
                                }
                            }
                        ]
                    }
                ]
            },
            {
                "BuildingID": "B2",
                "Unit": [
                    {
                        "UnitID": "5",
                        "Tenant": [
                            {
                                "TenantName": "VR",
                                "MonthlyBaseRent": {
                                    "CurrentValue": "3232",
                                    "PreviousValue": "3000"
                                }
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

Dale K · Accepted Answer · 2021-02-26 10:34:06Z

2

This is an example of nested JSON, 2 levels. Use this template to build as much levels as needed.

select PropertyID ,BuildingID, 
  (select UnitID ,
     (select t1.TenantName, t1.FieldName, t1.CurrentValue,t1.PreviousValue
     from #Temp t1
     where t1.PropertyID = t2.PropertyID and t1.BuildingID = t2.BuildingID and t1.UnitID = t2.UnitID
     for json path) Tenant
  from  #Temp t2
  where t2.PropertyID = t3.PropertyID and t2.BuildingID = t3.BuildingID
  group by UnitID, PropertyID ,BuildingID 
  for json path) Unit
from  #Temp t3
group by PropertyID ,BuildingID 
for json path

edited Feb 26, 2021 at 10:34

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Feb 26, 2021 at 8:43

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

2 Comments

pts Over a year ago

Suppose, i am inserting null values as below, The above script skip these values. ('p1','B1',null,null,'TotalRent','5000','5100'),('p1',null,null,null,'BuildingID','B3',null) It should come as, [ { "PropertyID": "p1", "Building": [ { "Building_Id": "B1", "TotalRent": { "CurrentValue": "5000", "PreviousValue": "5000" } }, { "Building_Id": { "CurrentValue": "B3", "PreviousValue": null } } ] } ]

Serg Over a year ago

@pts, use learn.microsoft.com/en-us/sql/relational-databases/json/…

Collectives™ on Stack Overflow

SQL script to form the nested JSON as a output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related