database - 在PostgreSQL中联接两个表，其中一个内容为Array字段，另一个为该数组的主表

Question

我是PostgreSQL的新手，我脑海中只有一个查询。假设有两个表，一个表是Fruit，它具有FruitID(文本)和FruitName(文本)字段。另一个是Food表，其中一个名为Fruit的字段是字符串数组类型，包含FruitID。在PostgreSQL中是否有任何查询，通过将两个表连接起来，我们可以使用水果名称数组而不是水果ID数组从Food表中获取所有数据？

Select * from Fruit

id|Name
--|-----
A1|Mango     
A2|Orange   
A3|Banana 


Select * from Food

id|Day     |Fruit
--|--------|-------
A1|Sunday  |{A1,A2}   
A2|Monday  |{A2,A3}   
A3|Tuesday |{A1,A3}

连接两个表后，我的输出应该是这样的:

id|Day     |Fruit Name
--|--------|--------------
A1|Sunday  |{Mango,Orange}   
A2|Monday  |{Orange,Banana}
A3|Tuesday |{Mango,Banana} 

Answer 1

我将Food表的FruitId扩展为行，将其加入Fruit表，然后使用array_agg()聚合扩展的行。

=# select * from fruit;

 fruitid | fruitname 
---------+-----------
 app     | apple
 ban     | banana
 grp     | grape
 ora     | orange
(4 rows)

=# select * from food;

 id |     fruit     
----+---------------
  1 | {app,grp}
  2 | {app,ban,grp}
(2 rows)

=# with expand as (
  select id, unnest(fruit) as FruitId 
    from food
), lookup as (
   select e.id, f.FruitName 
     from expand e
     join fruit f on f.FruitId = e.FruitId
)
select id, array_agg(FruitName) as FruitNames
  from lookup
 group by id;

 id |      fruitnames       
----+----------------------
  2 | {apple,banana,grape}
  1 | {apple,grape}
(2 rows)

调整为您添加到问题的架构:

with expand as (
  select id, day, unnest(fruit) as fruitid
    from food
), lookup as (
  select e.id, e.day, f.name as fruitname
    from expand e
    join fruit f on f.id = e.fruitid
)
select id, day as "Day", array_agg(fruitname) as "Fruit Name"
  from lookup
 group by id, day
 order by id;

 id |   Day   |   Fruit Name    
----+---------+-----------------
 A1 | Sunday  | {Mango,Orange}
 A2 | Monday  | {Orange,Banana}
 A3 | Tuesday | {Mango,Banana}
(3 rows)