SQLAlchemy Join using PyBigquery to filter results

Question

Using a SQLAlchemy class, I'm trying to generate a query that resembles

SELECT
  DISTINCT(non_unique_key)
FROM
  `tablename`,
  UNNEST(tasks_dns) AS dns
WHERE
  create_date_utc = TIMESTAMP("2020-12-31T23:59:59")
  AND dns LIKE "%whatever%"

Being an implicit join using unnest(), I don't have a clue how to construct my statement.

Using a combination of .label() and moving the unnest() call around, I've managed to move the unnest clause to either the SELECT or WHERE clauses, but not in the FROM.

For example,

session.query(Table.non_unique_key).filter(func.unnest(Table.dns) != '').filter(Table.create_date == "2021-04-22")

leaves me with

SELECT `tablename`.`non_unique_key` AS `tablename_non_unique_key` 
FROM `tablename` 
WHERE unnest(`tablename`.`tasks_dns`) IS NOT NULL AND `tablename`.`create_date_utc` = %(create_date_utc_1)s

So far, using join() has just caused exceptions around not having a column to join on (which while yes, I understand what that means, I'm not sure how to get around that since an unnest is basically doing an expansion of a nested data type that doesn't have a column to join on.. which is probably where my ignorance around how to properly use SQLAlchemys join() method comes in)

Is this just a SQLAlchemy / BigQuery dialect issue at this point? Or am I just a dunce? I know the dialect library is still infant, but even with Postgres, I would have thought that this should be a somewhat common query pattern?

Answer 1

After some additional digging, I've figured it out

Model().query().select_from(func.unnest(Model.col1).alias("whatever")).filter()....