Question 111092 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Sql Server: Select String array of JSON

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.

Tags:

2

Answers


  1. 0

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

    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