SQL 到 JSON - 对象数组到 SQL 2016 中的值数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37708638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to JSON - array of objects to array of values in SQL 2016
提问by Meghana Raj Jayanarasimha
SQL 2016 has a new feature which converts data on SQL server to JSON. I am having difficulty in combining array of objects into array of values i.e.,
SQL 2016 有一个新功能，可以将 SQL 服务器上的数据转换为 JSON。我在将对象数组组合成值数组时遇到困难，即，
EXAMPLE -
例子 -
CREATE TABLE #temp (item_id VARCHAR(256))
INSERT INTO #temp VALUES ('1234'),('5678'),('7890')
SELECT * FROM #temp
--convert to JSON
SELECT (SELECT item_id
FROM #temp
FOR JSON PATH,root('ids'))
RESULT -
结果 -
{
"ids": [{
"item_id": "1234"
},
{
"item_id": "5678"
},
{
"item_id": "7890"
}]
}
But I want the result as -
但我希望结果为 -
"ids": [
"1234",
"5678",
"7890"
]
Can somebody please help me out?
有人可以帮我吗？
采纳答案by Meghana Raj Jayanarasimha
Thanks! The soultion we found is converting into XML first -
谢谢！我们发现的灵魂首先转换为 XML -
SELECT
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"'
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER
回答by Magne Rekdal
Martin!
马丁！
I believe this is an even simpler way of doing it:
我相信这是一种更简单的方法：
SELECT '"ids": ' +
REPLACE(
REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
'"}','"' )
回答by massther
declare @temp table (item_id VARCHAR(256))
INSERT INTO @temp VALUES ('123"4'),('5678'),('7890')
SELECT * FROM @temp
--convert to JSON
select
json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path
When we want to concatenate strings as json array then:
当我们想将字符串连接为 json 数组时：
1) escape string - STRING_ESCAPE
1) 转义字符串 - STRING_ESCAPE
2) concatenate string with comma separator - STRING_AGG, comma ascii code is 44
2) 用逗号分隔符连接字符串 - STRING_AGG，逗号 ascii 代码为 44
3) add quotation it in brackets - QUOTENAME (without param)
3）在括号中添加引号 - QUOTENAME（不带参数）
4) return string (with array of elements) as json - JSON_QUERY
4) 返回字符串（带有元素数组）作为 json - JSON_QUERY
回答by CalvinDale
Since arrays of primitive values are valid JSON, it seems strange that a facility for selecting arrays of primitive values isn't built into SQL Server's JSON functionality. (If on the contrary such functionality exists, I at least haven't been able to discover it after quite a bit of searching).
由于原始值数组是有效的 JSON，SQL Server 的 JSON 功能中没有内置用于选择原始值数组的工具，这似乎很奇怪。（如果相反这样的功能存在，我至少在经过大量搜索后还没有发现它）。
The approach outlined above works as described. But when applied for a field in a larger query, the array of primitives is surrounded with quotes.
上面概述的方法如描述的那样工作。但是当应用于更大查询中的字段时，原始数组被引号包围。
E.g., this
例如，这个
DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');
works by producing:
通过生产工作：
"Children": ["101026,"101027]
But, following the approach above, this:
但是，按照上述方法，这是：
SELECT
p.PartNumber,
p.Description,
REPLACE(REPLACE((SELECT
ChildNumber
FROM
Part.BillOfMaterials
WHERE
ParentNumber = p.PartNumber
ORDER BY
ChildNumber
FOR
JSON AUTO
), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
Part.Parts AS p
WHERE
p.PartNumber = N'104444'
FOR
JSON PATH
Produces:
产生：
[
{
"PartNumber": "104444",
"Description": "ASSY HUB R-SER DRIV HP10 ABS",
"Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
}
]
Where the Childrenarray is wrapped as a string.
当孩子阵列封装为一个字符串。
回答by Eric
Most of these solutions are essentially creating a CSV that represents the array contents, and then putting that CSV into the final JSON format. Here's what I use, to avoid XML:
大多数这些解决方案本质上是创建一个代表数组内容的 CSV，然后将该 CSV 放入最终的 JSON 格式。这是我使用的，以避免 XML：
DECLARE @tmp NVARCHAR(MAX) = ''
SELECT @tmp = @tmp + '"' + [item_id] + '",'
FROM #temp -- Defined and populated in the original question
SELECT [ids] = JSON_QUERY((
SELECT CASE
WHEN @tmp IS NULL THEN '[]'
ELSE '[' + SUBSTRING(@tmp, 0, LEN(@tmp)) + ']'
END
))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
