sql - 是否可以将 array_agg 嵌套在另一个 array_agg 中

Question

我有一个 SQL 表 message(application, type, action, date, ...) 我想获取一个类型的所有操作和一个应用程序的所有类型如果可能，单个查询。

到目前为止，我已经设法在两个单独的查询中获得结果，如下所示:

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

第二个查询:

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}

到目前为止，我能想到的最明显的单一查询是:

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

这需要一些编程处理来构建 type 数组。

我想做的在理论上是这样的:select application, array_agg(type, array_agg(action)) from message group by application, type 这是不可能的，但我觉得有办法做到这一点。我还考虑过将第二个查询嵌套到第一个查询中，但还没有找到如何使其工作的方法。

Answer 1

demo:db<>fiddle

您可以创建元组(记录):(col1, col2)。因此，如果 col2 是数组类型，则您创建了 (text, text[])。这些元组也可以聚合成元组数组:

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

要获得访问权限，您必须在取消嵌套时明确定义记录类型:

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

---

然而，如评论中所述，JSON 可能对您来说是更好的方法:

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

结果:

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

---

另一种可能的 JSON 输出:

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

结果:

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