SQL Server result as Json Array

Question

我需要执行一个输出 Json 数组的查询。

SQL Server具有FOR JSON PATH;功能，但输出将是：

[{"key_1":"value_1","key_2":"value_2"},{"key_1":"value_3","key_2":"value_4"}]

但我需要的输出是：

{ "key_1": ["value_1","value_3"],
"key_2": ["value_2","value_4"]
}

这能做到吗？

Answer 1

在FOR JSON PATH后添加, WITHOUT_ARRAY_WRAPPER

https://learn.microsoft.com/en-us/sql/relational-databases/json/remove-square-brackets-from-json-without-array-wrapper-option?view=sql-server-ver15

更新：

这不是最漂亮的解决方案，但它有效。我试图根据输出的原始格式重新创建一些数据。所以答案是基于这个假设。

-- Test data (based on what I could figure out from your question)
CREATE TABLE dbo.jsonvalues
(
key_1 NVARCHAR(MAX)
,key_2 NVARCHAR(MAX)
);
INSERT INTO dbo.jsonvalues
(
key_1
,key_2
)
VALUES
(
N'value_1'
,N'value_2'
)
,(
N'value_3'
,N'value_4'
);

要以正确的格式获取数据，首先必须对数据进行透视(或以不同的方式存储数据(。然后，您可以使用SELECT中的子查询在输出中创建数组。

SELECT
*
INTO
#newformat
FROM
dbo.jsonvalues AS J
UNPIVOT
(
vals FOR keys IN (key_1, key_2)
) AS u;
SELECT
N.keys
,(SELECT N2.vals FROM #newformat AS N2 WHERE N2.keys = N.keys FOR JSON AUTO) AS vals
FROM
(SELECT DISTINCT
keys
FROM
#newformat
) AS N
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

输出：

{
"keys":"key_1","vals":[{"vals":"value_1"},{"vals":"value_3"}]
},
{
"keys":"key_2","vals":[{"vals":"value_2"},{"vals":"value_4"}]
}

这不会给你你想要的，但这是我在不开始做一些手动调整的情况下所能得到的最接近的。我在下面进行了调整，以达到您要求的输出格式。请注意，它很脆弱，可能需要对您的数据进行其他调整。

SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE((
SELECT
N.keys
,(SELECT N2.vals FROM #newformat AS N2 WHERE N2.keys = N.keys FOR JSON AUTO) AS vals
FROM
(SELECT DISTINCT
keys
FROM
#newformat
) AS N
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
),'"keys":',''),'"vals"',''),',:',':'),'},{:',','),'{:',''),'}]}',']');

输出：

{
"key_1":["value_1","value_3"]
,"key_2":["value_2","value_4"]
}