Querying Postgres JSONB, Arrays and Full-Text Search

Use JSONB for documents whose keys vary by row, ARRAY for short lists of one scalar type, and a generated TSVECTOR for text search — index all three with GIN, wrap the mutable ones in MutableDict or MutableList, and query them with SQLAlchemy's dialect comparators so the filtering happens in PostgreSQL. This topic belongs to advanced query patterns and bulk data operations.

Concept & Execution Model

PostgreSQL's richer column types exist so that data which does not fit a flat row can still be queried and indexed inside the database, rather than loaded into Python and filtered there. SQLAlchemy 2.0 exposes three of them through the PostgreSQL dialect, and each comes with a comparator that turns Python operators into the right SQL.

Three types, three jobs Three tiles. JSONB holds semi-structured documents whose keys vary by row, queried with containment and path operators and indexed with GIN. ARRAY holds an ordered list of one scalar type, such as tags or permission names, queried with any, contains and overlap and indexed with GIN. TSVECTOR holds a pre-processed document for full-text search, queried with the match operator against a tsquery and indexed with GIN. JSONB documents with varying keys @> ? @? GIN (jsonb_ops / path_ops) ARRAY(String) a list of one scalar type ANY @> && GIN (array_ops) TSVECTOR lexemes for text search @@ tsquery GIN (tsvector_ops) All three are served by GIN, which indexes the parts of a value rather than the whole value.

JSONB stores a parsed JSON document. Its comparator renders ->, ->>, @>, ? and the jsonpath operators, and its bind processor serialises dictionaries using the engine's JSON serializer. ARRAY stores an ordered list of one element type; its comparator renders ANY, @> and &&. TSVECTOR stores a document already processed for full-text search, queried with @@ against a tsquery. All three are indexed with GIN, which indexes the parts of a value — keys, elements, lexemes — so a query can find rows containing a part without reading every value.

from sqlalchemy import Computed, Index, String
from sqlalchemy.dialects.postgresql import ARRAY, JSONB, TSVECTOR
from sqlalchemy.ext.mutable import MutableDict, MutableList
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    sku: Mapped[str] = mapped_column(String(64), unique=True)
    name: Mapped[str]
    description: Mapped[str] = mapped_column(default="")
    attributes: Mapped[dict] = mapped_column(MutableDict.as_mutable(JSONB), default=dict)
    tags: Mapped[list[str]] = mapped_column(
        MutableList.as_mutable(ARRAY(String(40))), default=list
    )
    search_vector: Mapped[str] = mapped_column(
        TSVECTOR,
        Computed(
            "setweight(to_tsvector('english', coalesce(name, '')), 'A') || "
            "setweight(to_tsvector('english', coalesce(description, '')), 'B')",
            persisted=True,
        ),
    )

    __table_args__ = (
        Index("ix_products_attributes", "attributes", postgresql_using="gin"),
        Index("ix_products_tags", "tags", postgresql_using="gin"),
        Index("ix_products_search_vector", "search_vector", postgresql_using="gin"),
    )

The model above is the running example for this topic. Two guides take its columns in depth: querying and indexing JSONB columns covers the comparator, change tracking and index choice for documents, and implementing full-text search with tsvector covers generated vectors, query parsing and ranking. This page covers what they share, the ARRAY type that sits between them, and how to decide which one a piece of data belongs in.

All of this sits inside advanced query patterns and bulk data operations, and it is PostgreSQL-specific by design. MySQL and SQLite have JSON types with different operators and no equivalent of GIN; code that must run on several databases should keep these columns behind a repository boundary, or use the generic JSON type and accept its narrower feature set.

Query Construction & Async Execution Patterns

Statements built with these comparators are ordinary select() constructs, so the familiar rule holds: construction is identical in sync and async code, and only the execution call differs.

