在SQL Server中将行/表记录转换为JSON文档

Question

以下是输入的示例数据。

if object_id('tempdb.dbo.#store_data') is not null
drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select * from #store_data

以下是所需结果。

[{
"sid"="1",
"bid"="3",
"time"="2019-01-01"
"que"="apple"},
{"sid"="2",
"bid"="5",
"hrs"="6",
"dat"="pine"
}]

以下是我尝试过的查询：

select [key],[value] from #store_data for json path

没有达到预期结果。

- Smart003

---

你应该意识到一个事实，即没有隐含的排序顺序！如果没有最外层的ORDER BY，它可以运行一百次，通过所有内部测试，但在生产中会返回垃圾。如果bid=3出现在第一个或第二个JSON对象中，则什么都不是随机的。 - Shnugo

我已经更新了我的答案@Shnugo...由PSK提供...我使用了循环来获取结果集...但我感觉还有另一种方法可以以相同的顺序检索。 - Smart003

有循环或无循环：SQL-Server 的排序方式并不是按照插入数据的方式进行的。如果没有外层的 ORDER BY，任何 SELECT 都可能以任意随机排序返回。如果想要保持排序顺序，最简单的方法是添加一个 IDENTITY 列。 - Shnugo

Answer 1

请注意，您无法通过直接查询获取，您必须首先在storeid上进行PIVOT操作。

您可以尝试以下查询。

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

完整示例

 if object_id('tempdb.dbo.#store_data') is not null
 drop table #store_data

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100))

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
for json auto

输出：

[
  {
    "sid": "1",
    "bid": "3",
    "time": "2019-01-01",
    "que": "apple"
  },
  {
    "sid": "2",
    "bid": "5",
    "hrs": "6",
    "dat": "pine"
  }
]

在线演示：

这里

编辑：

PIVOT 会自动按 stroeid ASC 排序，而不需要指定任何顺序。如果你想要改为其他顺序，比如 DESC stroeid，那么你可以按以下方式更改查询语句。

select sid, bid, time,que, hrs,dat 
from #store_data src
pivot
(
  MAX([value])
  for [key] in (sid, bid, time,que, hrs,dat)
) piv
order by storeid desc
for json auto

- PSK

---

嗨，虽然这可能是正确的方法，但我们不能厌倦地指出一个事实，那就是没有隐式排序顺序！如果没有最外层的ORDER BY，它可以工作一百次，通过所有内部测试，但在生产中会返回垃圾。如果bid=3出现在第一个或第二个JSON对象中，那么它就是随机的。 - Shnugo

嗨@Shnugo，我不确定我是否理解了重点。您的评论超出了我的知识范围。您能否请再详细解释一下或提供一些示例数据，以便可以重新生成该场景？ - PSK

好的，我有点太快了，因为我没有在第一眼看到包含在SELECT *中的storeid。有了这些信息，对象将获得正确的值（作为组）。如果没有storeid作为最终JSON的一部分，仍然成立，即您无法保证哪个JSON对象将首先出现，哪个将出现第二个。但是这并不真正违反您的方法（+1）。 - Shnugo

我很高兴你已经找到了“显示顺序”这个点 @Shnugo.....我请求 PSK 和 Shnugo 是否有办法在不使用 while 循环的情况下以相同的顺序显示结果。 - Smart003

@Smart003，我已经编辑了答案，你可以看一下。 - PSK

@PSK，尽管PIVOT会隐式地对您的集合进行排序，但只有最外层的SELECT才起作用。这将如预期般返回1百万次，但最外层的SELECT（形成JSON）可以随时决定以不同的方式返回。如果您想确保排序顺序，您应该始终添加最后的ORDER BY... - Shnugo

Answer 2

试试这个：

drop table if exists #store_data;

create table #store_data ([key] nvarchar(max),[value] nvarchar(max),storeid varchar(100)
)

INSERT INTO #store_data VALUES ('sid','1','1')
INSERT INTO #store_data VALUES ('bid','3','1');
INSERT INTO #store_data VALUES ('time','2019-01-01','1');
INSERT INTO #store_data VALUES ('que','apple','1');
INSERT INTO #store_data VALUES ('sid','2','2');
INSERT INTO #store_data VALUES ('bid','5','2');
INSERT INTO #store_data VALUES ('hrs','6','2');
INSERT INTO #store_data VALUES ('dat','pine','2');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@ColumnNames NVARCHAR(MAX);

SELECT @ColumnNames = STUFF
(
    (
        SELECT DISTINCT ',[' + [key] + ']'
        FROM #store_data
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,1
   ,''
);

SET @DynamicTSQLStatement = N'
select ' + @ColumnNames + '
from #store_data
PIVOT 
(
    MAX([value]) FOR [key] IN (' + @ColumnNames + ')
) PVT
FOR JSON PATH;
';


EXEC sp_executesql @DynamicTSQLStatement;

将代码改为SELECT *，以便在JSON对象中也得到storeid。此外，在SQL Server 2017中，您可以使用STRING_AGG函数来连接键。

- gotqn

---

该查询被标记为“SQL Server 2017”。 - Panagiotis Kanavos

是的，这就是为什么我说他可以使用STRING_AGG。 - gotqn

为什么要发布XML串联技术呢？这掩盖了实际解决方案，即在将数据转换为JSON之前对其进行PIVOT。 - Panagiotis Kanavos

你可能会在关于“不存在隐式排序顺序”的问题下面阅读到我的评论。 - Shnugo

@Shnugo 是的，你说得对。这就是为什么我指出我们可以使用 SELECT * 来获取每个对象中的 storeid。它似乎很正常地在那里，因为它用于分组数据。 - gotqn