CODEWITHSUNDEEP
sqlpostgresqljoininner-join
guarinex
guarinex

Reputation: 122

Fetch posgresql data in JSON format including both filtered and non-filtered data

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: 2},
   ]
 },
]

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.

product_id product_org product_type product_...
producta orgabc appliances row
productb orgabc hand tools row
productc orgabc hand tools row
org_id org_...
orgabc row
orgxyz row
warehouse_id warehouse_org warehouse_name
warh65 orgabc this
warh098 orgabc that
warh777 orgabc that
warh523 orgxyz that
warhhgy orgxyz that
inven_warehouse_id inven_product_id inventory_quantity
warh65 producta 0
warh098 producta 5
warh777 producta 2
warh65 productb 3
warh098 productb 1
warh777 productb 0
warh65 productc 0
warh098 productc 1
warh777 productc 1

Upvotes: 1

Views: 54

Answers (2)

Charlieface
Charlieface

Reputation: 72194

The other answer is completely over-complicated. You just need conditional aggregation in a HAVING to filter the grouped results by the whole group.

SELECT
  p.*,
  jsonb_agg(
    jsonb_build_object(
      'warehouse_id', i.inven_warehouse_id,
      'quantity', 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'
GROUP BY p.product_id
HAVING COUNT(*) FILTER (WHERE i.inven_warehouse_id = 'warh65'  AND i.inventory_quantity = 0) > 0
  AND  COUNT(*) FILTER (WHERE i.inven_warehouse_id = 'warh777' AND i.inventory_quantity > 0) > 0;

If you want the final result as one big JSON then you need two levels of aggregation.

db<>fiddle

Upvotes: 0

samhita
samhita

Reputation: 3505

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"
  }
]

Upvotes: 1

Related Questions