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 :)

Gordon Linoff · Accepted Answer · 2016-01-17T23:14:44.690

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

Although this doesn't produce a Simple "null" for Kevin, its like : [{"ScoreName" : null, "Score" : null}], but still its simple and elegant. Thanks :) — Apple-and-Oranges, Jan 17 '16 at 18:52

score 1 · Answer 2 · answered Jan 17 '16 at 18:34

Use json_agg() with row_to_json() to aggregate scores data into a json value:

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)

JOIN, aggregate and convert in postgres between two tables

2 Answers2