SQL - generate JSON array without column names

Question

Let's say we have these data

CREATE TABLE [dbo].[tValues]
(
    [cValue] [VARCHAR](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[tValues] ([cValue]) 
VALUES ('red'), ('green'), ('blue'), ('brown')

From this query:

SELECT 
    (JSON_QUERY((SELECT 
                     'Ball' AS title, 
                     '20cm' AS size,
                     (SELECT cValue FROM tValues FOR JSON PATH) AS [colors]
                 FOR JSON PATH))) AS product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

I got this JSON result:

{
    "product": [
                 { "title":"Ball", 
                   "size":"20cm", 
                   "colors": [
                                { "cValue": "red" },
                                { "cValue": "green" },
                                { "cValue": "blue" },
                                { "cValue": "brown" }
                             ]
                 }
               ]
}

But I need without column names and curly brackets in colors tag like this:

{
    "product": [
                  {
                      "title": "Ball",
                      "size": "20cm",
                      "colors": [ "red", "green", "blue", "brown" ]
                                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                  }
               ]
}

How can I do that?

Answer 1

One possible solution is to use for xml path with stuff to build your array:

SELECT 
    (JSON_QUERY((SELECT 
                     'Ball' AS title, 
                     '20cm' AS size,
                      JSON_QUERY(
                    '[' + STUFF(( SELECT ',' + '"' + cValue + '"' 
                    FROM tValues 
                    FOR XML PATH('')),1,1,'') + ']' ) AS [colors]
                 FOR JSON PATH)

                 )) AS product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{
   "product":[
      {
         "title":"Ball",
         "size":"20cm",
         "colors":[
            "red",
            "green",
            "blue",
            "brown"
         ]
      }
   ]
}