Join tables using a value inside a JSONB column postgresql

Question

I have two tables in postgresql.

1. the first (product) has sku json row ([149461190])
2. the second (item) has an ordinary sku column

How can I join them on sku? I tried this, but it didn't work. cannot recognize input near 'jsonb_to_recordset' '(' 'ps' in joinSourcePart

  select * from product ps
, jsonb_to_recordset(ps.sku -> 'ps_sku') as (sku text)
join item v using sku
       

Answer 1

I hope this query help you, You can see data structure and sample data in dbfiddle

select 
  *
from  
  product p
  cross join jsonb_array_elements_text(p.sku -> 'ps_sku') as j(sku)
  inner join item i on i.sku = j.sku :: numeric