36801

PostgreSQL aggregate function calls cannot be nested using jsonb_agg function

Question:

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:

<ul><li>main Company document (OK)</li> <li>Events related to companies (OK)</li> <li>Dates related to the Events (* this is where we have a problem)</li> <li>Addresses related to Companies (OK)</li> <li>Cities related to Companies (OK)</li> </ul>

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?

Answer1:

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.

Recommend

  • How to get the selected item from the listpicker in windows phone 8?
  • Unable to call stored procedure with FromSql in EF Core
  • Plot USA map - big white triangle. FIXED. New: geocode limit of 2500 requests per day
  • Using LIKE and LIMIT in sqlite queries in Android
  • In rails_admin, how can I filter based on the presence of an association id?
  • Can't bind to 'ngValue' since it isn't a known native property\"
  • Jest - testing a component that uses react-router
  • Open Weather data in React using Ajax
  • multi query and pagination with firestore
  • Passing/Returning references to object + changing object is not working
  • Rails Query to return users belongs to any cities & not belong to any cities
  • Get Distinct Parent Items using Lambda
  • Google web service to get time by location? [duplicate]
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • sending email using “bcc” without “to” in java application
  • JPA - getting distinct value from one column
  • Aggregating two data frame columns without any existing pattern logic
  • DotNetOpenAuth - how to uniquely identify Google users?
  • data.table replicate rows after join?
  • Django: ORDER BY DESC on FloatField puts null values top?
  • .NET video play library which allows to change the playback rate?
  • How to 'create temp table as select' in Slick?
  • Rest Services conventions
  • MySQL Order by column = x, column asc?
  • Typescript - Unable to get 'import' statement to function
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • How to get address from latitude and longitude android google map v2 [duplicate]
  • Is there any way to access browser form field suggestions from JavaScript?
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Weird JavaScript statement, what does it mean?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How to delete a row from a dynamic generate table using jquery?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?