Querying Postgres array columns with ANY and contains

Postgres ARRAY columns are filtered with any_() for a single value, .contains() when every element must be present and .overlap() when any will do — all three index-assisted by a GIN index, while column == value silently compares the whole array. This guide belongs to querying Postgres JSONB, arrays and full-text search.

Quick Answer

Map the column with ARRAY, then pick the predicate that matches the question.

from sqlalchemy import String
from sqlalchemy.dialects.postgresql import ARRAY as PG_ARRAY
from sqlalchemy.orm import Mapped, mapped_column

from shop.models import Base


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    tags: Mapped[list[str]] = mapped_column(PG_ARRAY(String), default=list)

from sqlalchemy import any_, select

# Does the array contain this one value?  ->  'sale' = ANY(products.tags)
stmt = select(Product).where("sale" == any_(Product.tags))

# Does it contain all of these?           ->  products.tags @> ARRAY['sale','clearance']
stmt = select(Product).where(Product.tags.contains(["sale", "clearance"]))

# Does it contain any of these?           ->  products.tags && ARRAY['sale','new']
stmt = select(Product).where(Product.tags.overlap(["sale", "new"]))

# How many elements?                      ->  array_length(products.tags, 1) > 3
from sqlalchemy import func

stmt = select(Product).where(func.array_length(Product.tags, 1) > 3)

And the index that makes the first three fast:

CREATE INDEX ix_products_tags_gin ON products USING gin (tags);

The mistake to avoid is Product.tags == "sale", which compares the whole array to a single value. Postgres will either reject it or coerce it, and neither is what was meant.

Execution Context & Async Workflow Integration

An array column is a single value that happens to hold several elements, so every predicate is about the array as a whole.

any_() is the SQL ANY construct. It reads backwards compared with Python — the scalar goes on the left — because that is the shape of the generated SQL, 'sale' = ANY(tags). SQLAlchemy also offers Product.tags.any("sale") on the PostgreSQL ARRAY type, which generates the same thing; the any_() form is the one that composes with other operators (<, LIKE) and is worth learning as the default.

.contains() generates @>, which asks whether the left array holds every element of the right. .overlap() generates &&, which asks whether they share at least one. Getting these two the wrong way round is the commonest bug in array queries, and it fails silently — the query runs and returns plausible, wrong rows. The mental shortcut: contains is an AND, overlap is an OR.

Indexing. A GIN index over an array stores one entry per distinct element, each with a list of the rows containing it. @>, && and = ANY() all use it. array_length does not, and neither does anything that transforms the column first — unnest(tags) in a subquery, or array_to_string(tags, ',') LIKE '%sale%', both discard the index. That is the same rule as for expression indexes generally, covered in creating partial and expression indexes from SQLAlchemy models.

Declare the index on the model so Alembic generates it:

from sqlalchemy import Index


class Product(Base):
    __tablename__ = "products"
    __table_args__ = (
        Index("ix_products_tags_gin", "tags", postgresql_using="gin"),
    )

Async specifics. Array columns behave identically under asyncpg; the values arrive as Python lists with no extra round trip, because the array is part of the row. That is the main performance argument for an array over a child table: no join, no second query, no loader strategy to choose. A product list page that needs each product's tags gets them for free, where a child table would need a selectinload and a second statement — the trade-off weighed in using selectinload vs joinedload for N+1 prevention.

One caveat about nulls. 'sale' = ANY(tags) is false when tags is NULL, not unknown-then-false in a surprising way, so the filter behaves. But array_length(NULL, 1) is NULL, so > 3 excludes those rows and IS NULL is needed to find them. Defaulting the column to an empty array — server_default=text("'{}'::text[]") — removes the distinction between "no tags" and "unknown", which is usually what the domain means.

Aggregating, Unnesting and Updating Arrays

Three operations round out day-to-day use.

Building an array from rows. array_agg turns a grouped set of rows into one array, which is how a child table can be read with array ergonomics:

from sqlalchemy import func, select

from shop.models import Order, OrderItem

stmt = (
    select(
        Order.id,
        func.array_agg(OrderItem.sku).label("skus"),
    )
    .join(OrderItem, OrderItem.order_id == Order.id)
    .group_by(Order.id)
)

array_agg with DISTINCT and an internal ordering is often what a report wants:

from sqlalchemy.dialects.postgresql import aggregate_order_by

stmt = select(
    Order.id,
    func.array_agg(
        aggregate_order_by(func.distinct(OrderItem.sku), OrderItem.sku)
    ).label("skus"),
).join(OrderItem).group_by(Order.id)

aggregate_order_by comes from sqlalchemy.dialects.postgresql and is the only way to get ORDER BY inside an aggregate.

Expanding an array into rows. func.unnest used as a table function gives one row per element, which is how to count tag frequencies:

from sqlalchemy import func, select, true

tag = func.unnest(Product.tags).table_valued("tag")

stmt = (
    select(tag.c.tag, func.count().label("products"))
    .select_from(Product)
    .join(tag, true())
    .group_by(tag.c.tag)
    .order_by(func.count().desc())
)

