PostgreSQL多租户库存查询问题：按仓库存货量筛选并返回全量库存

问题分析 #

你的原查询存在两个核心问题：

1. 过滤范围错误：直接在WHERE子句中筛选inventory表的行，导致仅保留符合条件的仓库记录，聚合后无法返回产品的所有仓库库存。
2. 条件逻辑矛盾：当同时添加warh65和warh777的条件时，同一行inventory不可能同时属于两个不同仓库，因此返回空结果。

正确的思路是：先筛选出符合库存条件的产品，再关联该产品的所有库存记录进行聚合。

方法一：使用EXISTS子句筛选产品 #

该方法通过两次EXISTS检查，确认产品同时满足warh65库存为0、warh777库存>0的条件，再关联所有库存记录生成完整的库存列表：

SELECT 
    p.*,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'warehouse_id', i.inven_warehouse_id,
            'warehouse_quantity', i.inventory_quantity
        )
    ) AS inventory
FROM products p
INNER JOIN inventory i ON p.product_id = i.inven_product_id
WHERE 
    p.product_org = 'orgabc' 
    AND p.product_type = 'appliances'
    -- 检查产品在warh65仓库库存为0
    AND EXISTS (
        SELECT 1 
        FROM inventory i1
        WHERE i1.inven_product_id = p.product_id 
          AND i1.inven_warehouse_id = 'warh65' 
          AND i1.inventory_quantity = 0
    )
    -- 检查产品在warh777仓库库存>0
    AND EXISTS (
        SELECT 1 
        FROM inventory i2
        WHERE i2.inven_product_id = p.product_id 
          AND i2.inven_warehouse_id = 'warh777' 
          AND i2.inventory_quantity > 0
    )
GROUP BY p.product_id, p.product_org, p.product_type;

方法二：使用HAVING子句进行条件聚合 #

该方法先对产品的所有库存进行分组，再通过条件聚合判断是否满足两个仓库的库存要求：

SELECT 
    p.*,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'warehouse_id', i.inven_warehouse_id,
            'warehouse_quantity', i.inventory_quantity
        )
    ) AS inventory
FROM products p
INNER JOIN inventory i ON p.product_id = i.inven_product_id
WHERE 
    p.product_org = 'orgabc' 
    AND p.product_type = 'appliances'
GROUP BY p.product_id, p.product_org, p.product_type
HAVING 
    -- 统计warh65仓库的库存总量（单个产品在单个仓库仅一条记录，即等于该仓库的库存数）
    SUM(CASE WHEN i.inven_warehouse_id = 'warh65' THEN i.inventory_quantity ELSE 0 END) = 0
    -- 统计warh777仓库的库存总量并判断是否>0
    AND SUM(CASE WHEN i.inven_warehouse_id = 'warh777' THEN i.inventory_quantity ELSE 0 END) > 0;

关键优化点 #

• 使用JSON_BUILD_OBJECT生成符合预期的键值对结构，替代原查询的row()函数，确保返回的inventory数组格式与示例一致。
• 筛选逻辑聚焦于产品而非库存行，保证聚合时能包含该产品的所有仓库库存记录。

预期结果 #

执行上述查询后，将返回符合条件的产品及其所有仓库的库存信息，与你提供的示例结构一致：

[
 {
   "product_id": "producta",
   "product_org": "orgabc",
   "product_type": "appliances",
   "inventory":[
     {"warehouse_id":"warh65", "warehouse_quantity": 0},
     {"warehouse_id":"warh098", "warehouse_quantity": 5},
     {"warehouse_id":"warh777", "warehouse_quantity": 2}
   ]
 }
]

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