How to use OPENJSON to concatenate json Array in a column of a table-CodePudding

I have a column in SQL table which has a string value denoting a JSON array. I need to concatenate the array for comparison. There are multiple rows of this data and will be using a CURSOR to iterate through a set of values because no example of using using OPENJSON on a column is available

there are solutions for iterating the array in a property ('$.list') of a JSON but nothing which talks about a direct array.

from:

ID	Value
1	[{"id": 1, "val":"j"}, {"id": 2,"val":"o"}, {"id": 3, "val":"h"}, {"id": 4, "val":"h";}, {"id": 5, "val":"n"}]
2	[{"id": 1, "val":"d"}, {"id": 2,"val":"o"}, {"id": 3, "val":"e"}]

Result:

ID	Result
1	john
2	doe

and then proceed with my other iteration logic on the Result table

CodePudding user response：

If you correct the example JSON by switching the " and ' characters around you could get your desired results by way of string_agg() and openjson()...

declare @Table table (
  ID int,
  [Value] nvarchar(max)
);

insert @Table (ID, [Value]) values
  (1, N'[{"id": 1, "val":"j"}, {"id": 2,"val":"o"}, {"id": 3, "val":"h"}, {"id": 4, "val":"h"}, {"id": 5, "val":"n"}]'),
  (2, N'[{"id": 1, "val":"d"}, {"id": 2,"val":"o"}, {"id": 3, "val":"e"}]');

select ID, string_agg(J.[val], N'') as [Result]
from @Table
cross apply openjson([Value]) with (
    [val] nvarchar(50)
) J
group by ID;

ID	Result
1	johhn
2	doe