I am using below code to generate JSON, but I cannot seem to find a way to add an extra "root" to the JSON. I have tried to add multiple "roots" by using subqueries, but it does not work as it puts the "connection" root under the other.
SELECT
[cola] as [source.name]
,[colb] as [destination.name]
FROM [db].[dbo].[table]
FOR JSON PATH, ROOT('connection')
It returns below JSON:
{
"connection": [
{
"source": {
"name": "val1"
},
"destination": {
"name": "val2"
}
},
{
"source": {
"name": "val3"
},
"destionation": {
"name": "val"
}
}
]
}
But I want to return:
{
"type": "iwantthistobeincluded",
"connection": [
{
"source": {
"name": "val1"
},
"destination": {
"name": "val2"
}
},
{
"source": {
"name": "val3"
},
"destination": {
"name": "val4"
}
}
]
}
Just wrap it in another select?
select "iwantthistobeincluded" AS type
, (
SELECT
[cola] as [source.name]
,[colb] as [destination.name]
FROM [db].[dbo].[table]
FOR JSON PATH
) as connection
for json path