PostgreSQL avoid nested aggregate functions

Question

    SELECT
        p.id AS id,
        json_agg((SELECT x FROM (SELECT 
            c.id, 
            c.name, 
            json_agg((SELECT y FROM (SELECT s.id, s.name) y)) AS js2
            ) x)) AS js1
    FROM p
    INNER JOIN s ON s.id = p.s_id
    INNER JOIN c ON c.s_id = p.s_id
    INNER JOIN cc ON c.id = cc.c_id AND p.c_id = cc.c_id
    GROUP BY p.c_id;

I want to aggregate my sql like this, but psql doesn't let me to do js2.

ERROR: aggregate function calls cannot be nested json_agg((SELECT y FROM (SELECT s.id, ...

How can I avoid this?

edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. Do not post code or additional information in comments — a_horse_with_no_name
You can use rextester. See an example in: rextester.com/RTZWK4070 — Emilio Platzer

Ed Mendez Ed Mendez · Accepted Answer · 2017-09-08T16:34:36

Try to change the json_agg in js2 to row_to_json

 SELECT
        p.id AS id,
        json_agg((SELECT x FROM (SELECT 
            c.id, 
            c.name, 
            row_to_json((SELECT y FROM (SELECT s.id, s.name) y)) AS js2
            ) x)) AS js1
    FROM p
    INNER JOIN s ON s.id = p.s_id
    INNER JOIN c ON c.s_id = p.s_id
    INNER JOIN cc ON c.id = cc.c_id AND p.c_id = cc.c_id
    GROUP BY p.c_id;

-HTH

PostgreSQL avoid nested aggregate functions

1 Answers