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;

Are you opposed to doing this as part of the script? Like in python? You can create a server side cursor and iterate through the records to reduce performance issue. — codeBarer
– codeBarer, Commented May 23, 2016 at 20:56
@codeBarer I know it's been five years. No objections to your suggestion (of doing this transformation in a Python script - I'd personally do something like this in Python). But I'm curious about the reasons. Do you think doing it in Python will be better? I'm asking this because my impression so far has been that execution in a database engine is much faster than doing it in a scripting language. Could you elaborate on what pros & cons you see with both approaches? — m01010011
– m01010011, Commented Nov 1, 2021 at 13:11

Abelisto · Accepted Answer · 2016-05-23 21:04:25Z

56

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

answered May 23, 2016 at 21:04

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MC Hammerabi Over a year ago

that is just a great answer!

ceadreak · Accepted Answer · 2016-05-23 21:09:25Z

1

SELECT pid from activity a, (
    select json_agg(project) from project where code = a.code 
) as p

Something like that ...

answered May 23, 2016 at 21:09

ceadreak

1,6832 gold badges18 silver badges27 bronze badges

Collectives™ on Stack Overflow

postgresql json aggregate

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related