Trino/Presto/Athena多层嵌套JSON查询关联子查询失败求助

需要实现向客户端返回任意嵌套结构的JSON，当前已完成3层嵌套（users -> todo_lists -> todos）的查询，可在Trino/Presto和Athena正常运行，但扩展到4层嵌套（users -> todo_lists -> todos -> todo_items）时，所有引擎均执行失败，现寻求替代方案。

可行的3层嵌套实现 #

以下是3层嵌套的工作示例，包含测试数据和查询语句：

-- sample data
with users (user_id, name) as (values (1, 'Alice'),
                                  (2, 'Bob'),
                                  (3, 'Charlie')),
 todo_lists (todo_list_id, user_id, title) as (values (1, 1, 'todo list 1'),
                                                      (2, 1, 'todo list 2'),
                                                      (3, 2, 'todo list 3'),
                                                      (4, 3, 'todo list 4')),
 todos (todo_id, todo_list_id, title) as (values (1, 1, 'todo 1'),
                                                 (2, 1, 'todo 2'),
                                                 (3, 2, 'todo 3'),
                                                 (4, 3, 'todo 4'))
-- query
select *
from (select cast(array_agg(
    map(array['user_id', 'name', 'todo_lists'],
        array[user_id, name, cast(todo_lists as json) ]))
             as json)
  from (select cast(u.user_id as json)   user_id,
               cast(max(u.name) as json) name,
               cast(array_agg(
                       map(array['todo_list_id', 'title', 'todos'],
                           array[cast(tl.todo_list_id as json),
                           cast(tl.title as json),
                           cast(
                                   (select array_agg(
                                                   map(array['todo_id', 'title'],
                                                       array[cast(t.todo_id as json),
                                                       cast(t.title as json) ]))
                                    from todos t
                                    where t.todo_list_id = tl.todo_list_id)
                               as json) ]))
                   as json)              todo_lists
        from users u
                 join todo_lists tl on tl.user_id = u.user_id
        group by u.user_id) t) t;

执行结果：

[{"name":"Alice","todo_lists":[{"title":"todo list 2","todo_list_id":2,"todos":[{"title":"todo 3","todo_id":3}]},{"title":"todo list 1","todo_list_id":1,"todos":[{"title":"todo 1","todo_id":1},{"title":"todo 2","todo_id":2}]}],"user_id":1},{"name":"Charlie","todo_lists":[{"title":"todo list 4","todo_list_id":4,"todos":[null]}],"user_id":3},{"name":"Bob","todo_lists":[{"title":"todo list 3","todo_list_id":3,"todos":[{"title":"todo 4","todo_id":4}]}],"user_id":2}]

4层嵌套的失败尝试 #

尝试添加第4层todo_items时，Trino v371、Athena v2（Presto v0.217）均报错，示例代码如下：

-- sample data
with users (user_id, name) as (values (1, 'Alice'),
                                  (2, 'Bob'),
                                  (3, 'Charlie')),
 todo_lists (todo_list_id, user_id, title) as (values (1, 1, 'todo list 1'),
                                                      (2, 1, 'todo list 2'),
                                                      (3, 2, 'todo list 3'),
                                                      (4, 3, 'todo list 4')),
 todos (todo_id, todo_list_id, title) as (values (1, 1, 'todo 1'),
                                                 (2, 1, 'todo 2'),
                                                 (3, 2, 'todo 3'),
                                                 (4, 3, 'todo 4')),
 todo_items (todo_item_id, todo_id, title) as (values (1, 1, 'todo item 1'),
                                                      (2, 1, 'todo item 2'),
                                                      (3, 2, 'todo item 3'),
                                                      (4, 2, 'todo item 4'),
                                                      (5, 3, 'todo item 5'),
                                                      (6, 3, 'todo item 6'),
                                                      (7, 4, 'todo item 7'),
                                                      (8, 4, 'todo item 8'))
-- query
select cast(array_agg(
    map(array['user_id', 'name', 'todo_lists'],
        array[user_id, name, cast(todo_lists as json) ]))
       as json)
