T-SQL: JSON array

Question

我查询要保存到SQL Server 2016表中的API和JSON输出。以下是我如何模拟转换JSON输出：

当我运行这个：

DECLARE @J NVARCHAR(MAX) = '{"c":[4.935,4.935,4.9374,4.935,4.94],"t":[1643998980,1643999040,1643999100,1643999160,1643999220],"v":[1979,87494,9980,4382,17713]}';
SELECT [key] as jkey, value as c
FROM OPENJSON(@j, '$.c');

我得到这个：

jkey,c
0,4.935
1,4.935
2,4.9374
3,4.935
4,4.94

但我期待这个：

jkey,c,t,v
0,4.935,1643998980,1979
1,4.935,1643999040,87494
2,4.9374,1643999100,9980
3,4.935,1643999160,4382
4,4.94,1643999220,17713

我花了好几个小时都想不通。请帮忙。

Answer 1

只需连接到其他键的键即可。

DECLARE @J NVARCHAR(MAX) = '{"c":[4.935,4.935,4.9374,4.935,4.94],"t":[1643998980,1643999040,1643999100,1643999160,1643999220],"v":[1979,87494,9980,4382,17713]}';
SELECT c.[key] as jkey, c.value as c, t.value as t, v.value as v
FROM OPENJSON(@j, '$.c') c
LEFT JOIN OPENJSON(@j, '$.t') t ON t.[key] = c.[key]
LEFT JOIN OPENJSON(@j, '$.v') v ON v.[key] = c.[key]
ORDER BY jkey;

0		1		2		3		4