SQL to JSON - array of objects to array of values in SQL 2016

Question

SQL 2016 has a new feature which converts data on SQL server to JSON. I am having difficulty in combining array of objects into array of values i.e.,

EXAMPLE -

CREATE TABLE #temp (item_id VARCHAR(256))

INSERT INTO #temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM #temp

--convert to JSON

SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH,root('ids')) 

RESULT -

{
    "ids": [{
        "item_id": "1234"
    },
    {
        "item_id": "5678"
    },
    {
        "item_id": "7890"
    }]
}

But I want the result as -

"ids": [
        "1234",
        "5678",
        "7890"
    ]

Can somebody please help me out?

Buffybuford

I think that might just be the way it works. You might notice that FOR JSON returns an array of key:value pairs even if we might like to have something simpler e.g. plain array of values instead of array of objects. In this case we can write simple T-SQL user defined function that removes keys from the array and return plain array: – Boltonia

It is a serious hamper as FOR JSON doesn't have a way to specify 'array paths', which would make this transformation trivial without needing manual (hackish) building of strings: both the ability to specify an index (for tuples) and a 'last' (for building nested of arrays of indeterminate length). Maybe such a glaring omission in JSON generation will be correct in 2029? sigh – Nedrud

SQL Server 2022 added JSON_ARRAY, but this can only produce arrays from single values, not multiple rows in a table – Barchan

Answer 1

Thanks! The soultion we found is converting into XML first -

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER 

Buffybuford

I think due to the performance issue of FOR XML query it's not a good practice – Assimilable

we can use string_escape(item_id, N'json') to avoid producing invalid json format. – Ortolan

I've been using this one for quite some time. Is there a shorter version works with SQL server 2016? – Tholos

So ugly, but gets the job done. – Dimeter

Answer 2

Martin!

I believe this is an even simpler way of doing it:

    SELECT '"ids": ' + 
    REPLACE( 
      REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
      '"}','"' )

Santoyo

This is a much better hack than any other I've seen. And unfortunately, this has reached the graveyard of feature requests. – Spade

Answer 3

declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM @temp

--convert to JSON

select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path

When we want to concatenate strings as json array then:

1. escape string - STRING_ESCAPE

2. concatenate string with comma separator - STRING_AGG, comma ascii code is 44

3. add quotation it in brackets - QUOTENAME (without param)

4. return string (with array of elements) as json - JSON_QUERY

Bihar

STRING_AGG isn't available in SQL 2016 ? – Singular

If you are running a recent version of SQL Server, this is the cleanest solution. – Greenroom

For newer SQL versions, this is definitely the answer. If you are making a synthesized result set where this array is part of a larger object, just make this a subquery and leave off FOR JSON PATH and the alias then just alias the subquery. – Awad

Why is char(44) used for the comma instead of a literal ','? – Preach

While this is the cleanest solution, the quotename function here is wrong. It not only adds brackets around the value, but also escapes close brackets, ], inside it, according to the rules of SQL escaping, not JSON. So if a string value from @temp contains a close bracket, it will be incorrectly escaped. The correct approach is '[' + string_agg(...) + ']'. – Fant

NOTE: for the QUOTENAME() function, Inputs greater than 128 characters return NULL. – Squarerigged

Using quotename is incorrect - both in purpose and limitations. – Nedrud

Answer 4

