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.
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.
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
