Sql Server: Select String array of JSON-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.