Query posgresql data joining a table with compound id and multiple criteria for joined table

I'm working in a multi-tenant software to manage inventory of products and I'm having difficulties to create a query to bring products inventory based on quantity criteria.

Use case: Bring all products with all their inventory from all warehouses from current user's organization where product type is from appliances and their quantity in warehouse x is 0 and in warehouse y >0

Expected Result for organization abc, product type as appliances and warehouse warh65=0 and warehouse warh777>0, would be an array of products with an inventory property listing all warehouses inventory for the product not only the one being queried, for example:

[
 {
   product_id: producta,
   ...product,
   inventory:[
     {warehouse_id:warh65, warehouse_quantity: 0},
     {warehouse_id:warh098, warehouse_quantity: 5},
     {warehouse_id:warh777, warehouse_quantity: 1},
   ]
 },
]

Here is the point I am at the moment, where it brings an array with producta and productc, but it doesn't list the rest of inventory in other warehouses, only the one being queried. If I add a second condition for another warehouse the returned array is empty.

Using Postgresql

SELECT p.*, JSON_AGG(row(i.inven_warehouse_id, i.inventory_quantity)) as inventory
FROM products AS p
INNER JOIN inventory AS i
ON p.product_id = i.inven_product_id
WHERE p.product_org = orgabc AND p.product_type='appliances' AND (i.inven_warehouse_id='warn65' AND i.inventory_quantity=0)
GROUP BY p.product_id

Here is a visual aid for the tables and relations. Thank you in advance for the assistance.

Based on the condition given,

Expected Result for organization abc, product type as appliances and warehouse warh65=0 and warehouse warh777>0

• First fetch all data you need by INNER JOIN
• Then use EXISTS to check whether the required conditions are met in the fetch query(warehouse warh65=0 and warehouse warh777>0)

I have built this query, let me know if my understanding is correct. Fiddle seems to be down at the moment, so I could not share the live link with you.

WITH filtered_products AS (
  SELECT 
    p.product_id,
    p.product_org,
    p.product_type,
    JSON_AGG(
      DISTINCT 
      JSONB_BUILD_OBJECT(
        'warehouse_id', i.inven_warehouse_id, 
        'warehouse_quantity', i.inventory_quantity
      )
    ) AS inventory
  FROM 
    products AS p
  INNER JOIN 
    inventory AS i ON p.product_id = i.inven_product_id
  INNER JOIN
    warehouse AS w ON i.inven_warehouse_id = w.warehouse_id
  WHERE 
    p.product_org = 'orgabc' 
    AND p.product_type = 'appliances'
    AND EXISTS (
      SELECT 1 
      FROM inventory 
      WHERE inven_product_id = p.product_id 
      AND inven_warehouse_id = 'warh65' 
      AND inventory_quantity = 0
    )
    AND EXISTS (
      SELECT 1 
      FROM inventory 
      WHERE inven_product_id = p.product_id 
      AND inven_warehouse_id = 'warh777' 
      AND inventory_quantity > 0
    )
  GROUP BY 
    p.product_id, p.product_org, p.product_type
)
SELECT 
  'orgabc' AS organization,
  JSON_AGG(
    JSONB_BUILD_OBJECT(
      'product_id', fp.product_id,
      'product_org', fp.product_org,
      'product_type', fp.product_type,
      'inventory', fp.inventory
    )
  ) AS products
FROM 
  filtered_products fp;

Output JSON

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