SQL
14 Jun 2022
Returning Complex JSON Object from SQL
SQL
Select 50 as MaxRecords,
1 as Page,
(Select Count(*) from Customers) as TotalCustomers,
Json_Query((
Select Top(10)
Id,
FirstName,
LastName,
Json_Query((
Select Note, DateCreated from NotesTable
Where CustomerId = Id
FOR JSON PATH
)) As Notes,
Json_Query((
Select Top(1) Id as OrderNumber,
Status
From OrdersTable
Where CustomerId = Id
Order By DateCreated desc
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)) As LastOrderStatus
From CustomersTable
Order By Id Desc
FOR JSON PATH
)) as Customers FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
Json_Query Returns the result without escaping json data.
WITHOUT_ARRAY_WRAPPER For single record use this tag to remove it from array.
Output
JSON
{
"MaxRecords": 50,
"Page": 1,
"TotalCustomers": 2000,
"Customers": [
{
"Id": 123,
"FirstName": "John",
"LastName": "Doe",
"Notes": [
{
"Note": "Some note...",
"DateCreated": "2022-06-14T09:00:00.237"
},
{
"Note": "....",
"DateCreated": "2022-05-10T08:02:00.237"
}
],
"LastOrderStatus": {
"OrderNumber": 235,
"Status": "Processing"
}
}
]
}