PostgreSQL聚合函数无法嵌套：如何实现机器库存数据结构化输出？

问题核心 #

PostgreSQL不允许在聚合函数（如array_agg）中直接嵌套另一个聚合函数（如count），因此需要先在子查询/CTE中完成低维度（机器+餐品）的统计计算，再进行高维度（机器）的数组聚合。

正确的SQL查询语句 #

WITH dish_inventory AS (
    SELECT
        l.id AS machine_id,
        l.name AS machine_name,
        l.capacity AS machine_capacity,
        d.id AS dish_id,
        d.name AS dish_name,
        ld.suggested_quantity,
        -- 统计处于运输状态的餐品数量：truck_id非空的记录
        COUNT(CASE WHEN m.truck_id IS NOT NULL THEN 1 END) AS in_transport_quantity,
        -- 统计已上架到机器的餐品数量：machine_id非空的记录
        COUNT(CASE WHEN m.machine_id IS NOT NULL THEN 1 END) AS stocked_quantity
    FROM public.locations l
    INNER JOIN public.location_dishes ld ON l.id = ld.location_id
    INNER JOIN public.dishes d ON ld.dish_id = d.id
    -- 左关联meals表，确保无库存记录的餐品也能被保留
    LEFT JOIN public.meals m 
        ON d.id = m.dish_id 
        AND (m.truck_id IS NOT NULL OR m.machine_id IS NOT NULL)
    WHERE l.type = 'Machine'
    GROUP BY l.id, l.name, l.capacity, d.id, d.name, ld.suggested_quantity
)
SELECT
    machine_id,
    machine_name,
    machine_capacity,
    ARRAY_AGG(
        JSON_BUILD_OBJECT(
            'dish_id', dish_id,
            'dish_name', dish_name,
            'suggested_quantity', suggested_quantity,
            'in_transport_quantity', in_transport_quantity,
            'stocked_quantity', stocked_quantity
        )
    ) AS machine_plan
FROM dish_inventory
GROUP BY machine_id, machine_name, machine_capacity
LIMIT 10; -- 获取指定的10条机器记录

关键说明 #

1. CTE预统计：通过dish_inventory公共表表达式，先完成每个机器下单个餐品的库存统计，规避聚合函数嵌套的问题。
2. 精准统计逻辑：使用CASE分支配合COUNT，分别统计运输中和已上架的餐品数量，比直接count(m.truck_id)的逻辑更清晰可控。
3. 左关联保留全量餐品：用LEFT JOIN关联meals表，避免因餐品无库存记录而被过滤，确保每个机器的所有关联餐品都能出现在结果中。
4. 聚合生成数组：外层查询对机器维度分组，用ARRAY_AGG将每个餐品的JSON对象聚合成数组，完全匹配前端需要的machine_plan结构。

匹配的前端输出格式 #

返回的JSON结构与需求一致：

{
  machine_id: "机器UUID",
  machine_name: "机器名称",
  machine_capacity: 机器容量,
  machine_plan: [
    {
      dish_id: "餐品UUID",
      dish_name: "餐品名称",
      suggested_quantity: 建议库存数,
      in_transport_quantity: 运输中数量,
      stocked_quantity: 已上架数量
    },
    // 更多餐品对象...
  ]
}

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