Working with Arrays

Array Column Definition

from sqlalchemy import ARRAY, String, Integer

class Article(Base):
    __tablename__ = "articles"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    tags: Mapped[list] = mapped_column(ARRAY(String(50)))
    ratings: Mapped[list] = mapped_column(ARRAY(Integer))

Inserting Array Data

article = Article(
    title="Python Tips",
    tags=["python", "programming", "tips"],
    ratings=[5, 4, 5, 3, 5]
)
session.add(article)

Querying Arrays

# Contains element
stmt = select(Article).where(
    Article.tags.contains(["python"])
)

# Any element matches
from sqlalchemy import any_

stmt = select(Article).where(
    "python" == any_(Article.tags)
)

# All elements match
from sqlalchemy import all_

stmt = select(Article).where(
    5 == all_(Article.ratings)  # All ratings are 5
)

# Array overlap (shares any element)
stmt = select(Article).where(
    Article.tags.overlap(["python", "java"])
)

# Array contains
stmt = select(Article).where(
    Article.tags.contains(["python", "tips"])
)

# Contained by
stmt = select(Article).where(
    Article.tags.contained_by(["python", "java", "tips", "programming"])
)

Array Functions

from sqlalchemy import func

# Array length
stmt = select(Article, func.array_length(Article.tags, 1))

# Unnest (expand array to rows)
stmt = select(func.unnest(Article.tags))

# Array aggregation
stmt = select(func.array_agg(Article.title))

# Array append (in update)
from sqlalchemy import text
session.execute(
    text("UPDATE articles SET tags = array_append(tags, :tag) WHERE id = :id"),
    {"tag": "new_tag", "id": 1}
)

# Array remove
session.execute(
    text("UPDATE articles SET tags = array_remove(tags, :tag) WHERE id = :id"),
    {"tag": "old_tag", "id": 1}
)

Array Indexing

# GIN index for array columns
class Article(Base):
    __tablename__ = "articles"
    __table_args__ = (
        Index('ix_article_tags', 'tags', postgresql_using='gin'),
    )

PostgreSQL Arrays

PostgreSQL Arrays

Working with Arrays

Array Column Definition

Inserting Array Data

Querying Arrays

Array Functions

Array Indexing