SQL Server 2016 JSON:选择字符串数组而不是对象数组 [英] SQL Server 2016 JSON: Select array of strings instead of array of objects
本文介绍了SQL Server 2016 JSON:选择字符串数组而不是对象数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是SQL Server中JSON的新手,无法弄清楚如何返回简单的字符串数组:
I am new to JSON in SQL Server and can't figure out how to return a simple array of strings:
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希望它返回:
["foo","bar","test","ok"]
这甚至可以做到吗?
推荐答案
在AdventureWorks 2016 CTP3 JSON示例中,您可以找到一个可以清除key:value对数组并创建od值的函数:
In AdventureWorks 2016 CTP3 JSON sample you can find a function that can clean array of key:value pairs and create array od values:
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参数,并将要删除的键名作为第二个参数即可。可能是这样的:
Just provide result of your SELECT FOR JSON expression as @json parameter and name of the key that you want to remove as second parameter. Probably something like:
select dbo.ufnToRawJsonArray( (SELECT value FROM mytable for json path), 'value')
这篇关于SQL Server 2016 JSON:选择字符串数组而不是对象数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文