is it possible to nest an array_agg inside another array_agg

Question

I have a SQL table message(application, type, action, date, ...) and I would like to get all the actions for a type and all the types for an application in a single query if possible.

So far I have managed to get the result in two separate queries like so:

select application, array_agg(distinct type) as types from message group by application;
 application  |                                                           types                                                            
--------------+----------------------------------------------------------------------------------------------------------------------------
 app1          | {company,user}
 app2          | {document,template}
 app3          | {organization,user}

and the second query:

select type, array_agg(distinct action) as actions from message group by type;
            type                      |                 actions                 
--------------------------------------+-----------------------------------------
 company                              | {created,updated}
 document                             | {created,tested,approved}
 organization                         | {updated}
 template                             | {deleted}
 user                                 | {created,logged,updated}

The most obvious single query I could come up with so far is just:

select application, type, array_agg(distinct action) from message group by application, type;

Which would require some programmatic processing to build the type array.

What I wanted to do was something theoretically like: select application, array_agg(type, array_agg(action)) from message group by application, type which isn't possible as is but I feel there is a way to do it. I have also thought about nesting the second query into the first one but haven't found how to make it work yet.

Arrays don't seem to be the right type here; what about json? — Laurenz Albe
– Laurenz Albe, Commented Aug 12, 2019 at 15:19

S-Man · Accepted Answer · 2019-08-12 15:57:36Z

demo:db<>fiddle

You can create tuples (records): (col1, col2). So if col2 is of type array, you created (text, text[]). These tuples can be aggregated as well into array of tuples:

SELECT
    app,
    array_agg((type, actions))  -- here is the magic
FROM (
    SELECT
        app,
        type,
        array_agg(actions) actions
    FROM
        message
    GROUP BY app, type
) s
GROUP BY app

To get access, you have to explicitely define the record type at unnesting:

SELECT
    *
FROM (
    -- your query with tuples
)s,
unnest(types) AS t(type text, actions text[]) -- unnesting the tuple array

Nevertheless, as stated in the comments, maybe JSON may be a better approach for you:

demo:db<>fiddle

SELECT
    app,
    json_agg(json_build_object('type', type, 'actions', actions))
FROM (
    SELECT
        app,
        type,
        json_agg(actions) actions
    FROM
        message
    GROUP BY app, type
) s
GROUP BY app

Result:

[{
    "type": "company",
    "actions": ["created","updated"]
},
{
    "type": "user",
    "actions": ["logged","updated"]
}]

Another possible JSON output:

demo:db<>fiddle

SELECT 
    json_agg(data)
FROM (
    SELECT
        json_build_object(app, json_agg(types)) as data
    FROM (
        SELECT
            app,
            json_build_object(type, json_agg(actions)) AS types
        FROM
            message
        GROUP BY app, type
    ) s
    GROUP BY app
) s

Result:

[{
    "app1": [{
        "company": ["created","updated"]
    },
    {
        "user": ["logged","updated"]
    }]
},
{
    "app2": [{
        "company": ["created"]
    }]
}]

Totally what I was looking for. Json is indeed more suited to the problem. Thanks

Collectives™ on Stack Overflow

is it possible to nest an array_agg inside another array_agg

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related