Microsoft SQL Server FOR JSON Creating custom nested JSON structure
Example
If you need some complex JSON structure that cannot be created using FOR JSON PATH or FOR JSON AUTO, you can customize your JSON output by putting FOR JSON sub-queries as column expressions:
SELECT top 5 o.object_id, o.name,
(SELECT column_id, c.name
FROM sys.columns c WHERE o.object_id = c.object_id
FOR JSON PATH) as columns,
(SELECT parameter_id, name
FROM sys.parameters p WHERE o.object_id = p.object_id
FOR JSON PATH) as parameters
FROM sys.objects o
FOR JSON PATH
Each sub-query will produce JSON result that will be included in the main JSON content.
