As a system integration architect, I often deal with generating & processing payloads in SQL server. In a previous post, I showed how to avoid JSON array wrappers when generating payloads and samples from data using T-SQL.
Based on the feedback received from a few colleagues of mine, I will be sharing some tips I use to build various JSON payload samples from test data. For these examples, we will be using the [AdventureWorks2022] sample database.
Nesting/Embedding JSONs
The true value of FOR JSON lies in the fact that it can return complex hierarchical data as nested JSON objects or arrays. In the example below, we have a single object corresponding to one (1) customer but they have multiple child orders – which are represented as a JSON array.
USE [AdventureWorks2022];
GO
SELECT [Customer].[FirstName] AS [Name],
[Customer].[PhoneNumber] AS [Phone],
[Customer].[City] AS [City],
[Orders].[SalesOrderID] AS [OrderId],
[Orders].[OrderDate] AS [OrderDate],
[Orders].[DueDate] AS [ExpectedDeliveryDate]
FROM [Sales].[vIndividualCustomer] AS [Customer]
INNER JOIN [Sales].[SalesOrderHeader] AS [Orders] ON [Customer].[BusinessEntityID] = [Orders].[CustomerID]
WHERE [Customer].[BusinessEntityID] = 12630
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;
GO
{
"Name": "Clayton",
"Phone": "1 (11) 500 555-0145",
"City": "Roncq",
"Orders": [
{
"OrderId": 49271,
"OrderDate": "2013-01-09T00:00:00",
"ExpectedDeliveryDate": "2013-01-21T00:00:00"
},
{
"OrderId": 54524,
"OrderDate": "2013-08-16T00:00:00",
"ExpectedDeliveryDate": "2013-08-28T00:00:00"
},
{
"OrderId": 56797,
"OrderDate": "2013-09-26T00:00:00",
"ExpectedDeliveryDate": "2013-10-08T00:00:00"
}
]
}
Build complex JSON structures
As an extension to the thought process, suppose you want to embed a JSON into another object. Here’s a simple way to do that:
SELECT
JSON_QUERY(
(SELECT '404' AS [HttpStatusCode],
'Page not found' AS [Message]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
) AS [ProcessingStatus],
20 AS [TransactionId]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
GO
{
"ProcessingStatus": {
"HttpStatusCode": "404",
"Message": "Page not found"
},
"TransactionId": 20
}
Similarly, you can even combine 2 JSON messages together:
SELECT
JSON_QUERY(
(SELECT 'HttpTimeout' AS [name],
'60' AS [value]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
) AS [ConfigSettings],
JSON_QUERY(
(SELECT 'SQLTwins' AS [username]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
) AS [UserInfo]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
{
"ConfigSettings": {
"name": "HttpTimeout",
"value": "60"
},
"UserInfo": {
"username": "SQLTwins"
}
}
I trust you will find these tips and samples useful. Do share if you have any tricks that you use when working with JSONs and payloads.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 