JOIN, aggregate and convert in postgres between two tables

Question

Here are the two tables i have: [all columns in both tables are of type "text"], Table name and the column names are in bold fonts.

Names

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

Scores

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

End result i need is a table that has the following

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"}"

I need to do this using a single SQL command which i will use to create a materialized view.

I have gotten as far as realising that it will involve a combination of string_agg, JOIN and JSON, but haven't been able to crack it fully. Please help :)

Answer 1

I don't think the join is tricky. The complication is building the JSON object:

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;

Note: json_build_object() was introduced in Postgres 9.4.

EDIT:

I think you can add a case statement to get the simple NULL:

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