Btukfyl

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?

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 

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

      '"}','"' )

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.

declare @temp table (item_id VARCHAR(256))



INSERT INTO @temp VALUES ('123"4'),('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

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