Вызовы агрегатных функций PostgreSQL не могут быть вложены с помощью функции jsonb_agg

Question

Я пытаюсь, чтобы мой запрос возвращал JSON для использования в API. Я использую расширение PostGIS для работы с информацией о местоположении. Расширение PostGIS работает хорошо и не является проблемой с этой проблемой. Я упоминаю об этом только потому, что в запросе есть функции, связанные с расширением PostGIS. У меня есть эти объекты документа JSON:

• основной документ компании (ОК)
• События, связанные с компаниями (ОК)
• Даты, связанные с событиями (* здесь у нас проблема)
• Адреса, связанные с компаниями (ОК)
• Города, связанные с компаниями (ОК)

Вот мой SQL-оператор:

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

Любые идеи, как я мог бы изменить, чтобы не иметь вложенных функций и все же прийти к правильному результату?

Answer 1

Проблемная линия

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 во второй подзапрос. Так что меняй

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

в

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

Я не уверен, что это сработает, но если вы предоставите операторы CREATE ed а также ev таблицы, я могу это уточнить.