norbertpy norbertpy - 1 month ago 15
Python Question

sqlalchemy using INTERSECT and UNNEST

I'm trying to translate a raw SQL to sqlalchemy core/orm but I'm having some difficulties. Here is the SQL:

SELECT
(SELECT UNNEST(MyTable.my_array_column)
INTERSECT
SELECT UNNEST(ARRAY['VAL1', 'VAL2']::varchar[])) AS matched
FROM
MyTable
WHERE
my_array_column && ARRAY['VAL1', 'VAL2']::varchar[];


The following query, gives me a
FROM
clause which I don't need in my nested
SELECT
:

matched = select([func.unnest(MyTable.my_array_column)]).intersect(select([func.unnest('VAL1', 'VAL2')]))

# SELECT unnest(MyTable.my_array_colum) AS unnest_1
# FROM MyTable INTERSECT SELECT unnest(%(unnest_3)s, %(unnest_4)s) AS unnest_2


How can I tell the
select
to not include the
FROM
clause? Note that
func.unnest()
only accepts a column. So I cannot use
func.unnest('my_array_column')
.

Answer

Referring to a table of an enclosing query in a subquery is the process of correlation, which SQLAlchemy attempts to do automatically. In this case, it doesn't quite work, I believe, because your INTERSECT query is a "selectable", not a scalar value, which SQLAlchemy attempts to put in the FROM list instead of the SELECT list.

The solution is twofold. We need to make SQLAlchemy put the INTERSECT query in the SELECT list by applying a label, and make it correlate MyTable correctly:

select([
    select([func.unnest(MyTable.my_array_column)]).correlate(MyTable)
    .intersect(select([func.unnest('VAL1', 'VAL2')]))
    .label("matched")
]).select_from(MyTable)

# SELECT (SELECT unnest("MyTable".my_array_column) AS unnest_1 INTERSECT SELECT unnest(%(unnest_3)s, %(unnest_4)s) AS unnest_2) AS matched 
# FROM "MyTable"