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
If you just want a combine list, you can use
OPENJSON
to get a table and then useFOR XML PATH
orSTRING_AGG
to combine into a single string.Starting from SQL Server 2017, a possible solution is a combination of
OPENJSON()
andSTRING_AGG()
.Note, that
JSON_VALUE()
returns a scalar value, so theNULL
value is the expected result when you try to extract a JSON array ('$.fiskepind'
) from the input JSON text.