Sync and async differ only in the await Left, synchronous: session.scalars of a select where Product.tags contains a list, returning products carrying both tags. Right, asynchronous: the identical statement passed to await session.scalars. The statement construction, the operator and the rendered SQL are the same. sync Session stmt = select(Product) .where(Product.tags.contains(['usb', 'c'])) session.scalars(stmt).all() tags @> ARRAY['usb', 'c'] AsyncSession stmt = select(Product) .where(Product.tags.contains(['usb', 'c'])) (await session.scalars(stmt)).all() tags @> ARRAY['usb', 'c'] Build statements once, in shared code, and let each caller choose how to execute them.
# Sync
from sqlalchemy import select
from sqlalchemy.orm import Session

from shop.models import Product


def tagged(session: Session, *tags: str) -> list[Product]:
    stmt = select(Product).where(Product.tags.contains(list(tags))).order_by(Product.id)
    return list(session.scalars(stmt))

# Async
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from shop.models import Product


async def tagged(session: AsyncSession, *tags: str) -> list[Product]:
    stmt = select(Product).where(Product.tags.contains(list(tags))).order_by(Product.id)
    return list(await session.scalars(stmt))

The ARRAY comparator has three operations worth memorising, because they map to the three questions asked of a list. Product.tags.any_() == "sale", or the equivalent standalone any_(Product.tags) == "sale", renders 'sale' = ANY (tags) — does the list contain this one value? (The older Product.tags.any("sale") method still works but is the legacy spelling.) Product.tags.contains(["usb", "c"]) renders tags @> ARRAY[...] — does it contain all of these? Product.tags.overlap(["usb", "hdmi"]) renders tags && ARRAY[...] — does it contain any of these? The GIN index serves @> and &&. It does not serve = ANY(...), so for index-backed single-value lookups write contains(["sale"]) instead.

The three types combine naturally in one statement, which is where keeping them in the database pays off:

from sqlalchemy import func, select

from shop.models import Product


def catalogue_search(q: str, brand: str | None, tags: list[str], limit: int = 20):
    query = func.websearch_to_tsquery("english", q)
    rank = func.ts_rank_cd(Product.search_vector, query).label("rank")
    stmt = select(Product, rank).where(Product.search_vector.bool_op("@@")(query))
    if brand:
        stmt = stmt.where(Product.attributes.contains({"brand": brand}))
    if tags:
        stmt = stmt.where(Product.tags.overlap(tags))
    return stmt.order_by(rank.desc(), Product.id).limit(limit)

PostgreSQL can combine the three GIN indexes with a BitmapAnd, reading only rows that satisfy every filter. The same filters applied in Python after loading would read every text match into memory first.

Results come back as native Python values: dict for JSONB, list for ARRAY, and a string for TSVECTOR, which is rarely worth selecting at all. When a query selects only a JSONB element — select(Product.attributes["brand"].astext) — the result is a plain string, and yield_per streaming applies to large exports exactly as it does for flat columns.

State Management & Session Boundaries

Mutable column values are where these types interact with the session, and the interaction is easy to get wrong because nothing fails — changes are simply not saved.

Mutable values through a session Five steps. On load, a MutableDict or MutableList wrapper replaces the plain container. An in-place change such as append or item assignment calls changed on the wrapper, which flags the parent attribute. At flush, the whole value is written in one UPDATE, because PostgreSQL has no partial write for these types. At commit, with expire_on_commit true the value is reloaded on next access. Without a Mutable wrapper, step two never happens and nothing is written. load value wrapped in MutableList wrapper holds a parent reference product.tags.append("sale") wrapper calls changed() attribute flagged dirty flush UPDATE products SET tags = ... the whole array, every time commit expired or kept per expire_on_commit Nested structures are not tracked: a dict inside a MutableList is still a plain dict.

The ORM notices a change when an attribute is assigned. A dictionary or list that is mutated in place is still the same object, so no assignment happens. MutableDict.as_mutable(JSONB) and MutableList.as_mutable(ARRAY(...)) fix this by wrapping loaded values in container subclasses that report their own changes to the owning object. Every loaded value is wrapped, and every value you assign is coerced into the wrapper.

