Return Child Rows as a JSON Array for Every Parent

A very practical interview-style question. Take this self-referencing table:

CREATE TABLE dummy (
    id INT,
    parent_id INT NULL
);

INSERT INTO dummy (id, parent_id) VALUES
    (1, NULL),
    (2, 1),
    (3, 1),
    (4, 2),
    (5, 4),
    (6, 3);

 1
 |-- 2
 |    +-- 4
 |         +-- 5
 +-- 3
      +-- 6

• 1 is a root node
• 2 and 3 are children of 1
• 4 is a child of 2
• 5 is a child of 4
• 6 is a child of 3

Problem: return the child ids grouped by parent_id as a JSON array.

CREATE TABLE dummy (id INTEGER, parent_id INTEGER);
INSERT INTO dummy VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 4), (6, 3);

SELECT
    parent_id,
    JSON_ARRAYAGG(id) AS arrayagg
FROM dummy
GROUP BY parent_id;

Output

Explanation Grouping by parent_id collects all rows that share the same parent. Then JSON_ARRAYAGG(id) collects their id values into one JSON array. Parent 1 gets [2,3], parent 2 gets [4], and so on. The NULL row is the group of root nodes (their parent is NULL). This is one of the cleanest ways to return child rows per parent directly from SQL.