Postgresql JSONB | nested arrays gives ERROR aggregate function calls cannot be nested

Question

So i'm trying to convolute multiple queries and form the result as

[

    {
        heading: 'Test',
        subheading: 'SubTest',
        pItems: [
            {
                pid: 1,
                product_name: 'Random Product',
                stock: 500
            },
            {
                pid: 1,
                product_name: 'Random Product',
                stock: 500
            }
        ]
    },
    {
        heading: 'Test 2',
        subheading: 'SubTest 2',
        pItems: [
            {
                pid: 1,
                product_name: 'Random Product 2',
                stock: 500
            },
            {
                pid: 1,
                product_name: 'Random Product 2',
                stock: 500
            }
        ]
    }

]

Here is what i tried

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=af81919b853b571ca2f25c96abbad596

But i'm getting this error

ERROR: aggregate function calls cannot be nested

S-Man · Accepted Answer · 2019-08-16 09:26:31Z

1

You have two json_agg() calls. This function is an aggregate function. For each nested aggregate you need a separate GROUP BY clause. That's what the error message says:

demo:db<>fiddle

SELECT 
    *
FROM (
    SELECT 
        test, client_id, project_id,
        json_build_object (
            'myData_updated',
            json_agg(p_items)
        )
    FROM (
        SELECT
            test, client_id, project_id,
            jsonb_build_object(
                'heading', heading_elems -> 'heading',
                'subheading', heading_elems -> 'subheading',
                'pItems', json_agg(elems || jsonb_build_object('product_name', po.name))
            ) AS p_items

        FROM mainTable mt CROSS JOIN LATERAL
            jsonb_array_elements(mt.myData) AS heading_elems CROSS JOIN LATERAL
            jsonb_array_elements(heading_elems -> 'pItems') AS elems

        JOIN products po ON (elems ->> 'pid' )::int = po.pid

        GROUP BY test, client_id, project_id, heading_elems
    ) s
    GROUP BY test, client_id, project_id
) s

INNER JOIN clients client ON client.client_id = s.client_id
INNER JOIN projects project on project.project_id = s.project_id

Additionally, I would avoid to mix up the JSON part and joining all other tables in your case. That's why I put the JSON part into subqueries and join the irrelevant tables last.

answered Aug 16, 2019 at 9:26

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

3 Comments

Élisa Plessis Over a year ago

Your code isn't considering multiple pItems check your output it only has one item in pItems

S-Man Over a year ago

Yes, of course. Your examples only has one product. So what should be taken for pid = 2, if product 2 does not exist? Here I added a second product and it works: dbfiddle.uk/…

Élisa Plessis Over a year ago

oh my bad.. I forgot about the non existent reference. Thank you for your help again !

Collectives™ on Stack Overflow

Postgresql JSONB | nested arrays gives ERROR aggregate function calls cannot be nested

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related