Postgres中的嵌套JSON聚合

Question

我需要在Postgres数据库上运行一个查询并使用本地的Postgres工具对其进行聚合，然后将其导出为json对象。

我无法正确地实现聚合，并感到有些困惑。

以下是一些数据示例：

| msgserial |  object_type  | payload_key |                          payload                          | user_id |
+-----------+---------------+-------------+-----------------------------------------------------------+---------+
|   1696962 | CampaignEmail | a8901b2c    | {"id": "ff7221da", "brand": "MAGIC", "eventType": "SENT"} |     001 |
|   1696963 | OtherType     | b8901b2c    | {"id": "ff7221db", "brand": "MAGIC", "eventType": "SENT"} |     001 |
|   1696964 | OtherType     | c8901b2c    | {"id": "ff7221dc", "brand": "MAGIC", "eventType": "SENT"} |     002 |
|   1696965 | OtherType     | d8901b2c    | {"id": "ff7221dd", "brand": "MAGIC", "eventType": "SENT"} |     001 |
|   1696966 | CampaignEmail | e8901b2c    | {"id": "ff7221de", "brand": "MAGIC", "eventType": "SENT"} |     001 |
|   1696967 | CampaignEmail | f8901b2c    | {"id": "ff7221df", "brand": "MAGIC", "eventType": "SENT"} |     002 |
|   1696968 | SomethingElse | g8901b2c    | {"id": "ff7221dg", "brand": "MAGIC", "eventType": "SENT"} |     001 |
+-----------+---------------+-------------+-----------------------------------------------------------+---------+

我需要按照user_id分组输出一个像这样的JSON对象

{
  "user_id": 001,
  "brand": "MAGIC",
  "campaignEmails": [
    {"id": "ff7221da", "brand": "MAGIC", "eventType": "SENT"},
    {"id": "ff7221de", "brand": "MAGIC", "eventType": "SENT"},
    {"id": "ff7221de", "brand": "MAGIC", "eventType": "SENT"}
  ],
  "OtherTypes": [
    {"id": "ff7221db", "brand": "MAGIC", "eventType": "SENT"},
    {"id": "ff7221dd", "brand": "MAGIC", "eventType": "SENT"}
  ],
  "Somethingelses": [
    {"id": "ff7221dg", "brand": "MAGIC", "eventType": "SENT"}
  ]
},
{
  "user_id": 002,
  "campaignEmails": [
  ],
  "OtherTypes": [

  ],
  "Somethingelses": [
  ]
}

基本上需要按照它们的类型将所有有效载荷分组到由user_id分组的数组中

我开始使用JSONB_BUILD_OBJECT，将其中一个object_type分组到数组中，但随后遇到了麻烦。

我在纯PSQL中试图实现不可能的事情吗？我真的很困惑，并且一直会出现类似“X需要包含在GROUP BY子句中”等错误...

我可以将其中一个object_type按user_id分组到一个数组中，但似乎不能做到全部3个。

我的其他想法是进行3个子查询，但我也不确定如何做到。

- Scott Hooker

Answer 1

您需要两个聚合，第一个按user_id, object_type分组，第二个仅按user_id分组：

select 
    jsonb_build_object('user_id', user_id) 
    || jsonb_object_agg(object_type, payload) as result
from (
    select user_id, object_type, jsonb_agg(payload) as payload
    from my_table
    group by user_id, object_type
    ) s
group by user_id

Db<>Fiddle.

- klin