from sqlalchemy.orm.attributes import flag_modified

product = await session.get(Product, 42)

product.tags.append("clearance")             # tracked: MutableList
product.attributes["color"] = "graphite"      # tracked: MutableDict
product.attributes["dims"]["width"] = 41      # NOT tracked: nested plain dict

flag_modified(product, "attributes")          # mark the whole value dirty
await session.commit()

Three consequences follow. First, a flush writes the whole value, never a delta — PostgreSQL rewrites the row anyway, and SQLAlchemy has no partial-update syntax for these types. Two concurrent sessions that edit different keys of the same document therefore overwrite each other, and the second commit silently wins. Where that matters, use optimistic locking with a version counter, or push the edit into SQL with jsonb_set() in an update() so the database applies it to the current value.

Second, expire_on_commit applies to these attributes like any other. Under async, reading an expired attribute raises rather than lazily loading, so a handler that commits and then reads product.attributes needs either expire_on_commit=False or an explicit await session.refresh(product).

Third, a generated TSVECTOR column is server-maintained. After an update to name, the ORM expires search_vector because the database recomputed it, and reading it requires a fresh load. Most code never reads the vector, which is the right design — it is an index input, not application data.

Advanced Type Extensions: Typed Documents and Domain Arrays

A Mapped[dict] attribute is honest about what JSONB is, and unhelpful about what your documents contain. Two extensions make these columns carry domain types without giving up database-side querying.

Column, array, document or table? Four bands. If every row has the value and you filter, sort or join on it, it is a column. If it is a short list of one scalar type used for membership tests, it is an ARRAY. If its keys vary by row and it is read mostly as a whole, it is JSONB. If items have their own attributes, identity, or foreign keys, or the list is unbounded, it is a child table. a column every row has it; you filter, sort, join or constrain on it an ARRAY a short list of one scalar type, tested for membership: tags, roles, locales a JSONB document keys vary by row, read mostly whole: attributes from suppliers, settings a child table items have their own fields, identity or foreign keys, or the list is unbounded Move data down this ladder as it acquires rules. Moving it up later is a migration, not a refactor.

A TypeDecorator over JSONB can convert between a validated model and the stored document. The trade-off is that change tracking no longer applies to the inner fields — a frozen model makes that explicit, because the only way to change it is to assign a new one, which the ORM does see.

from dataclasses import asdict, dataclass

from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.types import TypeDecorator


@dataclass(frozen=True, slots=True)
class ShippingProfile:
    weight_grams: int
    fragile: bool = False
    hazmat_class: str | None = None


class ShippingProfileType(TypeDecorator):
    impl = JSONB
    cache_ok = True

    def process_bind_param(self, value: ShippingProfile | None, dialect):
        return None if value is None else asdict(value)

    def process_result_value(self, value: dict | None, dialect):
        return None if value is None else ShippingProfile(**value)

Querying still works through the underlying type's operators when you cast back: cast(Product.shipping, JSONB).contains({"fragile": True}) uses the same GIN index as before. The TypeDecorator pattern for encrypted columns covers cache_ok, bind expressions and testing in more depth.

Arrays of enumerated values are the other common extension. ARRAY(Enum(Role, name="role")) works, but under asyncpg the driver needs to know the array's element type, and PostgreSQL enum arrays have historically needed an explicit cast on the bound parameter. The pragmatic path is ARRAY(String) with a CHECK constraint limiting the allowed values, which stays portable across migrations — changing a PostgreSQL enum inside an array column is a multi-step operation, as adding a value to a Postgres enum in Alembic explains.

Multi-tenant designs sometimes store per-tenant settings in JSONB on a tenant row. That works well for settings read whole at request start, and badly for anything queried across tenants. If a setting is ever filtered on in a report, promote it to a column.

