如何从多表高效获取单个实体数据，避免重复与性能问题？

我有多个存储同一逻辑实体（例如帖子）数据的表，应用需要获取多个帖子的关联数据（如评论、反应），这些数据分散在不同表中。我担心两个问题：下载耗时、解析难度。

表结构示例 #

-- 帖子表
posts (
  id int,
  content text
)

-- 评论表
comments (
  id int,
  post_id int,
  content text
)

-- 反应表
reactions (
  post_id int,
  emoji char,
  count int
)

尝试过的方案及问题 #

• 简单JOIN查询：

  select * from 
  posts as p 
  inner join comments as c on c.post_id = p.id
  inner join reactions as r on r.post_id = p.id;
  

  问题：返回行数为「反应数×评论数」，数据量极易失控，且帖子内容重复下载，后续新增关联表时问题会更严重，应用端解析也很混乱。
• 多轮查询：先获取帖子列表，再逐个获取每个帖子的评论和反应。
  问题：数据库往返次数过多，新增层级关联数据时情况会恶化。
• 应用端自行关联：单次查询获取各表数据后，在应用层手动关联。
  问题：浪费了关系型数据库的关联优势。
• 压缩为JSON列返回：将评论、反应压缩为单个JSON数组列，返回单条帖子数据。
  问题：操作繁琐，不确定可行性。

期望输出格式 #

应用需要获取帖子1和2的完整关联数据，期望得到如下JSON结构：

{
  "posts": [
    {
      "content": "A post",
      "comments": [
        {"content": "Great post"},
        {"content": "Yeah it is"}
      ],
      "reactions": [
        {"emoji": "👋", "count": 12},
        {"emoji": "🍎", "count": 1}
      ]
    },
    {
      "content": "A second post",
      "comments": [
        {"content": "lol"}
      ],
      "reactions": [
        {"emoji": "🍎", "count": 10}
      ]
    }
  ]
}

注：我使用PostgreSQL，但数据库类型影响不大。

针对你的需求，PostgreSQL提供的JSON聚合函数是最优解，既能避免JOIN导致的数据膨胀，又能减少数据库往返次数，同时直接返回应用需要的结构化JSON，无需复杂解析。

实现SQL #

SELECT json_build_object(
  'posts', json_agg(
    json_build_object(
      'content', p.content,
      'comments', COALESCE(c.comments_arr, '[]'::json),
      'reactions', COALESCE(r.reactions_arr, '[]'::json)
    )
  )
) AS result
FROM posts p
LEFT JOIN (
  SELECT post_id, json_agg(json_build_object('content', content)) AS comments_arr
  FROM comments
  GROUP BY post_id
) c ON c.post_id = p.id
LEFT JOIN (
  SELECT post_id, json_agg(json_build_object('emoji', emoji, 'count', count)) AS reactions_arr
  FROM reactions
  GROUP BY post_id
) r ON r.post_id = p.id
WHERE p.id IN (1, 2); -- 指定要获取的帖子ID

方案优势 #

1. 避免数据膨胀：通过子查询先对评论、反应按post_id聚合为JSON数组，再和帖子表关联，不会出现「反应数×评论数」的冗余数据，帖子内容仅返回一次。
2. 减少往返次数：单次查询即可获取所有需要的结构化数据，无需多轮请求。
3. 解析成本低：直接返回应用需要的JSON格式，无需在应用层做复杂的关联或解析操作。
4. 扩展性强：后续新增关联表（比如点赞、收藏）时，只需在主查询中新增一个LEFT JOIN的聚合子查询，添加对应的JSON字段即可。

补充说明 #

• 使用LEFT JOIN而非INNER JOIN是为了确保即使帖子没有评论或反应，也能返回空数组而非过滤掉该帖子。
• COALESCE函数用于处理空值，当帖子没有评论/反应时，将对应字段设为空JSON数组[]，保证输出结构统一。

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