postgresql json aggregate

Question

I'm using postgres 9.4, I have the following query:

SELECT pid, code, name FROM activity, (

  SELECT code FROM project

) projects WHERE activity.pcode = projects.code;

which return this following relation:

pid | code |    name    

-------------------------------

  1 | p1   | activity1 

  1 | p3   | activity2

  2 | p1   | activity3

  2 | p2   | activity4

  2 | p3   | activity5

I am trying to write the same query but so that I get the project code an the activity name as a json Array relative to each "pid"

So I am looking for a query that would return something like this:

pid | json    

------------------------------------------------------------------------------------

  1 | [{'code': 'p1', 'name': 'activity1'}, {'code': 'p3', 'name': 'activity2'}] 

  2 | [{'code': 'p1', 'name': 'activity3'}, {'code': 'p2', 'name': 'activity4'}, {'code': 'p3', 'name': 'activity5'}]

Any ideas?
Thanks in advance for the help

UPDATE

Here is what I did (which is close to what Abelisto said):

SELECT pid, json_agg(json_build_object('code', code, 'name', name)) AS agg                                                                                                                                    

FROM activity JOIN (                                                                                                                                                                                          

  SELECT code FROM project                                                                                                                                                                                    

) AS p ON p.code=activity.pcode                                                                                                                                                                               

GROUP BY pid;

Answer 1

select
  pid,
  json_agg(json_build_object('code',code,'name',name))
from
  ...
group by
  pid

postgresql json aggregate

Sign up