PostgreSQL Aggregate, Array and Conditional Functions: FILTER, Ordered string_agg/array_agg, unnest, ANY/ALL, COALESCE and GREATEST

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02, right after reloading the schema; psql shows NULL values as (null), as in the psqlrc from post 02.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, wants a stock summary per category in the back office: how many books there are, how many are sold out, how many copies of the books priced 49 TL and above are on the shelf, and which of those expensive books are sold out. Within the same grouping every column needs a different condition. Then we ran the same aggregates with a condition that returns no rows at all:

SELECT b.category,
       count(*) AS books,
       count(*) FILTER (WHERE s.quantity_on_hand = 0) AS out_of_stock,
       sum(s.quantity_on_hand) FILTER (WHERE b.price >= 49) AS units_premium,
       array_agg(b.book_id ORDER BY b.book_id) FILTER (WHERE s.quantity_on_hand = 0 AND b.price >= 49) AS sold_out_premium
FROM books AS b
JOIN stock_items AS s ON s.book_id = b.book_id
GROUP BY b.category
ORDER BY b.category;
SELECT count(*) AS cnt, sum(quantity_on_hand) AS total, avg(quantity_on_hand) AS average,
       array_agg(book_id) AS ids, string_agg(book_id::text, ',') AS csv, bool_and(quantity_on_hand > 0) AS all_in_stock
FROM stock_items
WHERE book_id > 1000000;

For categories without a sold-out expensive book, does the last column return an empty array or NULL? With no rows at all, do count and sum say the same thing?

 category | books | out_of_stock | units_premium |  sold_out_premium
----------+-------+--------------+---------------+--------------------
 Classic  | 22000 |          106 |         49419 | {5913,10914,27643}
 Fantasy  |  4000 |           25 |         10641 | (null)
 Horror   |  2000 |            7 |          4924 | (null)
 SciFi    | 12000 |           69 |         30397 | (null)

 cnt | total  | average |  ids   |  csv   | all_in_stock
-----+--------+---------+--------+--------+--------------
   0 | (null) |  (null) | (null) | (null) | (null)

When no rows come in, count returns 0 and every other aggregate returns NULL: not an empty array, an empty string or false. If you put the result of array_agg into a JSON field or a list on the C# side, this difference matters; you need coalesce(array_agg(...), '{}') to fix it. FILTER (WHERE ...), which gives each column its own condition in a single query, is part of the SQL standard; PostgreSQL supports it, T-SQL does not. In this post we look at PostgreSQL's aggregate, array and conditional functions. The general logic of aggregates and GROUP BY is in the SQL Temelleri series, and T-SQL's COALESCE, NULLIF and ISNULL in the conversion and NULL functions post of the same series.

Conditional Aggregation with FILTER

In T-SQL the same report is written with the SUM(CASE WHEN quantity_on_hand = 0 THEN 1 ELSE 0 END) idiom. That works in PostgreSQL too, but FILTER is both more readable and fits every aggregate: giving array_agg a condition with CASE produces NULL elements for the unwanted rows, while FILTER keeps the row out of the aggregate entirely. FILTER works with window functions as well (count(*) FILTER (WHERE ...) OVER (...)).

The rules are simple: the condition inside FILTER is evaluated per row like a WHERE, but independently of the query's own WHERE. The query's WHERE removes a row completely, FILTER only from that column's aggregate. That is why we see the number of sold-out books on the same row as the category total.

Ordered and DISTINCT Aggregates: string_agg, array_agg, jsonb_agg

Collecting the lines of an order into one row for the order detail screen:

SELECT o.order_number,
       string_agg(b.title, ', ' ORDER BY oi.unit_price DESC, b.title) AS titles,
       array_agg(b.isbn ORDER BY b.isbn) AS isbns,
       jsonb_agg(jsonb_build_object('isbn', b.isbn, 'qty', oi.quantity) ORDER BY b.isbn) AS lines,
       bool_or(b.category = 'Horror') AS has_horror,
       bool_and(oi.quantity = 1) AS all_single
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.order_id = 1003
GROUP BY o.order_number;
SELECT array_agg(DISTINCT b.category ORDER BY b.category) AS categories,
       (array_agg(b.title ORDER BY oi.unit_price DESC))[1:2] AS top2
