Athena/Presto: SQL for producing array of nested maps/structs from flat rows

Question

Suppose I have data like this:

with users (user_id, name) as (
    values (1, 'Alice'),
           (2, 'Bob'),
           (3, 'Charlie')
), todos (todo_id, user_id, title) as (
    values (1, 1, 'todo 1'),
           (2, 1, 'todo 2'),
           (3, 2, 'todo 3'),
           (4, 3, 'todo 4')
)

And I'd like to produce JSON from a query like the example below:

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

Is there any way to do this natively in Athena/Presto?

I have gotten somewhat close thanks to answers I found from @gurustrom

• It seems fairly easy to make nested object that ONLY have 1 property
• You can use ARRAY[] or map_from_entries() to create struct-like types, but the values must all be the same, which means I don't understand how to create a mixed-value object like:
  • { "user_id": 1, "name": "Foo", "todos": [{}] }

The following SQL is somewhat on track, but produces the wrong output shape and the combinations I've tried all fail:

[{"2":{"todos":[{"id":3}]}},{"1":{"todos":[{"id":1},{"id":2}]}},{"3":{"todos":[{"id":4}]}}]

select
    cast(array_agg(res) as JSON) as result
    from
    (
        select map_agg(user_id, m) as res
        from
            (
                select
                    user_id,
                    map_agg('todos', todos) as m
                from
                    (
                        select
                            user_id,
                            array_agg(todo) as todos
                        from
                            (
                                select
                                    user_id,
                                    map_agg('id', todo_id) as todo
                                from
                                    todos
                                group by
                                    user_id,
                                    todo_id
                            ) t
                        group by
                            user_id
                    ) t
                group by
                    user_id
            ) t
        group by
            user_id
    ) t
group by
    true;

Answer 1

You can leverage map functions and some casting to json:

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

select cast(
               map(array['user_id', 'name', 'todos'], array[user_id, name, cast(todos as json)])
           as json)
from preprocess;

Output:

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