How do I combine multiple one-to-many results with Postgres json_agg?

Question

Multiple json_aggregates in function

My Postgres database's main table has a couple of one-to-many relationships to other tables, each connected by a [*]_references table. I need a result that comes back with results from the other tables as JSON. I can join up to get the data I want as so:

SELECT ft.id, json_agg(genres) AS genres 
FROM ft_references ft 
INNER JOIN genre_references gr ON gr.reference_id = ft.id 
INNER JOIN genres_catalog genres ON gr.genre_id = genres.id 
WHERE ft.id = 2 GROUP BY ft.id;

 id |                      genres                      
----+--------------------------------------------------
  2 | [{"id":1,"name":"Action","other_info":null},    +
    |  {"id":2,"name":"Adventure","other_info":null}, +
    |  {"id":5,"name":"Comedy","other_info":null},    +
    |  {"id":8,"name":"Drama","other_info":null},     +
    |  {"id":10,"name":"Fantasy","other_info":null},  +
    |  {"id":13,"name":"Horror","other_info":null},   +
    |  {"id":25,"name":"War","other_info":null}]
(1 row)

Likewise my second set of results:

SELECT ft.id, json_agg(tales) AS tales 
FROM ft_references ft 
INNER JOIN tale_references tr ON tr.reference_id = ft.id 
INNER JOIN tale_catalog tales ON tales.id = tr.tale_catalog_id 
WHERE ft.id = 2 GROUP BY ft.id;

 id |                                      tales                                      
----+---------------------------------------------------------------------------------
  2 | [{"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null}]
(1 row)

Both of these results are correct. However, when I want to put them together, suddenly I'm getting the product of my genre and tale results (ie: triple results!):

SELECT ft.id, json_agg(genres) AS genres, json_agg(tale) AS tales 
FROM ft_references ft 
INNER JOIN genre_references gr ON gr.reference_id = ft.id 
INNER JOIN genres_catalog genres ON gr.genre_id = genres.id 
INNER JOIN tale_references tr ON tr.reference_id = ft.id 
INNER JOIN tale_catalog tale ON tale.id = tr.tale_catalog_id 
WHERE ft.id = 2 GROUP BY ft.id, gr.reference_id, tr.reference_id;
 id |                      genres                      |                                      tales                                      
----+--------------------------------------------------+---------------------------------------------------------------------------------
  2 | [{"id":1,"name":"Action","other_info":null},    +| [{"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":1,"name":"Action","other_info":null},    +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":1,"name":"Action","other_info":null},    +|  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":2,"name":"Adventure","other_info":null}, +|  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":2,"name":"Adventure","other_info":null}, +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":2,"name":"Adventure","other_info":null}, +|  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":5,"name":"Comedy","other_info":null},    +|  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":5,"name":"Comedy","other_info":null},    +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":5,"name":"Comedy","other_info":null},    +|  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":8,"name":"Drama","other_info":null},     +|  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":8,"name":"Drama","other_info":null},     +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":8,"name":"Drama","other_info":null},     +|  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":10,"name":"Fantasy","other_info":null},  +|  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":10,"name":"Fantasy","other_info":null},  +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":10,"name":"Fantasy","other_info":null},  +|  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":13,"name":"Horror","other_info":null},   +|  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":13,"name":"Horror","other_info":null},   +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":13,"name":"Horror","other_info":null},   +|  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null},  +
    |  {"id":25,"name":"War","other_info":null},      +|  {"id":82,"tale_id":"570","tale_type":"Pied Piper","other_info":null},         +
    |  {"id":25,"name":"War","other_info":null},      +|  {"id":39,"tale_id":"327A","tale_type":"Hansel and Gretel","other_info":null}, +
    |  {"id":25,"name":"War","other_info":null}]       |  {"id":8,"tale_id":"124","tale_type":"The Three Brothers","other_info":null}]
(1 row)

How can I restructure my query so I'm only getting the correct `genres` results as in the first example, together with the correct `tales` results as in the second example, instead of things being over-done as in my actual (third) example?

Answer 1

The answer came as follows:

SELECT ft.id, 
        json_agg(DISTINCT genres.*) AS genres, 
        json_agg(DISTINCT tale.*) AS tales
  FROM ft_references ft 
  JOIN genre_references gr ON gr.reference_id = ft.id
  JOIN genres_catalog genres ON genres.id = gr.genre_id
  JOIN tale_references tr ON tr.reference_id = ft.id
  JOIN tale_catalog tale ON tale.id = tr.tale_catalog_id
  WHERE ft.id = 2 GROUP BY ft.id;