0

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

AsitK
  • 651
  • 1
  • 4
  • 14
  • 1
    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 Nov 29 '22 at 06:26
  • updated the JSON value to correct format – AsitK Nov 29 '22 at 10:46

1 Answers1

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
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35