Hybrid Architectures & Migration Strategies

Most codebases arrive at these types from one of two directions: a 1.4-era model using the generic JSON type and string-built search SQL, or a schema that started flat and grew an extra text column holding serialised JSON.

From 1.4 habits to 2.0 constructs Left, legacy: Column(JSON) with no mutation tracking, text() fragments building to_tsquery from user input, and session.query with filter. Right, 2.0: Mapped dict with MutableDict.as_mutable(JSONB), a Computed TSVECTOR column with a GIN index, websearch_to_tsquery with a bound parameter, and select with where. legacy 1.4 model data = Column(JSON) text("... to_tsquery('" + q + "')") session.query(Product).filter(...) edits lost, input in SQL text 2.0 model Mapped[dict] with MutableDict(JSONB) Computed TSVECTOR + GIN index websearch_to_tsquery(:q) select(Product).where(...) Move JSON to JSONB first: it is a type change PostgreSQL can do in place with USING data::jsonb.

For the first, the migration is mostly mechanical. Change Column(JSON) to mapped_column(MutableDict.as_mutable(JSONB)), and let Alembic generate ALTER COLUMN ... TYPE JSONB USING data::jsonb. That rewrites the table, so on a large table schedule it or use the expand-and-backfill approach. Replace session.query(Product).filter(...) with select(Product).where(...), and replace every text() fragment that concatenates user input into to_tsquery with a bound func.websearch_to_tsquery("english", q). The last change is a security fix as much as a modernisation.

For the second, add a real JSONB column alongside the text one, backfill with UPDATE products SET attributes = extra::jsonb WHERE ... in batches, switch reads, then drop the old column in a later release.

Core and ORM mix well here. Bulk loads of documents — a supplier feed of fifty thousand products — are much faster through Core insert() with a list of dictionaries than through ORM objects, and JSONB values in those dictionaries are serialised by the same engine serializer:

from sqlalchemy.dialects.postgresql import insert as pg_insert

from shop.db import engine
from shop.models import Product


async def load_feed(rows: list[dict]) -> None:
    stmt = pg_insert(Product).values(rows)
    stmt = stmt.on_conflict_do_update(
        index_elements=[Product.sku],
        set_={"attributes": stmt.excluded.attributes, "tags": stmt.excluded.tags},
    )
    async with engine.begin() as conn:
        await conn.execute(stmt)

For very large feeds, Core executemany bulk inserts and PostgreSQL COPY are faster again, and the generated search vector is computed by the database either way.

Maintaining GIN Indexes in Production

GIN indexes behave differently from the B-tree indexes most teams are used to, and three of those differences show up in production: they are large, they defer part of their write cost, and they are easy to create and never use.

GIN indexes are not free Bar chart of an illustrative catalogue. The table data is the baseline. A default jsonb_ops GIN index on the attributes column is roughly half the size of the table. A jsonb_path_ops index is roughly a third smaller than that. An expression B-tree index on one extracted key is small. products table baseline GIN jsonb_ops on attributes ≈ half the table GIN jsonb_path_ops on attributes ≈ a third smaller than jsonb_ops B-tree on attributes ->> 'brand' small: one key per row Illustrative proportions; measure yours with pg_relation_size. Every GIN index also slows writes.

Size comes from what GIN indexes. A B-tree has one entry per row; a GIN index has one entry per part — every key in every document, every element in every array, every lexeme in every vector — with a posting list of the rows containing it. For a catalogue with rich attribute documents, a default jsonb_ops index can approach half the size of the table. Measure before and after with pg_relation_size, and prefer jsonb_path_ops when containment is the only operator your queries use.

Write cost is deferred through the pending list. With fastupdate on, which is the default, new entries are appended to an unsorted list and merged into the main index later — by autovacuum, by an explicit gin_clean_pending_list(), or synchronously by whichever insert pushes the list past gin_pending_list_limit. The result is a distinctive latency pattern: most writes are fast, and an occasional one is very slow because it paid for everyone else's merge. Searches also slow down as the list grows, because it has to be scanned linearly.

