I worked out a solution that allowed me to get the behaviour I was looking
for, but instead of using func.array() I ended up using the
postgresql.dialect module and it's array class:
from sqlalchemy.dialects.postgresql import TIMESTAMP, array
@travel_dates.expression
def travel_dates(cls):
outbound = cls.itinerary.op('->')('outbound')
homebound = cls.itinerary.op('->')('homebound')
return array(
func.date_trunc('day',
cast(outbound.op('->')(0).op('->>')('arrival'), TIMESTAMP)),
func.date_trunc('day',
cast(outbound.op('->')(0).op('->>')('departure'), TIMESTAMP)),
func.date_trunc('day',
cast(outbound.op('->')(1).op('->>')('arrival'), TIMESTAMP)),
func.date_trunc('day',
cast(outbound.op('->')(1).op('->>')('departure'), TIMESTAMP)),
...
)
This then allows me to execute queries based upon the contents of this
array:
session.query(Itinerary).filter(Itinerary.travel_dates.any('2017-01-01')).all()
If anyone knows of a more elegant way to iterate over the nested structure
I would still appreciate feedback.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
