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 · Answer 1 · 2018-09-27T12:33:25.970

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"
  ]
}

`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 `id`s that are GUIDs). — Jeroen Mostert, Sep 27 '18 at 10:54
@JeroenMostert Hadn't thought about that: you're absolutely right! I updated my answer including `STRING_ESCAPE`, thank you. — Andrea, Sep 27 '18 at 12:36

Shnugo · Answer 2 · 2018-09-27T14:41:38.607

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

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.) — Jeroen Mostert, Sep 27 '18 at 14:48
This whole JSON feature in MS SQL Server seems incomplete to me. — KSib, Jan 31 '19 at 20:06

score 0 · Answer 3 · answered Jun 20 '19 at 01:28

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

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

3 Answers3