Get array of values instead of array of objects when using FOR JSON

Question

I'm trying to flatten out an object array that is constructed by FOR JSON.

My query looks like:

select 
(                           
    select id from MyTable
    where id in (select value from OPENJSON(@jsonArray))
    FOR JSON PATH
) existing,                 
(   
    select value id from OPENJSON(@jsonArray) 
    where value not in (select Id from MyTable)
    FOR JSON PATH                       
) missing
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

The resulting JSON is:

{
    "existing": [
        {
            "id": "a00cd8f6-d1c6-4604-b235-59d3cacd5bcc"
        },
        {
            "id": "052455b6-6bf5-47d3-8bee-7ba98d7fbd50"
        }
    ],
    "missing": [
        {
            "id": "328add2d-e8f2-4a0e-af54-5b1733310170"
        }
    ]
}

What I would like instead is:

{
    "existing": [
        {
            "id": "a00cd8f6-d1c6-4604-b235-59d3cacd5bcc"
        },
        {
            "id": "052455b6-6bf5-47d3-8bee-7ba98d7fbd50"
        }
    ],
    "missing": [
        "328add2d-e8f2-4a0e-af54-5b1733310170"            
    ]
}

The missing array should not contain json-objects, just values. Any suggestions?

Andrea · Accepted Answer · 2018-09-27 12:33:25Z

3

If you are using SQL Server 2017 you can build your array with JSON_QUERY and STRING_AGG (with SQL Server 2016 you can't use STRING_AGG, so you'll have to do a bit of estra work, but the following idea would still be valid):

declare @missing table(id varchar(max))
declare @existing table(id varchar(max))

insert into @missing values ('a00cd8f6-d1c6-4604-b235-59d3cacd5bcc')
insert into @missing values ('052455b6-6bf5-47d3-8bee-7ba98d7fbd50')
insert into @existing values ('328add2d-e8f2-4a0e-af54-5b1733310170')

select  
(                           
    select id from @missing
    FOR JSON PATH
) existing,                
(   
    select JSON_QUERY(concat('[' , STRING_AGG(concat('"' , STRING_ESCAPE(id, 'json') , '"'),',') , ']')) 
    from @existing                 
) missing 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{
  "existing": [
    {
      "id": "a00cd8f6-d1c6-4604-b235-59d3cacd5bcc"
    },
    {
      "id": "052455b6-6bf5-47d3-8bee-7ba98d7fbd50"
    }
  ],
  "missing": [
    "328add2d-e8f2-4a0e-af54-5b1733310170"
  ]
}

edited Sep 27, 2018 at 12:33

answered Sep 27, 2018 at 10:52

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeroen Mostert Over a year ago

QUOTENAME will not produce valid JSON if id contains any characters to escape. I suggest using '"' + STRING_ESCAPE(id, 'json') + '"' instead (even if it doesn't matter in the case of ids that are GUIDs).

Andrea Over a year ago

@JeroenMostert Hadn't thought about that: you're absolutely right! I updated my answer including STRING_ESCAPE, thank you.

Gottfried Lesigang · Accepted Answer · 2018-09-27 14:41:38Z

-1

This is not as easy as it should be...

AFAIK there's no way to create a naked json array with sql-server. But you can trick this out on string level:

DECLARE @exist TABLE(id VARCHAR(100));
DECLARE @miss TABLE(id VARCHAR(100));

INSERT INTO @exist VALUES ('exist1'),('exist2');
INSERT INTO @miss VALUES ('miss1'),('miss2');

--This will create the object array you want

SELECT id FROM @exist
FOR JSON PATH

--And this will create the naked array using some rather ugly tricks.

SELECT REPLACE(REPLACE(REPLACE(
(
    SELECT id from @miss
    FOR JSON PATH
),'"id":',''),'{',''),'}','')

--Now we have to combine both. And again we need a trick. We use JSON_QUERY() on the JSON literal to avoid escaped quotes.

SELECT
(
    SELECT id FROM @exist
    FOR JSON PATH
) AS existing
,JSON_QUERY(
   REPLACE(REPLACE(REPLACE(
    (
        SELECT id from @miss
        FOR JSON PATH
    ),'"id":',''),'{',''),'}','')
) AS missing
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

This ist the result

{
  "existing":[{"id":"exist1"},{"id":"exist2"}]  <--array of objects
 ,"missing":["miss1","miss2"]                   <--array of naked values
}

I have no idea, why this can't be done out-of-the-box...

edited Sep 27, 2018 at 14:41

answered Sep 27, 2018 at 13:56

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

2 Comments

Jeroen Mostert Over a year ago

This doesn't work correctly if the JSON values themselves contain { or } characters -- these will be ruthlessly stripped out by the REPLACE. (No JSON value will contain "id": literally, of course, so that's still safe.)

KSib Over a year ago

This whole JSON feature in MS SQL Server seems incomplete to me.

Nats · Accepted Answer · 2019-06-20 01:28:07Z

use append from a cursor

DECLARE @missing nvarchar(max),
    @json nvarchar(max) = (select 
                            (                           
                                select id from MyTable
                                where id in (select value from OPENJSON(@jsonArray))
                                FOR JSON PATH
                            ) existing
                            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

    DECLARE missing_cursor CURSOR FOR   
        select value id 
        from OPENJSON(@jsonArray)
        where value not in (select Id from MyTable)

        OPEN missing_cursor  

        FETCH NEXT FROM missing_cursor   
        INTO @missing

        WHILE @@FETCH_STATUS = 0  
        BEGIN  
            SET @json = JSON_MODIFY(@json,'append $.missing', @missing)

            FETCH NEXT FROM missing_cursor   
            INTO @missing  
        END   
        CLOSE missing_cursor;  
        DEALLOCATE missing_cursor; 

    select @json

Collectives™ on Stack Overflow

Get array of values instead of array of objects when using FOR JSON

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related