如何通过SQL将文档表的图片ID整数数组转换为对应图片详情对象数组

直接运行以下SQL即可得到目标结构的查询结果：

SELECT
  d.id,
  d.name,
  json_agg(
    json_build_object(
      'name', i.name,
      'size', i.size
    ) ORDER BY array_position(d.images, i.id)
  ) AS imagedetails
FROM documents d
LEFT JOIN images i ON i.id = ANY(d.images)
GROUP BY d.id, d.name;

逻辑拆解 #

• 关联匹配：直接用ANY(d.images)匹配文档图片数组中的所有ID，不需要额外通过WITH（CTE语法）拆分数组步骤，写法更精简
• 结构构造：json_build_object会为每条匹配的图片记录生成包含name、size键的JSON对象，外层套json_agg就会把同一文档下的所有图片对象收集为JSON数组，完全匹配期望的返回格式
• 顺序对齐：通过array_position(d.images, i.id)做排序，能保证最终返回的图片数组顺序，和文档表中images字段存储的ID顺序完全一致，不会因为关联查询的返回顺序出现错乱；如果需要按图片ID排序，只需将排序条件替换为i.id即可

常见场景适配 #

• 如果文档没有关联任何图片，默认json_agg会返回包含null的数组，可通过COALESCE调整为空数组：

  COALESCE(
    json_agg(
      json_build_object('name', i.name, 'size', i.size) ORDER BY array_position(d.images, i.id)
    ),
    '[]'::json
  ) AS imagedetails
  

• 对图片顺序没有要求的场景，可以去掉聚合内的ORDER BY子句，减少不必要的计算开销

如果你习惯用CTE（WITH语法）实现，等价逻辑是先将文档的图片ID数组拆为临时表结构，再和图片表关联聚合，最终结果和上述SQL完全一致，只是写法更冗长。

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