SQL-CTE递归查询子ID

场景1: 查询某个节点的所有子孙id

idparent_idname
1NULLRoot
21Child_A
32Child_B
父子id结构
WITH RECURSIVE descendants AS (
    -- 初始条件
    SELECT id, parent_id, name
    FROM my_table
    WHERE id = 1
    UNION ALL
    -- 递归条件
    SELECT t.id, t.parent_id, t.name
    FROM my_table t
    JOIN descendants d ON t.parent_id = d.id
)
SELECT id, name FROM descendants WHERE id <> 1;

场景2: 查询某个节点的所有子孙id,构建children数组结构JSON

用sql来说似乎没法实现