大查询表中的嵌套字段、数组和层次结构

表预览:所有字段都是可空字符串

e、 g.url 111111 2和111111 3是彼此的“兄弟”和url 111111 6的“孩子”

我想为每个元素 modemio_url_id号 一个数组 兄弟URL 和

为了得到所有 儿童 我试过这样的东西

SELECT
  url5_id as url_id,
  ARRAY_AGG(DISTINCT url6_id IGNORE NULLS) children
FROM  `xxx.modemio_url_schema`
GROUP BY url5_id
UNION ALL
SELECT
  url4_id as url_id,
  ARRAY_AGG(DISTINCT url5_id IGNORE NULLS) children
FROM `xxx.modemio_url_schema`
UNION ALL 
[...]
GROUP BY url4_id 
HAVING url_id is not null

兄弟 儿童

预期成果