Hello everyone, I've wrestling with a problem to do with hybrid queries and
Postgres that I'm hoping you can help me with.
I'm trying to express the concept of 'element in list' for SQL using hybrid
properties. The data I'm using is a nested JSON object like this:
{
"itinerary": {
"outbound": [
{
"arrival": "2016-12-27T19:20:00",
"departure": "2016-12-27T14:40:00",
},
{
"arrival": "2016-12-27T22:50:00",
"departure": "2016-12-27T21:40:00",
},
],
"homebound": [
{
"arrival": "2017-01-03T22:15:00",
"departure": "2017-01-03T20:15:00",
},
{
"arrival": "2017-01-04T10:50:00",
"departure": "2017-01-04T08:35:00",
}
]
}
}
This data is stored in a JSONB column, which allows me to access the
elements using PostgreSQL's JSON notation quite easily.
What I'm trying to do is generate a list of dates of travel, as represented
by the "itinerary" fields. In Python this is easily done, simply by
iterating across the dict representation of the decoded JSON object, like
this:
dates = set()
for direction in ('outbound', 'homebound'):
for leg in itinerary[direction]:
dates.add(leg['arrival'])
dates.add(leg['departure'])
I'm then able to normalise the dates and extract the date of travel so that
I can do "'2017-01-04' in dates".
I can't work out how to express this in SQLAlchemy though, and have ended
up with a mess of this:
@travel_dates.expression
def travel_dates(cls):
outbound = cls.itinerary.op('->')('outbound')
homebound = cls.itinerary.op('->')('homebound')
return func.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)),
...
)
Now I can't get this to work at all, and was hoping someone could point out
some suggestions for a better approach to this. I'm not even sure if this
is a sensible way to evaluate nested JSONB types like this, but I've not
been able to find anything that is cleaner (or works for that matter).
Yours tentatively.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
