Search code examples
arrayssql-serveropen-json

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


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


Solution

  • 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