Aggregate results in one row

Question

I have table in SQL Server database with following structure.

DB version: DBMS: Microsoft SQL Server (ver. 12.00.5409)

comments:

id | content | parent_id

And I would like to write a query that returns grouped child comments as a single row in main query.

So output will be like:

id | content | child_comments

1  | ''      | "[{id: 12, content: ""}, {id: 15, content: ""}, {id: 25, content: ""}]"
8  | ''      | "[{id: 22, content: ""}, {id: 44, content: ""}, {id: 65, content: ""}]"

I tried so far to create the following query

SELECT *,
       (SELECT * FROM comments c1 WHERE c.id = c1.parent_id GROUP BY c.parent_id)
FROM comments c

But of course I have the following error:

Only one expression can be specified in the select list

In PostgreSQL I could use one of the available aggregating functions like "ARRAY_AGG". But I see in SQL Server db it's not possible to do it.

And unfortunately I can't modify that table at all.

Is there any way how I can achieve desired output format?

Thank you

Answer 1

Unfortunately SQL Server doesn't support JSON_ARRAYAGG yet, although it is coming at some point.

But you don't need it. In modern versions of SQL Server (newer than your ancient SQL 2014), you can just use FOR JSON in your subquery.

SELECT *,
  (
    SELECT
      child.*
    FROM comments child
    WHERE c.id = child.parent_id
    FOR JSON PATH
  ) AS child_comments
FROM comments c;

In SQL Server 2014 you would have to concatenate and escape everything correctly yourself, as there is no native support for JSON.

Something like this maybe. Use CONCAT to get each row, the FOR XML trick to concatenate multiple rows, use .value to unescape the XML, and use STUFF to strip off the first comma.

SELECT *,
  '[' + STUFF((
    SELECT
      CONCAT(
        ',{"id":',
        child.id,
        ',"content":"',
        REPLACE(child.content, '"', '\"'),
        '"}'
      )
    FROM comments child
    WHERE c.id = child.parent_id
    FOR XML PATH(''), TYPE
  ).value('text()[1]','nvarchar(max)'), 1, 1, '') + ']' AS child_comments
FROM comments c;