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

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

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

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.

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) 
        , ']')

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

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

I have found that using OPENJSON to split out the objects and then OPENJSON to obtain value of the first property of each object works well and easier to follow. I use this so often, that I have put this into a scalar-value function.

Full definition of my scalar-value function and test case is below. Everyone is welcome to copy it.

ALTER FUNCTION util.JsonToValueArray
(
    @json       nvarchar(max)
)
RETURNS nvarchar(max)
/*
    Converts a JSON array of objects to array of values from the first
    property of each object in the array.

    EXAMPLE:
        SELECT  util.JsonToValueArray((
            SELECT  sc.name, sc.schema_id, sc.principal_id
            FROM    sys.schemas     sc
            ORDER BY sc.schema_id
            FOR JSON PATH
        ));
*/
AS
BEGIN
    DECLARE @result                 nvarchar(MAX) = NULL
    ;
    IF 1 = ISJSON(@json)
    BEGIN
        SELECT  @result = JSON_QUERY(CONCAT('[', STRING_AGG(jo.item, ','), ']'))
        FROM    OPENJSON(@json, '$')        jd
        OUTER
        APPLY  (
                -- Extract the first property of each object.
                SELECT  TOP 1
                        item    = CASE xjd.type
                                    WHEN 1 THEN CONCAT('"', xjd.value, '"') -- string
                                    WHEN 2 THEN xjd.value -- number
                                    WHEN 3 THEN xjd.value -- bool
                                           ELSE NULL
                                  END
                FROM    OPENJSON(jd.value, '$')    xjd
               )                            jo
        WHERE   jd.type = 5
        ;
    END
    ;
    RETURN @result;
END
GO
----------------------------------------------------------------------------------------------------
IF 1=1
BEGIN;
    SELECT  JsonToValueArrayTest
                = CASE WHEN r.expectedJson IS NULL AND r.actualJson IS NULL THEN 'VALID'
                       WHEN r.expectedJson IS NULL                          THEN 'INVALID NOT NULL'
                       WHEN r.actualJson IS NULL                            THEN 'INVALID NULL'
                       WHEN r.expectedJson = r.actualJson                   THEN 'VALID'
                                                                            ELSE 'INVALID'
                  END
    ,       r.*
    FROM   (
            SELECT  t.testJSON, t.expectedJSON
            ,       actualJSON = util.JsonToValueArray(t.testJSON)
            FROM   (
                    SELECT  testJSON = (
                                SELECT  sc.name, sc.schema_id, sc.principal_id
                                FROM    sys.schemas     sc
                                ORDER BY sc.schema_id
                                FOR JSON PATH
                            )
                    ,       expectedJSON = CONVERT(nvarchar(max), (
                                SELECT  names = CONCAT( '['
                                                      , STRING_AGG(CONVERT(nvarchar(max), CONCAT('"', sc.name, '"')), ',')
                                                            WITHIN GROUP (ORDER BY sc.schema_id)
                                                      , ']')
                                FROM    sys.schemas     sc
                            ))
                   )                        t
            UNION ALL
            SELECT  t.testJSON, t.expectedJSON
            ,       actualJSON = util.JsonToValueArray(t.testJSON)
            FROM   (
                    SELECT  testJSON = (
                                SELECT  db.name, db.database_id, db.create_date, db.compatibility_level
                                FROM    master.sys.databases        db
                                WHERE   db.owner_sid = 0x01
                                ORDER BY db.name
                                FOR JSON PATH
                            )
                    ,       expectedJSON = CONVERT(nvarchar(max), (
                                SELECT  names = CONCAT( '['
                                                      , STRING_AGG(CONVERT(nvarchar(max), CONCAT('"', db.name, '"')), ',')
                                                            WITHIN GROUP (ORDER BY db.name)
                                                      , ']')
                                FROM    master.sys.databases        db
                                WHERE   db.owner_sid = 0x01
                            ))
                   )                        t
           )                r
    ;
END
GO

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

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