两个表之间在postgres中的联接、聚合和转换

Question

下面是我拥有的两个表:两个表中的所有列都是"text“类型，表名和列名都是粗体字体。

名字

--------------------------------
Name     |    DoB   |     Team |
--------------------------------
Harry    |  3/12/85 |  England  
Kevin    |  8/07/86 |  England  
James    |  5/05/89 |  England  

分值

------------------------
ScoreName  |   Score   
------------------------
James-1    |   120      
Harry-1    |   30      
Harry-2    |   40      
James-2    |   56      

我需要的最终结果是一个具有以下内容的表

NameScores

---------------------------------------------
Name     |    DoB   |     Team |   ScoreData
---------------------------------------------
Harry    |  3/12/85 |  England  | "{"ScoreName":"Harry-1", "Score":"30"}, {"ScoreName":"Harry-2", "Score":"40"}" 
Kevin    |  8/07/86 |  England  | null
James    |  5/05/89 |  England  | "{"ScoreName":"James-1", "Score":"120"}, {"ScoreName":"James-2", "Score":"56"}"

我需要使用一个SQL命令来完成这个任务，我将使用这个命令来创建一个物化视图。

我已经意识到它将涉及string_agg、JOIN和JSON的组合，但是还没有完全破解它。(请帮助:)

Answer 1

我不认为join是棘手的。复杂的是构建JSON对象：

select n.name, n.dob, n.team,
       json_agg(json_build_object('ScoreName', s.name,
                                  'Score', s.score)) as ScoreData
from names n left join
     scores s
     ons.name like concat(s.name, '-', '%')
group by n.name, n.dob, n.team;

注：json_build_object()是在Postgres 9.4中引入的。

编辑：

我认为您可以添加一个case语句来获得简单的NULL

       (case when s.name is null then NULL
             else json_agg(json_build_object('ScoreName', s.name,
                                             'Score', s.score))
        end) as ScoreData

Answer 2

使用json_agg()和row_to_json()将分数数据聚合为json值：

select n.*, json_agg(row_to_json(s)) "ScoreData"
from "Names" n
left join "Scores" s
on n."Name" = regexp_replace(s."ScoreName", '(.*)-.*', '\1')
group by 1, 2, 3;

 Name  |   DoB   |  Team   |                                 ScoreData                                 
-------+---------+---------+---------------------------------------------------------------------------
 Harry | 3/12/85 | England | [{"ScoreName":"Harry-1","Score":30}, {"ScoreName":"Harry-2","Score":40}]
 James | 5/05/89 | England | [{"ScoreName":"James-1","Score":120}, {"ScoreName":"James-2","Score":56}]
 Kevin | 8/07/86 | England | [null]
(3 rows)