如何将所有连接表中的所有结果组合为一个JSON输出

Question

我正在加入一些表，使用JSON路径进行输出，但我只想以某种结构化的方式观察所有表的数据。

SELECT 
tableOne.attrA AS 'tableOne.attrA',
tableOne.attrB AS 'tableOne.attrB',
...
tableTwo.attrB AS 'tableTwo.attrB'
...
FROM tableOne
JOIN tableTwo 
  ON tableOne.fId = tableTwo.id
FOR JSON PATH

并获得输出之类的：

{
   "tableOne": {
         "attrA": "asdf"
    }
   "tableOne": {
         "attrB": "fdsa"
    }
}

我想对所有值都有符号，例如：

SELECT 
tableOne.* AS 'tableOne.*',
tableTwo.* AS 'tableTwo.*',
tableThree.* AS 'tableThree.*
....
FOR JSON PATH;

我看到json_array，可以与json_object 结合使用，但它仅在SQL Server 2022中。我有2019版。

Answer 1

因此，我找到了解决方案：

SELECT 
json_query((
        select t1.* 
        for json path,
        without_array_wrapper -- if its only one match
        ,INCLUDE_NULL_VALUES -- if we want to see null values
        )) as tableOne,
json_query((
        select t2.* 
        for json path,
        without_array_wrapper -- if its only one match
        )) as tableTwo
...
FROM tableOne t1
JOIN tableTwo t2
  ON t1.fId = t2.id
FOR JSON PATH, -- needs to be PATH; AUTO returns hierarchy which is just cascading encapsulation
INCLUDE_NULL_VALUES -- just for the sake of seeing all null values

这将在形成的JSON中输出所有数据。我会添加一些链接到MS Docu，但稍后。