Join tables using a value inside a JSONB column postgresql

Question

I have two tables in postgresql.

the first (product) has sku json row ([149461190])
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

Please append your JSON structure and fields, Also which column on item table do you want to join? — Pooya
– Pooya, Commented Nov 2, 2021 at 7:02
json column from the first table looks like this column name sku [149408204], [149461190], [149422714] — Alexandra Salamatina
– Alexandra Salamatina, Commented Nov 2, 2021 at 7:09
the second table also has sku column, it contains sky's but not in a json: 149461190, 149422714 — Alexandra Salamatina
– Alexandra Salamatina, Commented Nov 2, 2021 at 7:11
You should change the data model and not use JSON or arrays for that purpose. — Laurenz Albe
– Laurenz Albe, Commented Nov 2, 2021 at 7:35

Pooya · Accepted Answer · 2021-11-02 07:25:25Z

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

answered Nov 2, 2021 at 7:25

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexandra Salamatina Over a year ago

Thanks, but again a mistake ->> org.apache.hadoop.hive.ql.parse.ParseException:line 5:39 cannot recognize input near 'jsonb_array_elements_text' '(' 'p' in joinSourcePart

Collectives™ on Stack Overflow

Join tables using a value inside a JSONB column postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related