Is there a way to return a total count and rows in a single psql query?

Admin 1
Viewed 113

Consider the following:

CREATE TABLE IF NOT EXISTS person (
    id                  BIGSERIAL,
    favorite_color      TEXT,
    PRIMARY KEY (id)
);


INSERT INTO person (favorite_color) VALUES ('red');
INSERT INTO person (favorite_color) VALUES ('green');
INSERT INTO person (favorite_color) VALUES ('blue');
INSERT INTO person (favorite_color) VALUES ('blue');
INSERT INTO person (favorite_color) VALUES ('blue');

I want to now query the table to give me a COUNT for all the rows where favorite_color = blue AND return all the rows. How can I achieve the following output with a single postgresql query?

{
    "total" 3,
    "results": [
        {"id": 3, "favorite_color": "blue"},
        {"id": 4, "favorite_color": "blue"},
        {"id": 5, "favorite_color": "blue"},
    ]
}

I know how to write SQL statements, I am asking if it is possible to write a single SQL statement to achieve my desired result, oppose to hitting the database twice (once for the count, second for results).

postgresql sql json
2 Answers

You can use the windowed version of count() to get the count.

SELECT json_build_object('total', max(total),
                         'results', json_agg(element))
       FROM (SELECT count(*) OVER () AS total,
                    row_to_json(person) AS element
                    FROM person
                    WHERE favorite_color = 'blue') AS x;

db<>fiddle

user_96614240

Here is an alternative with a scalar subquery and no window function or min(). Leave the optimizer alone to do its job.

with p as (select * from person where favorite_color = 'blue')
select jsonb_build_object(
 'total', count(*), 
 'results',  
 (
  select jsonb_agg(to_jsonb(t)) 
    from (select id, favorite_color from p) t
 ))
from p;
user_23020320
Related