如何在SQL中生成带有自定义键名的对象数组？

你当前用row()构造记录再通过json_agg聚合时，生成的JSON对象会默认用f1、f2作为键名——这是因为row()创建的是匿名行记录，没有自定义字段名。要得到指定键名的JSON数组，有两种简单的解决办法：

方法一：用json_build_object构造自定义JSON对象 #

直接通过json_build_object指定每个键值对，再聚合：

SELECT 
  s.id,
  s.name,
  s.image,
  s.verified,
  s.s_position,
  s.status,
  s.platform,
  s.coupon_name,
  s.coupon_pct,
  s.coupon_start,
  s.coupon_end,
  -- 用json_build_object指定键名和对应字段
  json_agg(json_build_object('category', c.category, 'sub_category', scg.sub_category)) as categories
-- 保留原有的FROM、JOIN、WHERE等逻辑
....

方法二：给匿名行记录指定字段名 #

如果偏好使用row()，可以显式给每个字段命名，再转成JSON：

SELECT 
  s.id,
  s.name,
  s.image,
  s.verified,
  s.s_position,
  s.status,
  s.platform,
  s.coupon_name,
  s.coupon_pct,
  s.coupon_start,
  s.coupon_end,
  -- 给row的字段命名后转成JSON再聚合
  json_agg(to_json(row(c.category AS category, scg.sub_category AS sub_category))) as categories
-- 保留原有的FROM、JOIN、WHERE等逻辑
....

额外处理：去重重复的分类对象 #

如果结果里出现重复的分类条目（比如你示例里的两个相同对象），可以在json_agg里加上DISTINCT来去重：

json_agg(DISTINCT json_build_object('category', c.category, 'sub_category', scg.sub_category)) as categories

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