1

It is possible to filter by checking if a column is in a literal using the in_ column operator of SQLAlchemy - doc

filter(COL.in_(1,2,3)) is converted to SQL WHERE COL IN (1, 2, 3) which filters by checking if a column is in a literal

I am searching for a way to filter by checking if a literal is in a column, where the column is of type array (or repeated field in BigQuery)

Is there an SQLAlchemy construct in which it is possible to create a filter similar to the following SQL (BigQuery Standard)

WHERE 1 in UNNEST(COL)

A very similar query in PostgreSQL and the expected results can be seen here

Alonme
  • 1,364
  • 15
  • 28
  • what is the behaviour you are getting with in_ operator and how does it deviate from what you would like to achieve? – Albert Albesa Jun 30 '20 at 09:29
  • The `in_` operator can only be used on a column to my understanding. and thus i can only create SQL similar to the first SQL snippet `WHERE COL IN (1, 2, 3)` – Alonme Jun 30 '20 at 10:54
  • See https://stackoverflow.com/questions/15072976/string-literal-as-the-left-hand-side-argument-of-like-operator-using-sqlalchem and https://stackoverflow.com/questions/15165371/sqlalchemy-use-psqls-operator-when-string-is-on-left-and-column-on-right – Ilja Everilä Jun 30 '20 at 17:38
  • you can use the in_ operator directly the way you would in SQL (see https://stackoverflow.com/questions/8603088/sqlalchemy-in-clause) – Albert Albesa Jul 02 '20 at 09:10

0 Answers0