You need to enable JavaScript to run this app. PostgreSQL 单条SQL查询保留键名的分类-运动嵌套JSON结构
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 16:54:05