FROM order_items AS oi
JOIN books AS b ON b.book_id = oi.book_id
WHERE oi.order_id BETWEEN 1000 AND 1010;

 order_number |                        titles                        |                    isbns                    |                                                      lines                                                      | has_horror | all_single
--------------+------------------------------------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------------------------+------------+------------
 BK-00001003  | Middlemarch #24663, Hyperion #2496, Jane Eyre #31289 | {9780000002496,9780000024663,9780000031289} | [{"qty": 1, "isbn": "9780000002496"}, {"qty": 1, "isbn": "9780000024663"}, {"qty": 2, "isbn": "9780000031289"}] | f          | f

       categories        |                       top2
-------------------------+---------------------------------------------------
 {Classic,Fantasy,SciFi} | {"War and Peace #17632","The Silmarillion #6057"}

An ORDER BY written inside the aggregate determines the order of the elements in the result; without it the order depends on how the plan reads the rows and may change tomorrow. The T-SQL equivalent is STRING_AGG(...) WITHIN GROUP (ORDER BY ...); in PostgreSQL the same syntax also works in array_agg, jsonb_agg and json_agg. If you remove duplicates with DISTINCT, the ORDER BY can only use the aggregated expression. Slicing the aggregate's result with [1:2] handles a small "first n per group" need such as "the two most expensive books" without a window function; in large groups it is costly because the whole array is built first. We saw jsonb_agg producing API-ready JSON in post 05; the thing to note here is that jsonb stores object keys in its own order (shorter keys first): qty came before isbn. If key order matters, use json_agg and json_build_object. bool_or and bool_and are the aggregate forms of "at least one" and "all"; there is also an equivalent with the standard name every.

Ordered-Set Aggregates: Median, Percentile and mode

SELECT currency,
       round(avg(total_amount), 2) AS mean,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY total_amount) AS median,
       percentile_disc(0.9) WITHIN GROUP (ORDER BY total_amount) AS p90,
       mode() WITHIN GROUP (ORDER BY total_amount) AS most_common
FROM orders
WHERE created_at >= '2026-10-01'
GROUP BY currency
ORDER BY currency;

 currency | mean  | median |  p90   | most_common
----------+-------+--------+--------+-------------
 EUR      | 78.02 |  72.85 | 138.51 |       32.77
 TRY      | 79.12 |  73.27 | 142.48 |       32.77

For October orders the mean basket value is five units above the median: a few large orders pull the mean up, and for the "typical customer" the median is the more accurate number. percentile_cont interpolates a result that falls between two values (the result is double precision), while percentile_disc returns a value that actually exists in the data. In SQL Server, PERCENTILE_CONT only works as a window function, and getting a single row with GROUP BY needs DISTINCT or a subquery; in PostgreSQL it is a normal aggregate. mode() is the most frequent value.

Array Functions and Operators

We met the array type and a text[] column in post 03. The functions used most often in everyday work:

SELECT t.tag, t.n FROM unnest(ARRAY['classic', 'bestseller', 'gift']) WITH ORDINALITY AS t(tag, n);
SELECT * FROM unnest(ARRAY['9780000002496', '9780000024663'], ARRAY[1, 2]) AS t(isbn, qty);
SELECT array_length('{}'::text[], 1) AS len_empty, cardinality('{}'::text[]) AS card_empty,
       array_length(ARRAY[[1, 2], [3, 4]], 1) AS dim1, cardinality(ARRAY[[1, 2], [3, 4]]) AS card_2d,
       array_append(ARRAY['classic'], 'gift') AS appended, array_remove(ARRAY['a', 'b', 'a'], 'a') AS removed,
       array_position(ARRAY['a', 'b', 'c'], 'c') AS pos, ARRAY[1, 2] || 3 AS concat;
SELECT ARRAY['classic', 'gift'] @> ARRAY['gift'] AS contains, ARRAY['classic'] && ARRAY['gift', 'classic'] AS overlaps,
       'gift' = ANY (ARRAY['classic', 'gift']) AS any_eq, 5 > ALL (ARRAY[1, 2, 3]) AS gt_all;

    tag     | n
------------+---
 classic    | 1
 bestseller | 2
 gift       | 3

     isbn      | qty
