json - 如何使用 FOR JSON 构建这种格式的 JSON?
问题描述
我想使用FOR JSON
为 HTTP Post 调用构建数据有效负载。我的源表可以用这个片段重新创建:
drop table if exists #jsonData;
drop table if exists #jsonColumns;
select
'carat' [column]
into #jsonColumns
union
select 'cut' union
select 'color' union
select 'clarity' union
select 'depth' union
select 'table' union
select 'x' union
select 'y' union
select 'z'
select
0.23 carat
,'Ideal' cut
,'E' color
,'SI2' clarity
,61.5 depth
,55.0 [table]
,3.95 x
,3.98 y
,2.43 z
into #jsonData
union
select 0.21,'Premium','E','SI1',59.8,61.0,3.89,3.84,2.31 union
select 0.29,'Premium','I','VS2',62.4,58.0,4.2,4.23,2.63 union
select 0.31,'Good','J','SI2',63.3,58.0,4.34,4.35,2.75
;
数据需要格式化如下:
{
"columns":["carat","cut","color","clarity","depth","table","x","y","z"],
"data":[
[0.23,"Ideal","E","SI2",61.5,55.0,3.95,3.98,2.43],
[0.21,"Premium","E","SI1",59.8,61.0,3.89,3.84,2.31],
[0.23,"Good","E","VS1",56.9,65.0,4.05,4.07,2.31],
[0.29,"Premium","I","VS2",62.4,58.0,4.2,4.23,2.63],
[0.31,"Good","J","SI2",63.3,58.0,4.34,4.35,2.75]
]
}
到目前为止,我的尝试如下:
select
(select * from #jsonColumns for json path) as [columns],
(select * from #jsonData for json path) as [data]
for json path, without_array_wrapper
但是,这会返回对象数组而不是值,如下所示:
{
"columns":[
{"column":"carat"},
{"column":"clarity"},
{"column":"color"},
{"column":"cut"},
{"column":"depth"},
{"column":"table"},
{"column":"x"},
{"column":"y"},
{"column":"z"}
]...
}
如何将数组限制为仅显示值?
解决方案
老实说,使用字符串聚合而不是使用 JSON 功能似乎会更容易。
因为您使用的是 SQL Server 2016,所以您无权访问STRING_AGG
or CONCAT_WS
,因此代码要长很多。您必须使用FOR XML PATH
and手动插入所有分隔符(为什么表达式中STUFF
有这么多)。这导致以下结果:','
CONCAT
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SELECT N'{' + @CRLF +
N' "columns":[' + STUFF((SELECT ',' + QUOTENAME(c.[name],'"')
FROM tempdb.sys.columns c
JOIN tempdb.sys.tables t ON c.object_id = t.object_id
WHERE t.[name] LIKE N'#jsonData%' --Like isn't needed if not a temporary table. Use the literal name.
ORDER BY c.column_id ASC
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') + N'],' + @CRLF +
N' "data":[' + @CRLF +
STUFF((SELECT N',' + @CRLF +
N' ' + CONCAT('[',JD.carat,',',QUOTENAME(JD.cut,'"'),',',QUOTENAME(JD.color,'"'),',',QUOTENAME(JD.clarity,'"'),',',JD.depth,',',JD.[table],',',JD.x,',',JD.y,',',JD.z,']')
FROM #jsonData JD
ORDER BY JD.carat ASC
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + @CRLF +
N' ]' + @CRLF +
N'}';
推荐阅读
- javascript - /static/ 的位置在设置 app.route(/something/) 时更改为 /something/static/
) - android - 失败的 sepolicy 检查 - Android 10 构建 - Pixel 3a XL
- node.js - 我需要验证电话号码中的前四位数字,在 Node Js 中应该是“5678”。我该怎么做?我应该使用哪个验证器库
- php - 如何在箭头后应用 laravel 循环索引
- android - Espresso 无法点击显示的视图
- c# - 如何从中间件调用控制器方法并且只允许从该中间件调用该方法?
- c# - WebContentTypeMapper / GetMessageFormatForContentType 中的 HttpContext.Current null
- angular - @Input() 中的相同布尔值不会触发更改检测
- javascript - 如何在 ajax 中获取按钮所在的 DataTable 中的数据列?
- python - 如果 3 个值不能是不同的值,则查找最大值、最小值和中间值