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?

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 

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":','' ),       '"}','"' ) 

Answer 3

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":','' ),
      '"}','"' )

Answer 4

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

Answer 5

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.