Unnesting array with in Join PostgreSQL

Question

I have two tables, one of which has a unique ID column and a column which contains arrays which hold zero or more identifiers for unique records in Table B. I'm trying to show the orders each item was used in, but I can't wrap my head around a way to join table A and B in such a way that there's a row not just for every order or item, but for as many items are there are on all the orders

Table A:

OrderID   |      Items       |      name
----------+------------------+------------
order1    | {item1,item2}    |  "Bob's pizza order"
order2    | {item3,item1}    |  "Alice's breakfast order"

Table B:

itemID   |      price     |      name
---------+----------------+------------
item1    |      2.95      |  "cheese"
item2    |      3.15      |  "tomato sauce"
item3    |      3.50      |  "eggs"

Desired output would be something like

ItemID   |     OrderID    |     name
---------+----------------+------------
item1    |     order1     |  "cheese"
item1    |     order2     |  "cheese"
item2    |     order1     |  "tomato sauce"
item3    |     order2     |  "eggs"

Does anyone know how to unnest the array in Table A in such a way that I can then use all the items to join A & B with records for every item of every order?

You can read this post: https://stackoverflow.com/questions/2486725/postgresql-join-with-array-type-with-array-elements-order-how-to-implement — Viettel Solutions, Dec 08 '21 at 04:02
A better solution is to properly normalize your data model. If you need to unnest array on a regular basis this is a strong indication that the choice to de-normalize was not a good one. — , Dec 08 '21 at 06:15

score 1 · Accepted Answer · answered Dec 08 '21 at 06:19

To get each item as a row, you need to unnest your array (essentially normalizing the model on the fly):

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;

Online example

procopypaster · Answer 2 · 2021-12-08T06:28:09.383

Looks like postgresSQL has some great functionality that I'm not used to!

After looking into some other docs, this may have the effect you're looking for -

with table_a (OrderID, Items) as
      (select 'order1', '{item1,item2,item4}' union all
       select 'order2', '{item3,item1}'
    )
,

trim as (
  select OrderID, Replace(Replace(Items, '{', ''),'}','') as Items
from table_a)

SELECT
OrderID,
unnest(string_to_array(Items, ',')) AS ItemID
from trim

If the Items column is a true array, you should be able to remove the trim CTE and remove the string_to_array function.

Whole thing could look like -

with unnested_data as(
SELECT
 OrderID,
 unnest(Items) AS ItemID
FROM table_a)

SELECT
 a.ItemID,
 a.OrderID,
 b.name
FROM unnested_data AS a
JOIN table_b AS b
 on a.ItemID = b.itemID

Unnesting array with in Join PostgreSQL

2 Answers2