Select Condition based on UNNEST

[aczire]

I was trying to select rows from a BigQuery view based on a condition which is inside an array of structures,
Below is the SQL query

        SELECT id, enId, versionId, code_coding
        FROM `some.schema_views.table`, UNNEST(code_coding) AS cc
        WHERE (cc.code, cc.display) = ('H', 'BLAST')
        LIMIT 100

Below is my failed attempt to write the same in SQLAlchemy

        from sqlalchemy.sql.expression import cast
        from sqlalchemy import String, func
        code_coding = db.query(func.unnest(self.model.code_coding)).subquery()
        conditions.append(
            and_(cast(code_coding.c.code, String) == "H", cast(code_coding.c.display, String) == "BLAST")
        )

It says c.code is not found. Any idea? Thanks ahead!

[kiraksi]

@aczire The core problem seems to stem from how SQLAlchemy ORM constructs handles structures within arrays and how it references columns after UNNEST operations. I have been looking into unnest while working on a separate issue and from my limited knowledge, it might be because it needs an additional table alias assigned to the subquery because SQLAlchemy needs additional clarity about how to reference columns? It seems like adding that alias will solve your problem, but there are workarounds mentioned here for example. Can you try that and let me know if it still doesn't work and I will look into it.

[aczire]

Thanks @kiraksi

I tried adding the alias, code_coding = db.query(func.unnest(self.model.code_coding).alias('cc')).subquery()

But that still didnt work. Still got the KeyError, which means the unnest'd columns are still not in c.

Any idea?

Opened a discussion on the same here as well,
sqlalchemy/sqlalchemy#10779

[kiraksi]

I plan on taking a look at this issue next week. I see the sqlalchemy team had a suggested fix, @aczire did that work for you?

[aczire]

Thanks @kiraksi
I couldn't get it done, so went with filtering it in the business logic instead of filtering it during the query phase. Any help is much appreciated!