SQL Server 2016 JSON: 查询字符串数组而不是对象数组

Question

我对SQL Server中的JSON不熟悉，无法弄清如何返回一个简单的字符串数组：

DECLARE @T TABLE ([value] NVARCHAR(MAX))

INSERT INTO @T ([value]) VALUES ('foo')
INSERT INTO @T ([value]) VALUES ('bar')
INSERT INTO @T ([value]) VALUES ('test')
INSERT INTO @T ([value]) VALUES ('ok')

SELECT [value]
FROM @T
FOR JSON PATH

这会返回一个对象数组：

[{"value":"foo"},{"value":"bar"},{"value":"test"},{"value":"ok"}]

I would like it to return:

["foo","bar","test","ok"]

这真的可行吗？

- nokturnal

---

我从这个问答中学到的是，SQL Server 2016宣传支持JSON，但却没有提供一种直接查询表并以JSON格式返回数组的简单方法。为什么我们必须手动构建数组字符串呢？唉。 - youcantryreachingme

Answer 1

在Loui Bao和Roman的回答基础上进行：

declare @t table ([value] nvarchar(max))

insert into @t ([value]) values ('foo')
insert into @t ([value]) values ('bar')
insert into @t ([value]) values ('test')
insert into @t ([value]) values ('ok')

SELECT
    JSON_QUERY((SELECT CONCAT('["',STRING_AGG([value], '","'),'"]') FROM @t)) As MuhArray
    OtherValue,
    AnotherValue
FROM MyTableOValues
FOR JSON PATH

这是创建一个符合JSON格式的简单值数组并将其赋值给属性MuhArray。从中输出的JSON内容如下：

[{
  MuhArray: ["foo", "bar", "test", "ok"],
  OtherValue: "Value",
  AnotherValue: "AnotherValue"
}]

在这里，OtherValue和AnotherValue接收了表中对应的任何值。通过一些调整，你也可以选择将其构建为主查询体中的简单连接，而不是作为子查询在选择列表中构建。在我看来，在选择列表中使用子查询可以避免使用distinct关键字。

- RobotOptimist

Answer 2

在SQL2017中，使用STRING_AGG代替json。这个函数是生成逗号分隔值列表的最佳选择。

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

SELECT town, STRING_AGG (email, ';') AS emails 
FROM dbo.Employee 
GROUP BY town;

- Roman Polunin

---

如果我执行 insert into @t ([value]) values ('test " , '' ')，那么这个程序就会出错，因为 for json path 可以处理字符转义，但是 string_agg 会破坏输出结果。只有在确定源代码不包含需要转义的字符时，这种方法才更快。 - Keith

STRING_ESCAPE 可以帮助转义字符串。https://learn.microsoft.com/zh-cn/sql/t-sql/functions/string-escape-transact-sql?view=sql-server-2017 - Roman Polunin

Answer 3

在AdventureWorks 2016 CTP3 JSON示例中，您可以找到一个函数，该函数可以清理键值对数组并创建值数组：

DROP FUNCTION IF EXISTS dbo.ufnToRawJsonArray
GO
CREATE FUNCTION
[dbo].[ufnToRawJsonArray](@json nvarchar(max), @key nvarchar(400)) returns nvarchar(max)
AS BEGIN
       declare @new nvarchar(max) = replace(@json, CONCAT('},{"', @key,'":'),',')
       return '[' + substring(@new, 1 + (LEN(@key)+5), LEN(@new) -2 - (LEN(@key)+5)) + ']'
END

只需将您的SELECT FOR JSON表达式的结果作为@json参数提供，并将要删除的键的名称作为第二个参数。可能是这样的：

select dbo.ufnToRawJsonArray( (SELECT value FROM mytable for json path), 'value')

- Jovan MSFT

Answer 4

在Roman的回答基础上:

declare @t table ([value] nvarchar(max))

insert into @t ([value]) values ('foo')
insert into @t ([value]) values ('bar')
insert into @t ([value]) values ('test')
insert into @t ([value]) values ('ok')

select concat('[', string_agg(concat('"', [value], '"'), ','), ']')
from @t

输出：

["foo", "bar", "test", "ok"]

- Louie Bao

Answer 5

使用以下代码适用于任何版本的Sql Server 2016及以上

declare @t table ([value] nvarchar(max))

insert into @t ([value]) values ('foo')
insert into @t ([value]) values ('bar')
insert into @t ([value]) values ('test')
insert into @t ([value]) values ('ok')

DECLARE @values nvarchar(max) = N'[]'; 
SELECT @values = JSON_MODIFY(@values,'append $',[value]) 
FROM @t SELECT @values

- user12116884

---

你可以直接执行以下代码： ` DECLARE @values nvarchar(max) = N'[]'; SELECT @values = JSON_MODIFY(@values,'append $',[value]) FROM @tSELECT @values` - Ilya Chernomordik

Answer 6

另一种解决方案

DECLARE @T TABLE ([value] NVARCHAR(MAX))

INSERT INTO @T ([value]) VALUES ('foo')
INSERT INTO @T ([value]) VALUES ('bar')
INSERT INTO @T ([value]) VALUES ('test')
INSERT INTO @T ([value]) VALUES ('ok')

SELECT JSON_QUERY('[' + STUFF((SELECT ',"' +  [value] + '"' from  @T FOR XML PATH('')), 1, 1, '')+']')

返回：

["foo","bar","test","ok"]

- Onur

---

不需要过程/函数的解决方案 - complete_stranger

Answer 7

这应该可以让你开始了。2016年有几种处理JSON的方法。这种方法定义了JSON字符串，提取其中的值，然后使用XML技巧将多行合并为一行。

DECLARE @T TABLE ([value] NVARCHAR(MAX))

INSERT INTO @T ([value])
VALUES ('foo')

INSERT INTO @T ([value])
VALUES ('bar')

INSERT INTO @T ([value])
VALUES ('test')

INSERT INTO @T ([value])
VALUES ('ok')

DECLARE @JSON NVARCHAR(MAX) = (
        SELECT *
        FROM @T
        FOR JSON PATH
        );

WITH cte
AS (
    SELECT *
    FROM OPENJSON(@json) WITH (NAME VARCHAR(10) '$.value')
    )
SELECT QUOTENAME(left(names, LEN(names) - 1) )AS names
FROM (
    SELECT DISTINCT (
            SELECT QUOTENAME(NAME,'"') + ',' AS [text()]
            FROM cte
            FOR XML PATH('')
            ) names
    FROM cte
    ) x

- dfundako