SQL to JSON - Create a Json Array

Question

I have the below data:

CREATE TABLE mytable
 (
  ID int,
  Product nvarchar(50),
  Usage nvarchar(255),
 );

INSERT INTO mytable VALUES (99346,'Universal light','Art and Culture');
INSERT INTO mytable VALUES (99346,'Universal light','Health and Care');
INSERT INTO mytable VALUES (99346,'Universal light','Hotel and Wellness');
INSERT INTO mytable VALUES (99346,'Universal light','Education and Science');

And I have created the following code to get my JSON output:

SELECT DISTINCT T1.ID
      ,T1.Product
      ,(SELECT T2.Usage
        FROM mytable T2
        WHERE T1.ID=T2.ID
        FOR JSON PATH) as 'Usage'
FROM mytable T1
FOR JSON PATH

It outputs the following results:

[
    {
        "ID": 99346,
        "Product": "Universal light",
        "Usage": [
            {
                "Usage": "Art and Culture"
            },
            {
                "Usage": "Health and Care"
            },
            {
                "Usage": "Hotel and Wellness"
            },
            {
                "Usage": "Education and Science"
            }
        ]
    }
]

I would like to have the results as below, but can't figure out how to change the syntax:

[
    {
        "ID": 99346,
        "Product": "Universal light",
        "Usage": [ "Art and Culture" , "Health and Care" , "Hotel and Wellness" , "Education and Science"
         ]
    }
]

Any help on this much appreciated.

EDIT

If I use this initial data, where at the end of line 3 I have an extra ' ' the solution does not work, no error or warning:

INSERT INTO mytable VALUES (99346,'Universal light','Art and Culture');
INSERT INTO mytable VALUES (99346,'Universal light','Education and Science');
INSERT INTO mytable VALUES (99346,'Universal light','Health and Care ');
INSERT INTO mytable VALUES (99346,'Universal light','Hotel and Wellness');
INSERT INTO mytable VALUES (99346,'Universal light','Offices and Communication');
INSERT INTO mytable VALUES (99346,'Universal light','Presentation and Retail');

I have tried to use TRIM, as you can see below:

SELECT Distinct ID
      ,Product
      ,json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(TRIM(Usage), 'json') + '"', char(44)))) AS 'Usage'
FROM mytable
GROUP BY ID
      ,Product
FOR JSON PATH;

Unfortunately it does not work and the whole array 'Usage' is somehow ignored, see results:

[
    {
        "ID": 99346,
        "Product": "Universal light"
    }
]

gotqn · Accepted Answer · 2020-04-30 08:16:34Z

2

You can use STRING_AGG to build the array like this:

SELECT  DISTINCT ID
      ,Product
      ,json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(Usage, 'json') + '"', char(44)))) AS 'Usage'
FROM mytable T1
GROUP BY ID
      ,Product
FOR JSON PATH;

If you are not using SQL Sever 2017 or later, you can use concatenate the values using XML PATH.

SELECT DISTINCT T1.ID
      ,T1.Product
      ,
       (
         '[' +
          STUFF
          (
                (
                SELECT ',' + '"' + T2.Usage + '"' 
                FROM mytable T2
                WHERE T1.ID=T2.ID
                FOR XML PATH, TYPE
                ).value('.', 'NVARCHAR(MAX)')
                ,1
                ,1
                ,''
            )
          + ']'
       ) as 'Usage'
FROM mytable T1
FOR JSON PATH

For your edit use:

SELECT Distinct ID
      ,Product
      ,json_query('[' + (STRING_AGG('"' + STRING_ESCAPE(TRIM(Usage), 'json') + '"', char(44))) + ']') AS 'Usage'
FROM mytable
GROUP BY ID
      ,Product
FOR JSON PATH;

The issue is QUOTENAME input is limited to 128 chars and returns NULL when you add more records.

edited Apr 30, 2020 at 8:16

answered Apr 29, 2020 at 9:15

gotqn

44k47 gold badges167 silver badges256 bronze badges

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

5 Comments

Alex Isar Over a year ago

Many thanks! Strange there is no 'native' easier way to do this. Anyway, solves the problem. Have a great day ahead!

Alex Isar Over a year ago

Hi @gotqn, could you please have a look at my Edit to the problem, using a different dataset does not generate any results. And I can't figure out why :( PS. it has nothing to do with the extra blank space at the end.

gotqn Over a year ago

@AlexIsar Are you using the first or the second technique?

Alex Isar Over a year ago

Hi @gotqn I use the 1st option.

Alex Isar Over a year ago

Many thanks, works perfectly. Had no idea QUOTENAME was limited to 128. Cheers!

Collectives™ on Stack Overflow

SQL to JSON - Create a Json Array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related