---------------+-----
 9780000002496 |   1
 9780000024663 |   2

 len_empty | card_empty | dim1 | card_2d |    appended    | removed | pos | concat
-----------+------------+------+---------+----------------+---------+-----+---------
    (null) |          0 |    2 |       4 | {classic,gift} | {b}     |   3 | {1,2,3}

 contains | overlaps | any_eq | gt_all
----------+----------+--------+--------
 t        | t        | t      | t

unnest expands an array into rows; WITH ORDINALITY adds a sequence number, and when several arrays are given it lays their elements out side by side as columns. The latter is the short way to take an "ISBN and quantity" list coming from the application as a single pair of parameters and turn it into a table. array_length returns NULL, not 0, for an empty array, because an empty array has no dimensions at all; for "number of elements", cardinality is safer. In a multidimensional array, too, array_length(..., 1) only counts the first dimension. @> asks "does it contain all of them", && "is there a common element", = ANY "is any of them equal", > ALL "is it greater than all of them".

If you search in an array column, the index type matters. We tagged 40,000 books and added signed to 40 of them:

CREATE TABLE book_tags AS
SELECT book_id,
       CASE book_id % 4 WHEN 0 THEN ARRAY['classic', 'bestseller']
                        WHEN 1 THEN ARRAY['classic']
                        WHEN 2 THEN ARRAY['gift']
                        ELSE ARRAY['fantasy', 'gift'] END
       || CASE WHEN book_id % 997 = 0 THEN ARRAY['signed'] ELSE '{}' END AS tags
FROM books;
ANALYZE book_tags;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM book_tags WHERE tags @> ARRAY['signed'];
CREATE INDEX ix_book_tags_tags ON book_tags USING gin (tags);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM book_tags WHERE tags @> ARRAY['signed'];
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM book_tags WHERE 'signed' = ANY (tags);

                       QUERY PLAN
---------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=387
   ->  Seq Scan on book_tags (actual rows=40.00 loops=1)
         Filter: (tags @> '{signed}'::text[])
         Rows Removed by Filter: 39960
         Buffers: shared hit=387
 Planning:
   Buffers: shared hit=8

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=42
   ->  Bitmap Heap Scan on book_tags (actual rows=40.00 loops=1)
         Recheck Cond: (tags @> '{signed}'::text[])
         Heap Blocks: exact=40
         Buffers: shared hit=42
         ->  Bitmap Index Scan on ix_book_tags_tags (actual rows=40.00 loops=1)
               Index Cond: (tags @> '{signed}'::text[])
               Index Searches: 1
               Buffers: shared hit=2
 Planning:
   Buffers: shared hit=22

                       QUERY PLAN
---------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=384
   ->  Seq Scan on book_tags (actual rows=40.00 loops=1)
         Filter: ('signed'::text = ANY (tags))
         Rows Removed by Filter: 39960
         Buffers: shared hit=384

With a GIN index, @> read 42 buffers instead of 387. But when the same question was asked as 'signed' = ANY (tags), the index was not used: GIN's array operator class supports @>, <@, && and =, not = ANY. In post 12 we saw that this is why the Entity Framework Core provider translates Contains to @> when the column has a GIN index.

IN, = ANY and Array Parameters

Fetching books by the ISBN list of an order is a classic IN query in Bookstore. Let us see how PostgreSQL treats it, then prepare the same query with a single array parameter:

EXPLAIN (COSTS OFF) SELECT book_id, title FROM books WHERE isbn IN ('9780000002496', '9780000024663', '9780000031289');
PREPARE books_by_isbn(text[]) AS
    SELECT book_id, title, price FROM books WHERE isbn = ANY ($1) ORDER BY book_id;
EXPLAIN (COSTS OFF) EXECUTE books_by_isbn(ARRAY['9780000002496', '9780000024663', '9780000031289']);
EXECUTE books_by_isbn(ARRAY['9780000002496', '9780000024663', '9780000031289']);
EXECUTE books_by_isbn('{}');
DEALLOCATE books_by_isbn;

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using ux_books_isbn on books
   Index Cond: ((isbn)::text = ANY ('{9780000002496,9780000024663,9780000031289}'::text[]))

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Sort
   Sort Key: book_id
   ->  Index Scan using ux_books_isbn on books
         Index Cond: ((isbn)::text = ANY ('{9780000002496,9780000024663,9780000031289}'::text[]))

 book_id |       title        | price
