PostgreSQL jsonb with dynamic attributes

Question

I have a table products with ID and jsonb:

prod_id|attributes
1| {"3": 17, "4": 50, "5": 195}

And I have a table attributes, that contains the name and id for each attribute:

attr_id|name
3|width
4|size
5|height
6|color

I would like to have the attributes be user definable as it can vary based on products.

I've tried this but it's not working:

SELECT name
FROM attributes
WHERE attr_id = (SELECT attributes->>'3' AS attr_id FROM products)

What am I doing wrong? And is this the correct approach?

---

Expected result:

prod_id|width|size|height
1      |17   |50  |195

Answer 1

You can join the attributes table onto the keys of the JSON object, reaggregating for use later to retrieve the keys in the desired columns:

select t1.prod_id, t1.js -> 'width', t1.js -> 'size', t1.js -> 'height' 
from (select p.prod_id, (select jsonb_object_agg(a.name, v.value) 
         from jsonb_each(p.attributes) v join attributes a on a.attr_id = v.key::int) js   
      from products p
) t1

Try it online!