CODEWITHSUNDEEP
jsonsql-servert-sqlstring-escape
Alex Isar
Alex Isar

Reputation: 57

SQL to JSON - Create a Json Array

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

Upvotes: 4

Views: 4080

Answers (1)

gotqn
gotqn

Reputation: 43636

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.

Upvotes: 2

Related Questions