Postgresql group by and jsons just in one array

Question

SQL Query like this below

select json_build_array(
            json_build_object(
                 'entegrator_name', e2."name",
                 'provider_name', p."name",
                 'table1_count', count(t1.id)
        )
    )
    FROM table1 AS t1
    JOIN entegrators AS e2 ON e.entegrator_id = e2.id
    JOIN providers AS p ON e.provider_id = p.id
    GROUP BY e2."name", p."name"

result like this below

[{"entegrator_name": "entegrator_name", "provider_name": "provider_name", "table1_count": 6}]
[{"entegrator_name": "entegrator_name1", "provider_name": "provider_name1", "table1_count": 6}]
[{"entegrator_name": "entegrator_name2", "provider_name": "provider_name2", "table1_count": 6}]
...

But expected like this below, so in one row and one array.

[{"entegrator_name": "entegrator_name", "provider_name": "provider_name", "table1_count": 6},
 {"entegrator_name": "entegrator_name1", "provider_name": "provider_name1", "table1_count": 6},
 ...]

how can I do this. Thanks :)

lemon · Accepted Answer · 2023-01-19 13:25:31Z

1

The issue with your query you need two aggregations:

COUNT to count your ids,
JSON_AGG to make a json array out of all json records

As long as aggregation functions cannot be nested, they can't be found within the same scope. Use a subquery to solve this problem.

WITH cte AS (
    SELECT e2."name"    AS e2_name, 
           p."name"     AS p_name, 
           COUNT(t1.id) AS cnt_t1_id
    FROM table1 AS e
    JOIN entegrators AS e2 ON e.entegrator_id = e2.id
    JOIN providers AS p ON e.provider_id = p.id
    GROUP BY e2."name", p."name"
)
SELECT json_agg(
            json_build_object(
                 'entegrator_name', e2_name,
                 'provider_name', p_name,
                 'table1_count', cnt_t1_id
            )
       )
FROM cte

edited Jan 19, 2023 at 13:25

answered Jan 19, 2023 at 13:03

lemon

15.6k6 gold badges23 silver badges42 bronze badges

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

1 Comment

SefaUn Over a year ago

aah sorry. Your JSON_AGG correct using. I wrote wrong. it works so perfect. thanks :)

Alen Oblak · Accepted Answer · 2023-01-19 12:36:56Z

0

You can use the JSON_AGG() aggregate function in the query like this:

SELECT JSON_AGG(
    JSON_BUILD_OBJECT(
        'entegrator_name', e2."name",
        'provider_name', p."name",
        'table1_count', COUNT(t1.id)
    ) ) FROM table1 AS t1 JOIN entegrators AS e2 ON e.entegrator_id = e2.id JOIN providers AS p ON e.provider_id = p.id GROUP BY e2."name", p."name"

answered Jan 19, 2023 at 12:36

Alen Oblak

3,32515 silver badges27 bronze badges

1 Comment

SefaUn Over a year ago

it gives this error ERROR: aggregate function calls cannot be nested. that error caused by 'table1_count', COUNT(t1.id)

Collectives™ on Stack Overflow

Postgresql group by and jsons just in one array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related