postgresql - 如何将多个行值展平为 postgres 中的连接字符串？

Question

我有一组简单的三张表。 2 个“源”表和一个允许一对多关系的“连接”表。

我有这个问题:

          select data.mph1.data ->> 'name' as name, data.mph1.data ->> 'tags' as Tags, 
          data.mph2.data ->> 'School' as school from data.mph1 
          join data.mph1tomph2 on data.mph1tomph2.mph1 = data.mph1.id
          join data.mph2 on data.mph2.id = data.mph1tomph2.mph2

输出显示为:

            Name             Tags                                School
           "Steve Jones"    "["tag1", "tag2"]"                  "UMass"
           "Steve Jones"    "["tag1", "tag2"]"                  "Harvard"
           "Gary Summers"   "["java", "postgres", "flutter"]"   "Yale"
           "Gary Summers"   "["java", "postgres", "flutter"]"   "Harvard"
           "Gary Summers"   "["java", "postgres", "flutter"]"   "UMass"

我要找的是

            Name             Tags                               School
           "Steve Jones"    "["tag1", "tag2"]"                  "UMass", "Harvard"
           "Gary Summers"   "["java", "postgres", "flutter"]"   "Yale, Harvard, UMass"

如何在单个查询中获得此结果？可能吗？

Answer 1

使用聚合函数string_agg()

select 
    data.mph1.data ->> 'name' as name, 
    data.mph1.data ->> 'tags' as tags, 
    string_agg(data.mph2.data ->> 'School', ', ') as school
from data.mph1 
join data.mph1tomph2 on data.mph1tomph2.mph1 = data.mph1.id
join data.mph2 on data.mph2.id = data.mph1tomph2.mph2
group by data.mph1.id  -- if id is a primary key
-- group by 1, 2       -- otherwise