jsonsql-servert-sqlfor-json

Add two JSON roots to JSON in SQL Server


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"
      }
    }
  ]
}

Solution

  • 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