How to get intersection of two arrays/lists in sqlalchemy

Question

I have similar problem to this one (most similar is the answer with &&). For postgres, I would like to get the intersection of array column and python list. I've tried to do that with && operator:

query(Table.array_column.op('&&')(cast(['a', 'b'], ARRAY(Unicode)))).filter(Table.array_column.op('&&')(cast(['a', 'b'], ARRAY(Unicode))))

but it seems that op('&&') return bool type (what has sense for filter) not the intersection.

So for table data:

id   |   array_column
1        {'7', 'xyz', 'a'}
2        {'b', 'c', 'd'}
3        {'x', 'y', 'ab'}
4        {'ab', 'ba', ''}
5        {'a', 'b', 'ab'}

I would like to get:

id   |   array_column
1        {'a'}
2        {'b'}
5        {'a', 'b'}

Answer 1

One way^* to do this would be to unnest the array column, and then re-aggregate the rows that match the list values, grouping on id. This could be done as a subquery:

select id, array_agg(un) 
  from (select id, unnest(array_column) as un from tbl) t
  where un in ('a', 'b') 
  group by id 
  order by id;

The equivalent SQLAlchemy construct is:

subq = sa.select(
    tbl.c.id, sa.func.unnest(tbl.c.array_column).label('col')
).subquery('s')
stmt = (
    sa.select(subq.c.id, sa.func.array_agg(subq.c.col))
    .where(subq.c.col.in_(['a', 'b']))
    .group_by(subq.c.id)
    .order_by(subq.c.id)
)

returning

(1, ['a'])
(2, ['b'])
(5, ['a', 'b'])

---

^* There may well be more efficient ways.