select all row values as a list

Question

I have a table tasks that looks like this:

userId          caption    status       id
1               Paul       done         1
2               Ali        notDone      18
3               Kevin      notDone      12
3               Elisa      notDone      13

I join it with another table users to find the number of taskswhere status = notDone. I do it like this:

    SELECT u.id, 
       t.number_of_tasks,
    FROM users u
    INNER JOIN (
      SELECT userId, COUNT(*) number_of_tasks
      FROM tasks 
      WHERE status = "notDone"
      GROUP BY userId
      ) t ON u.id = t.userId
    """

Now, I want create another column captions that somehow includes a list of all captions that were included in the countand fulfil the join + where conditions.

For example, I would expect this as one of the rows. How can I achieve this?

userId      number_of_tasks     captions
3           2                   ["Kevin", "Elisa"]

Answer 1

You can use json_group_array() aggregate function inside the subquery to create the list of captions for each user:

SELECT u.id, t.number_of_tasks, t.captions
FROM users u 
INNER JOIN (
  SELECT userId, 
         COUNT(*) number_of_tasks,
         json_group_array(caption) captions
  FROM tasks 
  WHERE status = 'notDone'
  GROUP BY userId
) t ON u.id = t.userId;