Unnesting array with in Join PostgreSQL-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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