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?

Some sample data showing the relationship between the 3 tables would help here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 14, 2024 at 1:39

Tim Biegeleisen · Accepted Answer · 2024-02-14 12:20:47Z

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';

edited Feb 14, 2024 at 12:20

answered Feb 14, 2024 at 1:57

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

perusopersonale Over a year ago

thanks for try to help me. The problem was the structure of modules table, that contain a json field. Adding to_json instead jsonb_build_object. Fix the problem

Tim Biegeleisen Over a year ago

@Charlieface I agree with your comment and have edited.

Collectives™ on Stack Overflow

Postgres, unable to usedistinct in json_aggr

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related