Unable to format JSON export with dynamic sections

FreSod
May 3, 2024
151 views
0 votes
2 Answers

I need help to construct a TSQL query (SQL Server 2016) that can produce the following JSON structure.

The structure is as follows.The root is called "Agreement".

Each agreement got the main attributes AgreementDate, Position, OrganizationName. It is then followed by two sections "OrganizationDetails" and "OrganizationDetails2". All this is data in common for all agreements.

It ends with a section that can be different across the agreements. In the first agreement (below example) its called "AgreementType1" and for the second one its called "AgreementOfADifferentType". The last section can have different column setup as the data going into this section are stored in different tables.

Currently all in common data is stored in one single table. It includes data from column name "Agreement" down to "TypeOfOrganization2". The last dynamic section got data stored to different tables.
However these different tables can always be joined to the "in common table".

I would have no problem construction a TSQL query producing this if it wouldnt be for the last dynamic section of the JSON… Note that the dynamic sections can have different amount of columns, different names etc etc…

Any help is appreciated! 🙂

Example JSON

{"Agreement": 
[
    {
        "AgreementDate": "2024-01-01",
        "Position": "123",
        "OrganisationName": "Hello",
        "OrganisationDetails": {
            "BusinessDate": "2024-01-01",
            "TypeOfOrganization": 1
        },
        "OrganisationsDetails2": {
            "BusinessDate2": "2024-01-01",
            "TypeOfOrganization2": null
        },
       ** "AgreementType1":** {
            "DateOfRegistration": null,
            "Amount": 884400.00
        }
    },
    {
        "AgreementDate": "2024-01-01",
        "Position": "222",
        "OrganisationName": "Hello2222",
        "OrganisationDetails": {
            "BusinessDate": "2024-02-02",
            "TypeOfOrganization": 2
        },
        "OrganisationsDetails2": {
            "BusinessDate2": "2024-05-01",
            "TypeOfOrganization2": 5
        },
        **"AgreementOfADifferentType":** {
            "Date": "2024-22-01",
            "Date2": null,
            "AmountOfSales": null,
            "AmountOfSales2": 222.22
            
        }
    }
]

}

I have tried FOR JSON PATH but cant get it to work. I have also tried dynamic SQL trying to pieces together without success.

Answers

Chosen as BEST ANSWER

I have tried different types of writing the SQL. LEFT JOINS. Subqueries. Nothing works :) Above example that I first posted is my desired format. My problem is that the final part of the JSON gets mixed. Unfortunately I have to use "INCLUDE_NULL_VALUES". If I hadnt have to do that then everything would be fine...

{"Agreement": 
[
{
    "AgreementDate": "2024-01-01",
    "Position": "123",
    "OrganisationName": "Hello",
    "OrganisationDetails": {
        "BusinessDate": "2024-01-01",
        "TypeOfOrganization": 1
    },
    "OrganisationsDetails2": {
        "BusinessDate2": "2024-01-01",
        "TypeOfOrganization2": null
    },
    "AgreementType1": {
        "DateOfRegistration": null,
        "Amount": 884400.00
    }
    -- this part shouldnt be here
    "AgreementOfADifferentType": {
        "Date": null,
        "Date2": null,
        "AmountOfSales": null,
        "AmountOfSales2": null
    }
},
{
    "AgreementDate": "2024-01-01",
    "Position": "222",
    "OrganisationName": "Hello2222",
    "OrganisationDetails": {
        "BusinessDate": "2024-02-02",
        "TypeOfOrganization": 2
    },
    "OrganisationsDetails2": {
        "BusinessDate2": "2024-05-01",
        "TypeOfOrganization2": 5
    },
    "AgreementOfADifferentType": {
        "Date": "2024-22-01",
        "Date2": null,
        "AmountOfSales": null,
        "AmountOfSales2": 222.22
    }
 },
 -- this part shouldnt be here
    "AgreementType1": {
        "Date": null,
        "Date2": null,
        "AmountOfSales": null,
        "AmountOfSales2": null
    }
}
]

(Edit)

i typed fast so it might not me exactly as you expected, but the key is PATRH you have to provide to JSON

declare @Agreement Table (ID int not null identity (1,1), AgreementDate Date, Position int, OrganisationName nvarchar(50) )
insert into @Agreement Values (GetDate(), 123, 'Hello'),(GetDate(), 222, 'Hello2222')

declare @OrganisationDetails table (BusinessDate2 date, TypeOfOrganization int)
insert into @OrganisationDetails Values(GetDate(), 1), (GetDate(), 2), (GetDate(), 3)

declare @AgreementOfADifferentType table (ID int not null identity (1,1), [Date] Date, Date2 Date, AmountOfSales decimal null, AmountOfSales2 decimal)
insert into @AgreementOfADifferentType Values(GetDate(), null, null, 884400.00)

select 
      [Aggreement.AgreementDate] = AgreementDate
    , [Aggreement.Position] = Position
    , [Aggreement.OrganisationName] = OrganisationName
    , [Aggreement.OrganisationDetails.BusinessDate2] = OrganisationDetails.BusinessDate2
    , [Aggreement.OrganisationDetails.TypeOfOrganization] = OrganisationDetails.TypeOfOrganization
    , [Aggreement.OrganisationDetails2.BusinessDate2] = OrganisationDetails2.BusinessDate2
    , [Aggreement.OrganisationDetails2.TypeOfOrganization] = OrganisationDetails2.TypeOfOrganization
    , [Aggreement.AgreementOfADifferentType.Date] = AgreementOfADifferentType.Date
    , [Aggreement.AgreementOfADifferentType.Date2] = AgreementOfADifferentType.Date2
    , [Aggreement.AgreementOfADifferentType.AmountOfSales] = AgreementOfADifferentType.AmountOfSales
    , [Aggreement.AgreementOfADifferentType.AmountOfSales2] = AgreementOfADifferentType.AmountOfSales2
from @Agreement Agreement
Left Join @OrganisationDetails OrganisationDetails on OrganisationDetails.BusinessDate2 = Agreement.AgreementDate and OrganisationDetails.TypeOfOrganization = 1
Left Join @OrganisationDetails OrganisationDetails2 on OrganisationDetails2.BusinessDate2 = Agreement.AgreementDate and OrganisationDetails2.TypeOfOrganization = 2
Left Join @AgreementOfADifferentType AgreementOfADifferentType on  AgreementOfADifferentType.[Date] = Agreement.AgreementDate and Agreement.ID = AgreementOfADifferentType.ID
FOR JSON PATH

output would be

[
    {
        "Aggreement": {
            "AgreementDate": "2024-04-30",
            "Position": 123,
            "OrganisationName": "Hello",
            "OrganisationDetails": {
                "BusinessDate2": "2024-04-30",
                "TypeOfOrganization": 1
            },
            "OrganisationDetails2": {
                "BusinessDate2": "2024-04-30",
                "TypeOfOrganization": 2
            },
            <!-- here -->
"AgreementOfADifferentType": {
                "Date": "2024-04-30",
                "AmountOfSales2": 884400
            }
        }
    },
    {
        "Aggreement": {
            "AgreementDate": "2024-04-30",
            "Position": 222,
            "OrganisationName": "Hello2222",
            "OrganisationDetails": {
                "BusinessDate2": "2024-04-30",
                "TypeOfOrganization": 1
            },
            "OrganisationDetails2": {
                "BusinessDate2": "2024-04-30",
                "TypeOfOrganization": 2
            }
        }
    }
]

Please signup or login to give your own answer.

Click here to cancel reply.