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 :)

CodePudding user response：

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

CodePudding user response：

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"