Postgres, unable to usedistinct in json_aggr

Question

I'm not so good with SQL I have three tables agencies, modules, documents. Modules and documents belongs to company. I want to select a company with all the information and aggregate by status:

select "agencies".*,
JSON_AGG(  modules.*) FILTER(WHERE modules.status like 'active') as activeModules,
JSON_AGG(  modules.*) FILTER(WHERE modules.status like 'draft') as draftModules,
JSON_AGG(  documents.*) FILTER(WHERE documents.status like 'active') as activeDocuments
from "agencies"
left outer join "modules" on "agencies"."id" = "modules"."agency_id"
left outer join "documents" on "agencies"."id" = "documents"."agency_id"
where "agencies"."id" = '959e5e04-e8ba-4367-b3d7-8bc5d5b5e666' group by "agencies"."id"

This works, but all the records aggregated are duplicated

If I change the select like this:

JSON_AGG(  DISTINCT modules.*) FILTER(WHERE modules.status like 'active') as activeModules,

It raise an error: ERROR: could not identify an equality operator for type modules

I have found a solution by doing something like this:

JSON_AGG( distinct jsonb_build_object(modules.id, modules.field2...) ) FILTER(WHERE modules.status like 'active') as activeModules,

This works,but I don't like the idea to specify all the fields. Is there a way to achieve what I want without having to insert all the fields?

Answer 1

You could try writing your query to use subqueries to find the JSON aggregates:

SELECT a.*, m.activeModules, m.draftModules, d.activeDocuments
FROM agencies a
LEFT JOIN
(
    SELECT agency_id,
           JSON_AGG(*) FILTER (WHERE status LIKE 'active') AS activeModules,
           JSON_AGG(*) FILTER (WHERE status LIKE 'draft') AS draftModules
    FROM modules
    GROUP BY agency_id
) m
    ON a.id = m.agency_id
LEFT JOIN
(
    SELECT agency_id,
           JSON_AGG(*) FILTER (WHERE status LIKE 'active') AS activeDocuments
    FROM documents
    GROUP BY agency_id
) d
    ON a.id = d.agency_id
WHERE a.id = '959e5e04-e8ba-4367-b3d7-8bc5d5b5e666';