from sqlalchemy import Index

# Tune per index: a smaller pending list means smoother write latency and faster searches,
# at the cost of slightly slower average inserts.
Index(
    "ix_products_attributes",
    "attributes",
    postgresql_using="gin",
    postgresql_with={"fastupdate": "on", "gin_pending_list_limit": 1024},  # kilobytes
)

For bulk loads the opposite choice is right: drop or disable the index, load, then build it once, which is much faster than maintaining it row by row.

Unused indexes are the third problem, and GIN makes them likely because the operator a query uses decides whether the index applies. pg_stat_user_indexes.idx_scan counts how often each index has been used since statistics were last reset. An index that stays at zero after a week of real traffic is serving no query and costing every write:

from sqlalchemy import text

UNUSED_INDEXES = text("""
    SELECT relname AS table_name, indexrelname AS index_name,
           pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
    FROM pg_stat_user_indexes
    WHERE idx_scan = 0
    ORDER BY pg_relation_size(indexrelid) DESC
""")


async def report_unused_indexes(session) -> list[dict]:
    return [dict(row._mapping) for row in await session.execute(UNUSED_INDEXES)]

Before dropping one, check replicas as well — statistics are per server, and an index idle on the primary may be busy serving read traffic routed to a replica. Then drop it with DROP INDEX CONCURRENTLY in a migration, following the same outside-the-transaction pattern used to create it.

Arrays and documents each have a guide of their own. Querying Postgres array columns with ANY and contains works through any_(), contains() and overlap(), the GIN index that makes them fast, and when a child table is the better model. For documents with a known shape, validating JSONB documents with Pydantic TypeDecorators puts a real schema on the column — typed attributes, defaults in one place, and a versioning path for when the shape changes.

Production Pitfalls & Anti-Patterns

  • ProgrammingError: operator does not exist: jsonb ~~* character varying — a text operator applied to attributes["key"], which is jsonb. Use .astext.
  • In-place edits not persisted — a plain JSONB or ARRAY mapping has no change tracking. Wrap with MutableDict or MutableList, and flag_modified() for nested edits.
  • TypeError: Object of type Decimal is not JSON serializable at flush — the default serializer cannot encode it. Pass json_serializer= to create_async_engine.
  • syntax error in tsquery — raw user input passed to to_tsquery. Use websearch_to_tsquery.
  • generation expression is not immutable — one-argument to_tsvector. Name the configuration.
  • Sequential scans despite a GIN index — ->> equality or = ANY(...), neither of which GIN serves. Use .contains(), or add an expression B-tree index.

Index size is the quieter cost. GIN indexes are large, and every write to an indexed column updates them. Index the operators you actually query with, prefer jsonb_path_ops when containment is the only operator you use, and check sizes with pg_relation_size before adding a third GIN index to a write-heavy table.

Frequently Asked Questions

Should product attributes be JSONB or columns?

Columns for anything every row has and that you filter, sort, join or constrain on; JSONB for attributes whose keys vary by supplier or category and that are mostly read whole. Promote a key to a column as soon as it starts appearing in WHERE clauses across the codebase.

Is ARRAY better than a child table for tags?

For a short list of plain strings tested for membership, yes — one row, one GIN index, no join. Use a child table as soon as tags have their own attributes, need referential integrity, or are renamed across many products at once.

Do these types work with aiosqlite in tests?

No. JSONB operators, ARRAY and TSVECTOR are PostgreSQL-only. Test code that uses them against a real PostgreSQL instance, such as a Postgres testcontainer.

Why was my JSONB change not saved?

Because it was an in-place mutation of an untracked value. Map the column with MutableDict.as_mutable(JSONB), and call flag_modified() after editing nested structures.