---------+--------------------+-------
    2496 | Hyperion #2496     | 14.89
   24663 | Middlemarch #24663 | 20.24
   31289 | Jane Eyre #31289   |  8.07

 book_id | title | price
---------+-------+-------

The planner already turns the IN (...) list into = ANY (array); the plan is the same. The difference is in the query text: with IN, 3 ISBNs and 300 ISBNs are two different query texts, two separate prepared statements and separate rows in pg_stat_statements, and a 300-element list means 300 parameters. = ANY ($1) is one text and one parameter, and it is still valid SQL when the list is empty (an empty IN () is a syntax error). We saw how to send a string[] or List<string> from Npgsql in post 11; Dapper's IN @isbns form, on the other hand, expands the list into separate parameters.

The well-known NOT IN trap is the same with arrays:

SELECT 3 NOT IN (1, 2, NULL) AS not_in_with_null, 3 <> ALL (ARRAY[1, 2, NULL]) AS ne_all_with_null,
       3 = ANY (ARRAY[1, 2, NULL]) AS any_with_null, 2 = ANY (ARRAY[1, 2, NULL]) AS any_found;
SELECT count(*) AS not_in_rows FROM books
WHERE book_id NOT IN (SELECT book_id FROM order_items WHERE order_id <= 3);
SELECT count(*) AS not_in_rows_with_null FROM books
WHERE book_id NOT IN (SELECT book_id FROM order_items WHERE order_id <= 3 UNION ALL SELECT NULL);
SELECT count(*) AS not_exists_rows_with_null FROM books AS b
WHERE NOT EXISTS (SELECT 1 FROM (SELECT book_id FROM order_items WHERE order_id <= 3 UNION ALL SELECT NULL) AS x
                  WHERE x.book_id = b.book_id);

 not_in_with_null | ne_all_with_null | any_with_null | any_found
------------------+------------------+---------------+-----------
 (null)           | (null)           | (null)        | t

 not_in_rows
-------------
       39992

 not_in_rows_with_null
-----------------------
                     0

 not_exists_rows_with_null
---------------------------
                     39992

If the list contains a single NULL, the answer to "3 is none of these" is unknown (NULL), and WHERE drops the row: adding one NULL to the subquery took the result from 39,992 down to zero. NOT EXISTS gives the right result in the same situation. If the subquery's column is nullable, use NOT EXISTS instead of NOT IN.

Conditional Expressions: COALESCE, NULLIF, GREATEST and LEAST

SELECT greatest(10, NULL, 30) AS g, least(10, NULL, 30) AS l, greatest(NULL::int, NULL) AS g_all_null,
       coalesce(NULL, NULL, 'fallback') AS c, nullif(0, 0) AS n, 100 / nullif(0, 0) AS safe_div;
SELECT 100 / 0;
SELECT c.customer_id, c.created_at::date AS registered,
       max(o.created_at)::date AS last_order,
       greatest(c.created_at, max(o.created_at))::date AS last_activity,
       CASE WHEN max(o.created_at) IS NULL THEN 'never'
            WHEN max(o.created_at) < '2026-01-01' THEN 'dormant'
            ELSE 'active' END AS segment
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (2, 99991, 99998)
GROUP BY c.customer_id, c.created_at
ORDER BY c.customer_id;

 g  | l  | g_all_null |    c     |   n    | safe_div
----+----+------------+----------+--------+----------
 30 | 10 |     (null) | fallback | (null) |   (null)

ERROR:  division by zero

 customer_id | registered | last_order | last_activity | segment
-------------+------------+------------+---------------+---------
           2 | 2021-01-01 | 2026-10-20 | 2026-10-20    | active
       99991 | 2022-09-17 | (null)     | 2022-09-17    | never
       99998 | 2022-09-17 | 2023-12-29 | 2023-12-29    | dormant

In PostgreSQL, GREATEST and LEAST ignore NULL arguments and return NULL only if all of them are NULL. That is how the "last activity" of customer 99991, who has never ordered, became the registration date. This behavior is not the same in every database: the MySQL documentation states plainly that GREATEST() returns NULL if any argument is NULL, so the same query would give NULL for this customer on MySQL. GREATEST/LEAST, added in SQL Server 2022, skip NULLs like PostgreSQL; we saw this on SQL Server in the SQL Server 2022 post of the SQL Temelleri series. Put these functions on the checklist when code changes dialect.

