SQLAlchemy: how to obtain all distinct values from Array field?

Question

I have the following model of a blog post:

title = db.Column(db.String())
content = db.Column(db.String())
tags = db.Column(ARRAY(db.String))

Tags field can be an empty list.

Now I want to select all distinct tags from the database entries with max performance - excluding empty arrays.

So, say I have 3 records with the following values of the tags field:

The result would be ['database', 'server', 'connection', 'security']

The actual order is not important.

This may help: [eliminate duplicate array values in postgres](https://stackoverflow.com/q/3994556/1164465) — Christopher Peisert, Aug 30 '19 at 20:39

score 0 · Answer 1 · answered Sep 03 '19 at 20:29

The distinct() method should still work fine with array columns.

from sqlalchemy import func

unique_vals = BlogPost.query(func.unnest(BlogPost.tags)).distinct().all()

This would be identical to running a query in postgres:

SELECT DISTINCT unnest(tags) FROM blog_posts

1 Answers1