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?

Starting with SQL-Server 2017 there is STRING_AGG(), but really, this should've been included into the FOR JSON syntax... — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 4, 2019 at 8:50

Andrea · Accepted Answer · 2019-11-01 18:20:25Z

3

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

answered Nov 1, 2019 at 18:20

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ARadzh · Accepted Answer · 2022-09-21 08:48:44Z

2

My solution is in a working application for SQL Server 2016

JSON_QUERY('[' + STRING_AGG(tValues.color), ',') + ']') 'colors'

And will JSON_ARRAY in SQL Server 2022

answered Sep 21, 2022 at 8:48

ARadzh

1482 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL - generate JSON array without column names

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related