CODEWITHSUNDEEP
jsonsql-servert-sqlfor-json
uba2012
uba2012

Reputation: 385

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

Upvotes: 0

Views: 347

Answers (2)

siggemannen
siggemannen

Reputation: 9169

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

Upvotes: 1

abolfazl sadeghi
abolfazl sadeghi

Reputation: 2368

you can add tag type in First Tage

SELECT 
       [cola] as [source.name]
      ,[colb] as [destination.name] 
FROM #Test
FOR JSON PATH, ROOT('type: iwantthistobeincluded,connection')

You can insert Example Data with the following codes

create table #Test(cola varchar(100),colb varchar(100))
insert into #Test
(cola,colb)
select 'val1','val2'
union select 'val3','val4'

Upvotes: 0

Related Questions