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

wz3gfoph 于 2023-02-26 发布在 SQL Server

关注(0)|答案(2)|浏览(183)

我正在使用下面的代码生成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"
      }
    }
  ]
}

JSON

来源：https://stackoverflow.com/questions/75525058/add-two-json-roots-to-json-in-sql-server

2条答案

按热度按时间

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

赞(0）回复(0）举报 2023-02-26

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'

赞(0）回复(0）举报 2023-02-26

我来回答

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

2条答案

相关问题

热门标签

最新问答