How to select multiple values inside array of JSON values?-CodePudding

I have multiple arrays of JSON values:

[{"header": "A", "value": "test"}, {"header": "B", "value": "test1"}, {"header": "C", "value": "test2"}]

[{"header": "A1", "value": "test"}, {"header": "B1", "value": "test1"}, {"header": "C1", "value": "test2"}]

[{"header": "A2", "value": "test"}, {"header": "B2", "value": "test1"}, {"header": "C2", "value": "test2"}]

How do I select all of the headers in the array of JSONs? I used JSON_VALUE and can only get the provided array index:

SELECT JSON_VALUE(Columns, '$[0].header') FROM Files

1: A
2: A1
3: A2

My expected result is:

1: A, B, C
2: A1, B1, C1
3: A2, B2, C2

CodePudding user response：

You could do it like:

SELECT 
  JSON_VALUE(Columns, '$[0].header') as Col1, 
  JSON_VALUE(Columns, '$[1].header') as Col2,
  JSON_VALUE(Columns, '$[2].header') as Col3 FROM Files;

DBFiddle demo

EDIT: If key, header would do:

SELECT t.[key], z.header from files cross apply OpenJson(Columns) t cross apply OpenJson(t.Value) with (header VARCHAR(10)) z;

DBFiddle demo

With string_agg added:

SELECT t.[key], string_agg(z.header,',') headers
  from files 
  cross apply OpenJson(Columns) t
  cross apply OpenJson(t.Value) with (header VARCHAR(10)) z
group by t.[key];

DBFiddle demo