向SQL Server中的JSON添加两个JSON根

wz3gfoph  于 2023-02-26  发布在  SQL Server
关注(0)|答案(2)|浏览(182)

我正在使用下面的代码生成JSON,但我似乎找不到一种方法来添加一个额外的“根”到JSON。我曾尝试通过使用子查询添加多个“根”,但它不工作,因为它把“连接”根放在另一个下面。

SELECT 
       [cola] as [source.name]
      ,[colb] as [destination.name] 
FROM [db].[dbo].[table]
FOR JSON PATH, ROOT('connection')

它返回以下JSON:

{
  "connection": [
    {
      "source": {
        "name": "val1"
      },
      "destination": {
        "name": "val2"
      }
    },
    {
      "source": {
        "name": "val3"
      },
      "destionation": {
        "name": "val"
      }
    }
  ]
}

但我想返回:

{
  "type": "iwantthistobeincluded",
  "connection": [
    {
      "source": {
        "name": "val1"
      },
      "destination": {
        "name": "val2"
      }
    },
    {
      "source": {
        "name": "val3"
      },
      "destination": {
        "name": "val4"
      }
    }
  ]
}
zwghvu4y

zwghvu4y1#

把它包在另一个选择里?

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
axkjgtzd

axkjgtzd2#

可以在第一个标记中添加标记类型

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

可以使用以下代码插入示例数据

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

相关问题