COALESCE returns the first non-NULL argument and is standard; T-SQL's ISNULL has no PostgreSQL counterpart, COALESCE is used instead. NULLIF(a, b) returns NULL if the two values are equal: the standard way to turn a division by zero from an error into NULL. Both forms of CASE (the simple CASE x WHEN ... and the searched CASE WHEN ...) work as in T-SQL; T-SQL's IIF and CHOOSE shortcuts have no PostgreSQL counterpart, you write CASE instead.

Bookstore: Stock Status per Category in One Query

Sellable stock is what is left of the quantity on hand after the reserved quantity. In-stock and out-of-stock book counts per category, the share, and a grand total row:

SELECT b.category,
       count(*) FILTER (WHERE s.quantity_on_hand - s.quantity_reserved > 0) AS in_stock,
       count(*) FILTER (WHERE s.quantity_on_hand - s.quantity_reserved <= 0) AS out_of_stock,
       round(100.0 * count(*) FILTER (WHERE s.quantity_on_hand - s.quantity_reserved <= 0)
             / nullif(count(*), 0), 2) AS out_pct
FROM books AS b
JOIN stock_items AS s ON s.book_id = b.book_id
GROUP BY ROLLUP (b.category)
ORDER BY b.category NULLS LAST;

 category | in_stock | out_of_stock | out_pct
----------+----------+--------------+---------
 Classic  |    21894 |          106 |    0.48
 Fantasy  |     3975 |           25 |    0.63
 Horror   |     1993 |            7 |    0.35
 SciFi    |    11931 |           69 |    0.58
 (null)   |    39793 |          207 |    0.52

The table is read once and each column counts with its own condition; ROLLUP adds the total row for all categories at the end (the category column is NULL there). Multiplying by 100.0 avoids integer division; the habit of nullif(..., 0) wherever you divide by count(*) keeps the query from failing on the day a filter produces an empty group.

In Production

string_agg and array_agg collect the whole group in memory. Collecting all the order numbers of the institutional customer with 200,000 orders (customer 1 in the lab) into one string produces a value of several megabytes; return paged results from the API and use these aggregates only on small groups.

For array columns, choose the search operator by the index: with GIN, @> and &&. Check the SQL your ORM generates with EXPLAIN; = ANY (tags) does not use GIN.

Use = ANY ($1) for parameter lists: it does not bloat the plan cache or pg_stat_statements, and an empty list needs no special code. For very large lists (tens of thousands of elements), a COPY into a temporary table followed by a JOIN is more predictable.

Common Pitfalls

Expecting array_agg to return an empty array for an empty group: it returns NULL; add coalesce(..., '{}').

Relying on order without an ORDER BY inside the aggregate: the order that looks right on test data breaks when the plan changes.

NOT IN with a nullable subquery: a single NULL empties the whole result.

Checking for an empty array with array_length(x, 1) = 0: it is never true, because for an empty array the result is NULL. Write cardinality(x) = 0 or x = '{}'.

Assuming GREATEST treats NULL the same in every dialect: PostgreSQL and SQL Server skip it, MySQL returns NULL.

A Small Experiment

★☆☆ For October 2026 orders, compute the number of orders, the number of refunded payments (status = 'Refunded') and the refund rate per payment method (payments.method) in a single query with FILTER.

★★☆ Get the numbers of each customer's last three orders with (array_agg(order_number ORDER BY created_at DESC))[1:3] and compare the result with a query written with ROW_NUMBER(). For customer 1, look at the duration and the EXPLAIN (ANALYZE) output of both queries.

★★★ Using the two-array form of unnest, expand an ISBN and quantity list coming from the application (text[] and int[]) into rows, join it with books and stock_items, and write a single query that returns the lines whose sellable stock is not enough. How should an ISBN that is not in the catalog show up?

In the next post we move on to views and materialized views: updatable views, security_invoker, REFRESH MATERIALIZED VIEW CONCURRENTLY and refreshing the "top 100 best-selling books" list every ten minutes with pg_cron.