skip to Main Content

Given the following test data:

declare @mg nvarchar(max);
set @mg = '{"fiskepind":["ko","hest","gris"]}';

select @mg, JSON_VALUE(@mg,'$.fiskepind')

How do i get returned a column with:

   ko,hest,gris

Example returns: NULL, and i dont want to [index] to only get one returned.

2

Answers


  1. If you just want a combine list, you can use OPENJSON to get a table and then use FOR XML PATH or STRING_AGG to combine into a single string.

    declare @mg nvarchar(max);
    set @mg = '{"fiskepind":["ko","hest","gris"]}';
    
    select @mg, JSON_VALUE(@mg,'$.fiskepind')
      , STUFF((
        SELECT
          ',' + value
        FROM OPENJSON(@mg, '$.fiskepind')
        FOR XML PATH('')
      ),1,1,'') as combined_list
    
    Login or Signup to reply.
  2. Starting from SQL Server 2017, a possible solution is a combination of OPENJSON() and STRING_AGG().

    SELECT STRING_AGG([value], ',') WITHIN GROUP (ORDER BY CONVERT(int, [key])) AS Result
    FROM OPENJSON(@mg, '$.fiskepind')
    

    Note, that JSON_VALUE() returns a scalar value, so the NULL value is the expected result when you try to extract a JSON array ('$.fiskepind') from the input JSON text.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search