CODEWITHSUNDEEP
sqlpostgresqljsonbjsonb-array-elements
Pugalarasan
Pugalarasan

Reputation: 1

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

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.

Upvotes: 0

Views: 37

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23837

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

Upvotes: 0

Related Questions