json - 如何在 MS-SQL 2016 中将列中的 JSON 值查询到一个 JSON 数组中？

Question

我开始研究如何在 MSSQL 2016+ 中处理 JSON

我只是创建了一个具有 ID (int) 和 JSON (nvarchar) 列的表。

以下是我的查询以显示该问题:

第一个查询只返回关系表结果，很好，符合预期。

SELECT * FROM WS_Test

-- Results:

1   { "name": "thomas" }
2   { "name": "peter" }

第二个查询仅返回 json 列，因为“JSON”创建了我的 MSSQL。
不太好，因为它将 json 列内容输出为字符串，而不是解析后的 JSON。

SELECT json FROM WS_Test FOR JSON PATH

-- Results: 

[{"json":"{ \"name\": \"thomas\" }"},{"json":"{ \"name\": \"peter\" }"}]

第三个查询给了我两个结果行，其中 json 列内容为已解析的 JSON，很好。

SELECT JSON_QUERY(json, '$') as json FROM WS_Test

-- Results:

{ "name": "thomas" }
{ "name": "peter" }

第四个查询为我提供了 json 列内容作为一个(!)JSON 对象，完美解析。

SELECT JSON_QUERY(json, '$') as json FROM WS_Test FOR JSON PATH

-- Results:

[{"json":{ "name": "thomas" }},{"json":{ "name": "peter" }}]

但是:我不想在示例四的每个数组对象中拥有包含 json 列内容的“json”属性。我只想要一个包含列内容的数组，不能少，不能多。像这样:

[
    {
        "name": "peter"
    },
    {
        "name": "thomas"
    }
]

如何仅使用 T-SQL 来存档此内容？这可能吗？

Answer 1

FOR JSON 子句将始终包含列名称 - 但是，您可以简单地将 json 列中的所有值连接成一个结果，然后添加方括号括起来。

首先，创建并填充示例表(请在您以后的问题中保存此步骤):

CREATE TABLE WS_Test
(
    Id int, 
    Json nvarchar(1000)
);

INSERT INTO WS_Test(Id, Json) VALUES
(1, '{ "name": "thomas" }'),
(2, '{ "name": "peter" }');

对于 SQL Server 2017 或更高版本，请使用内置的 string_agg 函数:

SELECT '[' + STRING_AGG(Json, ',') + ']' As Result
FROM WS_Test

对于较低版本，您可以将 for xml path 与 stuff 一起使用，以获得与 string_agg 相同的结果:

SELECT STUFF(
(
    SELECT ',' + Json
    FROM WS_Test
    FOR XML PATH('')
), 1, 1, '[')+ ']' As Result

这两个查询的结果如下:

Result
[{ "name": "thomas" },{ "name": "peter" }]

您可以在 DB<>Fiddle 上观看现场演示