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

Question

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

The example "JSON" is just a single JSON string literal. Everything between the " characters would be invalid JSON, because the standard doesn't support ' characters to delimit properties and string literals, and so cannot be queried using SQL Server's built-in functions as you're expecting to do. — AlwaysLearning
– AlwaysLearning, Commented Nov 29, 2022 at 6:26

AlwaysLearning · Accepted Answer · 2022-11-29 06:42:32Z

2

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

answered Nov 29, 2022 at 6:42

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related