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;
pid | code | name
-------------------------------
1 | p1 | activity1
1 | p3 | activity2
2 | p1 | activity3
2 | p2 | activity4
2 | p3 | activity5
pid | json
------------------------------------------------------------------------------------
1 | [{'code': 'p1', 'name': 'activity1'}, {'code': 'p3', 'name': 'activity2'}]
2 | [{'code': 'p1', 'name': 'activity3'}, {'code': 'p2', 'name': 'activity4'}, {'code': 'p3', 'name': 'activity5'}]
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;
select
pid,
json_agg(json_build_object('code',code,'name',name))
from
...
group by
pid