Since arrays of primitive values are valid JSON, it seems strange that a facility for selecting arrays of primitive values isn't built into SQL Server's JSON functionality. (If on the contrary such functionality exists, I at least haven't been able to discover it after quite a bit of searching).

The approach outlined above works as described. But when applied for a field in a larger query, the array of primitives is surrounded with quotes.

E.g., this

DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');

works by producing:

"Children": ["101026,"101027]

But, following the approach above, this:

SELECT
    p.PartNumber,
    p.Description,
    REPLACE(REPLACE((SELECT
                        ChildNumber
                     FROM
                        Part.BillOfMaterials
                     WHERE
                        ParentNumber = p.PartNumber
                     ORDER BY
                        ChildNumber
                    FOR
                     JSON AUTO
                    ), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
    Part.Parts AS p
WHERE
    p.PartNumber = N'104444'
FOR
    JSON PATH

Produces:

[
    {
        "PartNumber": "104444",
        "Description": "ASSY HUB           R-SER  DRIV HP10  ABS",
        "Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
    }
]

Where the Children array is wrapped as a string.

Chinquapin

Add JSON_QUERY() around REPLACE. That will disable redundant escaping – Phooey

Answer 5

This version (building on the others):

• correctly escapes an special JSON characters (e.g. quotes)
• returns an empty array [] for no data

Requires SQL 2017 or later (due to STRING_AGG):

    SELECT 
       CONCAT('[', 
            (SELECT STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', ',') 
             FROM #temp) 
        , ']')

Shirtwaist

Correction: This requires SQL 2017 or later due to STRING_AGG. – Nedrud

Answer 6

Here's a wild idea that may or may not be practical. Recurse over your data set and append things to your JSON arrays using JSON_MODIFY:

with
  d (d) as (select * from (values (1),(2),(3),(4)) t (d)),
  j (d, j) as (
    -- Adapt the recursion to make it dynamic
    select 1, json_modify('[]', 'append $', d)
    from d
    where d = 1
    union all
    select d.d, json_modify(j, 'append $', d.d)
    from d join j on d.d = j.d + 1
  )
select * 
from j;

I kept it simple for illustration purposes. You'll adapt it to make it dynamic, of course. This produces:

|d  |j        |
|---|---------|
|1  |[1]      |
|2  |[1,2]    |
|3  |[1,2,3]  |
|4  |[1,2,3,4]|

Could even be used to emulate standard SQL JSON_ARRAYAGG

Hacksaw

Answer 7

I like @massther's answer, for SQL Server 2017 and above. However, the resultant JSON is wrapped in an array. To get rid of the array, use the WITHOUT_ARRAY_WRAPPER option in the FOR JSON clause.

Also, as someone mentioned in the comments, theQUOTENAME() function causes problems if any of the data contains a closing square bracket, ].

Below is massther's original version and a modified version with these changes.

declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('1234'),('5678'),('7890'),('[problem]')

SELECT * FROM @temp

--convert to JSON

-- Original version:
select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) 
        as [json]
from @temp
for json path

-- Modified version: 
--    Replaced QUOTENAME() with '[' + ... + ']'
--    Replaced char(44) as the separator character with ','
--    Added WITHOUT_ARRAY_WRAPPER option.
select 
    json_query('[' + STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', ',') + ']') 
        as [json]
from @temp
for json path, WITHOUT_ARRAY_WRAPPER;

Results:

Original version:

Note that it is a JSON array, not a JSON object, and the double "]]" following the "problem" text.

[
    {
        "json": [
            "1234",
            "5678",
            "7890",
            "[problem]]"
        ]
    }
]

Modified version:

A JSON object, not a JSON array, and the closing "]" following the "problem" text is handled correctly.

{
    "json": [
        "1234",
        "5678",
        "7890",
        "[problem]"
    ]
}

Felonious

Answer 8

Most of these solutions are essentially creating a CSV that represents the array contents, and then putting that CSV into the final JSON format. Here's what I use, to avoid XML:

DECLARE @tmp NVARCHAR(MAX) = ''

SELECT @tmp = @tmp + '"' + [item_id] + '",'
FROM #temp -- Defined and populated in the original question

SELECT [ids] = JSON_QUERY((
    SELECT CASE
        WHEN @tmp IS NULL THEN '[]'
        ELSE '[' + SUBSTRING(@tmp, 0, LEN(@tmp)) + ']'
        END
    ))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Airspeed

SELECT $tmp = $tmp + '"' + [item_id] + '",' FROM #temp is not a proper way of concatenating strings. It is not guaranteed to produce the correct result. (had to replace @ with $ for the comment) – Costanzia

Answer 9

I think the following would be easier in SQL server 2017

select
 JSON_QUERY
 (
     '["' + STRING_AGG(t.item_id,'","') + '"]'
 ) as ids
 from #temp t
 for json auto, without_array_wrapper

Swoon