Question 90061 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql group by and jsons just in one array

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 🙂

Tags:

2

Answers


  1. 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"
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search