from (select cast(user_id as json)   user_id,
         cast(name as json) name,
         cast(todo_lists as json)   todo_lists
    from (select cast(u.user_id as json)   user_id,
                 cast(max(u.name) as json) name,
                 cast(array_agg(
                         map(array['todo_list_id', 'title', 'todos'],
                             array[cast(tl.todo_list_id as json),
                             cast(tl.title as json),
                             cast(
                                     (select array_agg(
                                                     map(array['todo_id', 'title', 'todo_items'],
                                                         array[cast(t.todo_id as json),
                                                         cast(t.title as json),
                                                         cast(
                                                                 (select array_agg(
                                                                                 map(array['todo_item_id', 'title'],
                                                                                     array[cast(ti.todo_item_id as json),
                                                                                     cast(ti.title as json) ]))
                                                                  from todo_items ti
                                                                  where ti.todo_id = t.todo_id)
                                                             as json) ]))
                                      from todos t
                                      where t.todo_list_id = tl.todo_list_id)
                                 as json) ]))
                     as json)              todo_lists
          from users u
                   join todo_lists tl on tl.user_id = u.user_id
          group by u.user_id) t
    ) t;

替代方案：逐层JOIN聚合替代嵌套子查询 #

深层嵌套子查询会导致引擎解析或执行出错，可改为从最底层开始，逐层向上JOIN并聚合，避免嵌套子查询的问题。以下是4层嵌套的可行实现：

with users (user_id, name) as (values (1, 'Alice'),
                                  (2, 'Bob'),
                                  (3, 'Charlie')),
 todo_lists (todo_list_id, user_id, title) as (values (1, 1, 'todo list 1'),
                                                      (2, 1, 'todo list 2'),
                                                      (3, 2, 'todo list 3'),
                                                      (4, 3, 'todo list 4')),
 todos (todo_id, todo_list_id, title) as (values (1, 1, 'todo 1'),
                                                 (2, 1, 'todo 2'),
                                                 (3, 2, 'todo 3'),
                                                 (4, 3, 'todo 4')),
 todo_items (todo_item_id, todo_id, title) as (values (1, 1, 'todo item 1'),
                                                      (2, 1, 'todo item 2'),
                                                      (3, 2, 'todo item 3'),
                                                      (4, 2, 'todo item 4'),
                                                      (5, 3, 'todo item 5'),
                                                      (6, 3, 'todo item 6'),
                                                      (7, 4, 'todo item 7'),
                                                      (8, 4, 'todo item 8')),
-- 1. 聚合todo_items到每个todo
todo_with_items as (
    select 
        t.todo_id,
        t.title as todo_title,
        t.todo_list_id,
        cast(array_agg(map(array['todo_item_id', 'title'], 
                           array[cast(ti.todo_item_id as json), cast(ti.title as json)])) as json) as todo_items
    from todos t
    left join todo_items ti on ti.todo_id = t.todo_id
    group by t.todo_id, t.title, t.todo_list_id
),
-- 2. 聚合todos到每个todo_list
list_with_todos as (
    select 
        tl.todo_list_id,
        tl.title as list_title,
        tl.user_id,
        cast(array_agg(map(array['todo_id', 'title', 'todo_items'], 
                           array[cast(twi.todo_id as json), cast(twi.todo_title as json), twi.todo_items])) as json) as todos
    from todo_lists tl
    left join todo_with_items twi on twi.todo_list_id = tl.todo_list_id
    group by tl.todo_list_id, tl.title, tl.user_id
),
-- 3. 聚合todo_lists到每个user
user_with_lists as (
    select 
        u.user_id,
        u.name,
        cast(array_agg(map(array['todo_list_id', 'title', 'todos'], 
                           array[cast(lwt.todo_list_id as json), cast(lwt.list_title as json), lwt.todos])) as json) as todo_lists
    from users u
    left join list_with_todos lwt on lwt.user_id = u.user_id
    group by u.user_id, u.name
)
-- 4. 最终聚合所有用户为JSON数组
select cast(array_agg(map(array['user_id', 'name', 'todo_lists'], 
                          array[cast(user_id as json), cast(name as json), todo_lists])) as json)
from user_with_lists;

这个方案通过分层CTE逐步构建嵌套结构，每一层只处理当前层级的聚合，避免了深层嵌套子查询，可在Trino和Athena上正常执行，返回符合要求的4层嵌套JSON。

内容的提问来源于stack exchange，提问作者Gavin Ray