CODEWITHSUNDEEP
jsonpostgresql
Ryan Coyne
Ryan Coyne

Reputation: 15

PostgreSQL aggregate function calls cannot be nested using jsonb_agg function

I am trying to have my query return JSON to be used in an API. I am using the PostGIS extension to deal with location information. The PostGIS extension is working well and not an issue with this problem. I only mention this because there are functions in the query related to the PostGIS extension. I have these objects of the JSON document:

Here is my SQL statement:

SELECT c.id, c.name, c.website, c.longdescription, c.description, c.email, 
jsonb_agg((SELECT ev FROM (SELECT ev.title, ev.description, ev.longdescription, jsonb_agg((SELECT ed FROM(SELECT ed.startdate, ed.enddate, ed.id WHERE ed.id notnull)ed)) AS dates, ev.id WHERE ev.id notnull)ev)) AS events, 
jsonb_agg((SELECT ca FROM (SELECT ct.zip, ca.id, ca.street1, ca.street2, ca.addresstype_id, ST_Y(ca.geopoint::geometry) AS latitude, ST_X(ca.geopoint::geometry) AS longitude WHERE ca.id notnull)ca)) AS addresses
FROM companies c
LEFT JOIN events ev ON ev.company_id = c.id
LEFT JOIN companyaddresses ca ON ca.company_id = c.id
LEFT JOIN cities ct ON ct.id = ca.city_id
LEFT JOIN eventdates ed ON ed.event_id = ev.id
GROUP BY c.id

Any ideas how I could reform as not to have the nested functions and still arrive at the correct result?

Upvotes: 1

Views: 2173

Answers (1)

Laposhasú Acsa
Laposhasú Acsa

Reputation: 1580

The problematic line is 

jsonb_agg((SELECT ev FROM (SELECT ev.title, ev.description, ev.longdescription, jsonb_agg((SELECT ed FROM(SELECT ed.startdate, ed.enddate, ed.id WHERE ed.id notnull)ed)) AS dates, ev.id WHERE ev.id notnull)ev)) AS events,

I think it will work, if you move the jsonb_agg into the second subquery. So change

jsonb_agg((SELECT ed FROM(SELECT ed.startdate, ed.enddate, ed.id WHERE ed.id notnull)ed)) AS dates

into

(SELECT jsonb_agg(ed) FROM(SELECT ed.startdate, ed.enddate, ed.id WHERE ed.id notnull)ed) AS dates

I'm not sure this will work, but if you provide CREATE statements to the ed and ev tables, I can refine it.

Upvotes: 1

Related Questions