CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
hyperknot
hyperknot

Reputation: 13966

Distinct values from an ARRAY column

I'm using PostgreSQL's ARRAY to store tags for images.

How can I write an ORM query in SQLAlchemy, which returns the set of all tags found in the table, for the following model:

from sqlalchemy.dialects.postgresql import ARRAY

class Image(Base):
    __tablename__ = 'images'
    id = Column(String, primary_key=True)
    tags = Column(ARRAY(Unicode))

I guess I need to use a lateral join, but I do not know how to do it using SQLAlchemy's ORM syntax.

PG version: 9.5

Upvotes: 2

Views: 1863

Answers (1)

Jesse Bakker
Jesse Bakker

Reputation: 2623

You can use func.unnest:

from sqlalchemy import func

session.query(func.unnest(Image.tags)).distinct().all()

distinct() will make the result a set and unnest will split the arrays into separate rows (like the postgresql function unnest).

Upvotes: 3

Related Questions