none
SQL2016: How do I put all values in a JSON array ? RRS feed

  • Question

  • Hi,

    We use SQL 2016 to read and generate JSON Messages.
    I have the following example code:

    DECLARE @DenomTable  TABLE (Primkey int, denom varchar(5));

    DECLARE @JSONMessage Nvarchar(max);

    SET @JSONMessage = '{"denom": ["EUR5", "EUR10", "EUR20", "EUR50"]}';

    INSERT INTO @DenomTable
     SELECT  
       [key]
       ,denom.[value]
     FROM
      openjson (@JSONMessage)
     with
      (   
       denom       nvarchar(max) AS JSON       
      ) as Parent

     CROSS APPLY
      OPENJSON (denom) as denom

    SELECT
     denom
    FROM
     @DenomTable
    FOR
     JSON Path, WITHOUT_ARRAY_WRAPPER
    -- How do I rewrite the above query so I have the values in an Array, just like the JSON on top ?


    • Edited by Hatsekidee Wednesday, January 22, 2020 7:39 AM
    Wednesday, January 22, 2020 7:38 AM

All replies

  • select '''{"denom": ["'+stuff(
      (select ',' + denom as [text()] from @DenomTable order by denom for xml path('')),
      1, 1, '')+'"]}''';

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 22, 2020 7:45 AM
    Answerer
  • Can you give example of output?

    Maybe I missed something but it seems very simple. Just remove the "FOR JSON PATH" part

    SELECT denom FROM @DenomTable -- or

    SELECT string_agg(denom,',') as values_array FROM @DenomTable



    Uri K.

    Wednesday, January 22, 2020 9:39 AM
  • The Query must be a JSON result if you add a quote it will be escaped by for JSon and will treat it as a string.

    It must be a Json because it's part of a bigger Json Query (For JSON Path).

    --

    In SQL2016 there is a .[value] for reading JSON values in an array, I need this also to generate the JSON Message: {"denom": ["EUR5", "EUR10", "EUR20", "EUR50"]}

    Every other solution with concat strings will result that the quotes will be escaped if you use For JSON Path in the query.

    • Edited by Hatsekidee Wednesday, January 22, 2020 11:58 AM
    Wednesday, January 22, 2020 10:10 AM
  • Hi Hatsekidee, 

    Could you please share us more information like your expected result ? 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 23, 2020 7:19 AM
  • Check this script too:

    declare @result as nvarchar(max) = '{ }'
    
    select @result = JSON_MODIFY( @result, 'append lax $.denom', denom ) 
    from @DenomTable 
    order by Primkey
    
    select @result as Result

    /*
    Result
    { "denom":["EUR5","EUR10","EUR20","EUR50"]}
    */



    • Edited by Viorel_MVP Thursday, January 23, 2020 6:45 PM
    Thursday, January 23, 2020 6:40 PM
  • @Rachel: Expected result is Json the same as I insert in the table, but now I want to generate it from the table.

    The complete query will be a nested (FOR JSON Path) query, so concatenating strings will not work, this will escape all quotes.

    Friday, January 24, 2020 7:40 AM
  • tnx, it's close but this JSON query will be part of a bigger query with "FOR JSON Path".

    So If you add For JSON Path at the end it will still escape all quotes:

    DECLARE @DenomTable  TABLE (Primkey int, denom varchar(5));
    DECLARE @JSONMessage Nvarchar(max);
    SET @JSONMessage = '{"denom": ["EUR5", "EUR10", "EUR20", "EUR50"]}';
    INSERT INTO @DenomTable
     SELECT 
       [key]
       ,denom.[value]
     FROM
      openjson (@JSONMessage)
     with
      (  
       denom       nvarchar(max) AS JSON      
      ) as Parent
     CROSS APPLY
      OPENJSON (denom) as denom
    SELECT
     denom
    FROM
     @DenomTable
    FOR
     JSON Path, WITHOUT_ARRAY_WRAPPER
     declare @result as nvarchar(max) = '{ }'
    select @result = JSON_MODIFY( @result, 'append lax $.denom', denom )
    from @DenomTable
    order by Primkey
    select @result as Result for Json Path

    Friday, January 24, 2020 7:45 AM
  • tnx, it's close but this JSON query will be part of a bigger query with "FOR JSON Path".

    So If you add For JSON Path at the end it will still escape all quotes:

    [...]

    In this case, try:

    select JSON_QUERY(@result, '$') as Result for json path


    Friday, January 24, 2020 7:50 AM