title: SQL to JSON - Create a Json Array
body: <p>I have the below data:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>And I have created the following code to get my JSON output:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>It outputs the following results:</p>
  
  <pre class="lang-json prettyprint-override"><code>[
      {
          "ID": 99346,
          "Product": "Universal light",
          "Usage": [
              {
                  "Usage": "Art and Culture"
              },
              {
                  "Usage": "Health and Care"
              },
              {
                  "Usage": "Hotel and Wellness"
              },
              {
                  "Usage": "Education and Science"
              }
          ]
      }
  ]
  </code></pre>
  
  <p>I would like to have the results as below, but can't figure out how to change the syntax:</p>
  
  <pre><code>[
      {
          "ID": 99346,
          "Product": "Universal light",
          "Usage": [ "Art and Culture" , "Health and Care" , "Hotel and Wellness" , "Education and Science"
           ]
      }
  ]
  </code></pre>
  
  <p>Any help on this much appreciated.</p>
  
  <p>EDIT</p>
  
  <p>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:</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>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');
  </code></pre>
  
  <p>I have tried to use TRIM, as you can see below:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>Unfortunately it does not work and the whole array 'Usage' is somehow ignored, see results:</p>
  
  <pre class="lang-json prettyprint-override"><code>[
      {
          "ID": 99346,
          "Product": "Universal light"
      }
  ]
  </code></pre>