Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table_a (orderid  text, items text[],  name text);

CREATE TABLE


insert into table_a
VALUES
  ('order1', '{item1,item2}', 'Bob''s pizza order'),
  ('order2', '{item3,item1}', 'Alice''s breakfast order');

INSERT 0 2


create table table_b (itemid text, price numeric, name text);

CREATE TABLE


insert into table_b
VALUES
  ('item1', 2.95, 'cheese'),
  ('item2', 3.15, 'tomato sauce'),
  ('item3', 3.50, 'eggs');

INSERT 0 3


select b.itemid, a.orderid, b.name
from table_a a 
  cross join unnest(a.items) as i(item) 
  join table_b b on i.item = b.itemid
order by b.itemid;


itemid
orderid
name


item1
order1
cheese

item1
order2
cheese

item2
order1
tomato sauce

item3
order2
eggs



SELECT 4

itemid	orderid	name
item1	order1	cheese
item1	order2	cheese
item2	order1	tomato sauce
item3	order2	eggs