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;
select
pid,
json_agg(json_build_object('code',code,'name',name))
from
...
group by
pid
SELECT pid from activity a, (
select json_agg(project) from project where code = a.code
) as p
Something like that ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With