在Join PostgreSQL中取消使用的数组的目标

Question

我有两个表，其中一个表有一个唯一的ID列，另一个列包含数组，这些数组为表B中的唯一记录保存零个或多个标识符，但是的所有订单上有多少商品

表A:

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

表B:

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

所需输出将类似

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

有人知道如何取消测试表A中的数组，这样我就可以使用所有的项来加入A&B每个订单的每一项都有记录？

Answer 1

要将每个项目作为一行，您需要运行数组(本质上是动态规范化模型(：

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;

在线示例

Answer 2

看起来postgresSQL有一些我不习惯的强大功能！

在查看了一些其他文档后，这可能会产生你正在寻找的效果-

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

如果Items列是一个真正的数组，则应该能够删除trim CTE并删除string_to_array函数。

整个事情可能看起来像

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