如何用PostgreSQL JSON函数将表数据按gid聚合为嵌套JSON对象

当然可以！你只需要调整聚合逻辑，利用PostgreSQL的json_object_agg函数就能轻松实现你想要的键值对结构。下面是具体的解决方案：

最终SQL查询 #

SELECT json_object_agg(gid, messages) AS chat_by_game
FROM (
  SELECT 
    gid,
    ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(msg_data))) AS messages
  FROM (
    -- 先处理原始数据，转换时间戳格式
    SELECT 
      gid,
      uid,
      EXTRACT(EPOCH FROM created)::int AS created,
      msg
    FROM chat
  ) raw_data
  -- 生成不含gid的记录，避免后续JSON里重复出现gid
  , LATERAL (SELECT uid, created, msg) msg_data
  -- 按游戏ID分组聚合
  GROUP BY gid
) grouped_data;

逻辑解释 #

1. 内层数据处理：最里层的子查询先把created字段转换成Unix时间戳整数，同时取出所有需要的字段。
2. 剥离gid字段：通过LATERAL子查询生成仅包含uid、created、msg的记录，确保后续生成的JSON对象里不会带有gid。
3. 分组聚合消息：按gid分组，用ARRAY_AGG(ROW_TO_JSON(msg_data))把每个游戏下的所有聊天记录聚合成一个JSON数组。
4. 生成最终JSON对象：最外层用json_object_agg把每个gid作为键，对应的消息数组作为值，拼接成你需要的嵌套JSON结构。

简化写法（可选） #

如果你觉得LATERAL子查询有点繁琐，也可以用嵌套子查询直接构造不含gid的行：

SELECT json_object_agg(gid, messages) AS chat_by_game
FROM (
  SELECT 
    gid,
    ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON((SELECT t FROM (SELECT uid, created, msg) t)))) AS messages
  FROM (
    SELECT 
      gid,
      uid,
      EXTRACT(EPOCH FROM created)::int AS created,
      msg
    FROM chat
  ) raw_data
  GROUP BY gid
) grouped_data;

输出结果 #

执行以上查询后，你会得到完全符合预期的JSON结构：

{
  "10": [
    {"uid":1,"created":1514813043,"msg":"msg 1"},
    {"uid":2,"created":1514813103,"msg":"msg 2"},
    {"uid":1,"created":1514813163,"msg":"msg 3"},
    {"uid":2,"created":1514813223,"msg":"msg 4"},
    {"uid":1,"created":1514813283,"msg":"msg 5"},
    {"uid":2,"created":1514813343,"msg":"msg 6"}
  ],
  "20": [
    {"uid":3,"created":1514813403,"msg":"msg 7"},
    {"uid":4,"created":1514813463,"msg":"msg 8"},
    {"uid":4,"created":1514813523,"msg":"msg 9"}
  ]
}

内容的提问来源于stack exchange，提问作者Alexander Farber