table_valued() is what makes a set-returning function usable in the FROM clause. Note that this query scans the table by design — it is a full aggregation, not a lookup, and no index helps.

Updating an array. Two things surprise people here. First, Postgres has no in-place element update: changing one element rewrites the whole value, and the row with it. Second, SQLAlchemy does not track mutations of a plain ARRAY — appending to the list in Python leaves the attribute looking unchanged, so the flush emits nothing:

product.tags.append("sale")      # not detected: no UPDATE is emitted
await session.commit()           # silently does nothing

Three fixes, in order of preference. Reassign the attribute, which is explicit and needs no extra machinery:

product.tags = [*product.tags, "sale"]
await session.commit()

Use MutableList, which tracks in-place changes at the cost of wrapping every value:

from sqlalchemy.ext.mutable import MutableList


class Product(Base):
    tags: Mapped[list[str]] = mapped_column(
        MutableList.as_mutable(PG_ARRAY(String)), default=list
    )

Or push the change into SQL, which avoids reading the row at all and is race-free under concurrency:

from sqlalchemy import func, update

await session.execute(
    update(Product)
    .where(Product.id == product_id)
    .values(tags=func.array_append(Product.tags, "sale"))
)

That last form matters more than it looks. Read-modify-write on an array loses concurrent appends — two requests each read ['new'], each write their own two-element list, and one tag vanishes. array_append in SQL has no such window, the same reasoning as for JSONB updates in querying and indexing JSONB columns in SQLAlchemy. Use array_remove for deletion, and array_cat to merge.

Resolving Warnings, Errors & Common Mistakes

Two design points are worth more than a table row.

Use postgresql.ARRAY, not the generic one, on Postgres. sqlalchemy.ARRAY is the cross-dialect base and lacks .contains(), .overlap() and .any(); sqlalchemy.dialects.postgresql.ARRAY has them. Importing the wrong one produces an AttributeError at query-build time, which is at least immediate.

Know when an array is the wrong model. An array cannot have a foreign key, so nothing stops tags holding a value no tag table knows about; there is nowhere to put per-value metadata such as who added it and when; and every change rewrites the row, which on a hot table means bloat. A child table gives all three, at the cost of a join. The heuristic: arrays suit short, closed, read-mostly lists — role names, tag slugs, ISO country codes. A list that grows without bound, or that other tables need to join against, belongs in its own table with the cascade rules described in configuring cascade delete and delete-orphan correctly.

The intermediate position — a child table, read as an array with array_agg, and filtered with a EXISTS subquery — gets integrity and reasonable ergonomics, and is usually the right call when in doubt.

Filtering, Sorting and Paginating by Array Contents

Array filters compose with everything else, and two combinations come up repeatedly.

Faceted filtering. A product search with several optional tag filters builds up predicates conditionally, in the pattern described in building dynamic filters and sorting from API query parameters:

from sqlalchemy import select


def search(any_tags: list[str] | None, all_tags: list[str] | None, q: str | None):
    stmt = select(Product)
    if any_tags:
        stmt = stmt.where(Product.tags.overlap(any_tags))
    if all_tags:
        stmt = stmt.where(Product.tags.contains(all_tags))
    if q:
        stmt = stmt.where(Product.name.ilike(f"%{q}%"))
    return stmt.order_by(Product.id).limit(50)

Both array predicates use the one GIN index, and the planner combines them with any other index it has. A tag-and-text search that needs to be fast on both halves wants the text side indexed too, which is implementing full-text search with tsvector in SQLAlchemy rather than ILIKE.

Ranking by overlap size. "Most relevant" for a tag search usually means "shares the most tags", which is a cardinality of an intersection:

from sqlalchemy import func, select

wanted = ["sale", "new", "clearance"]
tag = func.unnest(Product.tags).table_valued("tag")

matches = (
    select(func.count())
    .select_from(tag)
    .where(tag.c.tag.in_(wanted))
    .scalar_subquery()
    .label("matches")
)

stmt = (
    select(Product, matches)
    .where(Product.tags.overlap(wanted))
    .order_by(matches.desc(), Product.id)
    .limit(20)
)

The where clause does the index work and the ordering expression runs only on the surviving rows, which is the important part: computing the overlap for every row in the table would be a scan. Filter with the indexable operator, rank with the expensive expression.

Pagination. Ordering by an array is legal — Postgres compares element by element — but rarely meaningful, and an array is a poor keyset cursor. Paginate by a scalar column and treat the array as a filter only, which is the keyset approach in paginating large result sets with keyset pagination. Where a rank like the one above drives the order, it must be included in the cursor along with the primary key, because ranks tie.

Frequently Asked Questions

Related

• Querying Postgres JSONB, Arrays and Full-Text Search — The parent guide covering all three Postgres types.
• Querying and indexing JSONB columns in SQLAlchemy — The same mutation-tracking problem in JSONB.
• Implementing full-text search with tsvector in SQLAlchemy — GIN indexes for text, alongside arrays.
• Creating partial and expression indexes from SQLAlchemy models — Declaring a GIN index on the model.