Join sql table on key from jsonb array column in one table to field on another table

Question

I have users table which has a cart column of type jsonb array, for example:

[{"product_id": 1, "qty": 2}, 
 {"product_id": 2, "qty": 3}]

and products table that has primary key product_id.

I want to retrive all the products that are present in the documents in the cart column of the users table.

Answer 1

You can use built-in functions like jsonb_to_recordset to expand the proucts jsonb and join to products table. ie:

select p.product_id, products.name, p.qty
from usercarts uc,
     lateral jsonb_to_recordset(uc.products) as p(product_id int, qty int)
         left join products on p.product_id = products.product_id
where user_id = 1;

DBFiddle demo