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

CodePudding user response：

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

CodePudding user response：

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;