PostgreSQL 单条SQL查询保留键名的分类-运动嵌套JSON结构
核心原因
- 之前用
ARRAY_AGG生成结果丢失键名的本质原因:ARRAY_AGG生成的是普通SQL数组类型,转JSON时仅按值的顺序映射内容,就算聚合行类型数据,生成的JSON字段名也是系统自动分配的f1/f2这类无意义名称,完全无法自定义业务要求的固定键名,自然满足不了API按指定键取值的需求 - 实现目标结构的核心思路:用PostgreSQL内置的JSON构造函数
json_build_object显式指定每层的JSON键名,配合JSON专属聚合函数json_agg做分层聚合,结合侧联查询关联两张表的数据
单条可运行SQL
SELECT json_build_object( -- 最外层根字段:category 数组 'category', json_agg( -- 每个分类的JSON对象,显式定义所有业务要求的键名 json_build_object( 'c.id', c.id, 'c.title', c.title, -- 分类下关联的运动集合 'sport', COALESCE(category_sport.sport_collection, '[]'::json) ) ORDER BY c.id ) ) AS api_result FROM category c -- 侧联查询:单独聚合每个分类下的运动数据,避免全量GROUP BY的性能损耗 LEFT JOIN LATERAL ( SELECT json_agg( -- 每个运动的JSON对象,定义对应键名 json_build_object( 's.id', sport.id, 's.title', sport.title ) ORDER BY sport.id ) AS sport_collection FROM sport WHERE sport.category_id = c.id ) category_sport ON true;
实现要点
- 所有JSON层级的键名都通过
json_build_object手动定义,完全不会出现键名丢失的情况,可根据API需求任意调整键名 - 采用
LEFT JOIN LATERAL实现关联数据聚合:逐行处理分类表时,先在子查询里聚合对应分类下的所有运动数据,既可以保留没有关联运动的分类记录,又避免全量关联后做外层GROUP BY带来的内存开销 - 通过
COALESCE将无关联运动时的null值替换为空JSON数组[],匹配前端取数组的逻辑要求 - 聚合过程中可通过
ORDER BY控制数组内元素的排序规则,输出顺序完全可控 - 后续要扩展JSON返回字段时,只需在对应层级的
json_build_object中新增键值对即可,无需调整整体查询逻辑
内容的提问来源于stack exchange,提问作者Matrob9
相关产品推荐
相关产品推荐

