T-SQL: JSON array

Question

I query API and JSON output I want to save to a SQL Server 2016 table. Here is how I simulate transforming JSON output:

When I run this:

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');

I get this:

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

But I expect this:

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

I spent hours and I could not figure it out. Please help.

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Feb 5, 2022 at 16:03

LukStorms · Accepted Answer · 2022-02-05 16:25:40Z

5

Simply join to the keys of the other keys.

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;

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

db<>fiddle here

answered Feb 5, 2022 at 16:25

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vlad Over a year ago

Thank You Very Much! It is exactly what I needed

Zhorov Over a year ago

@Vlad, if you think that this or any other answer is the best solution to your problem, you may accept it. Only one answer can be accepted.

Vlad Over a year ago

@Zhorov - I am new to Stack Overflow (and programming) and I just figured out that check-mark icon represents "Accept answer" and I just did that - Thanks!

LukStorms Over a year ago

@Vlad Thanks. Many new users overlook or ignore it. And your question was better than what the majority of the new users start with.

Collectives™ on Stack Overflow

T-SQL: JSON array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

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

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related