How to select Postgresql record based on calculated json value with Sqlalchemy?

Question

I have a postgres table with 2 columns that I use in a query:

start_date_detail: date 
flex: jsonb

Example of the flex field:

{
    "communication": [
        {
            "remind_on": "[email protected]",
            "type": "email",
            "remind_date": -150
        },
        {
            "remind_on": "+31612345678",
            "type": "sms",
            "remind_date": -360
        }
    ]
}

I need to select all records that have a remind date that lies in the last week, so (pseudo code):

(now() - 1-week) < (start_date_detail + remind_date) < now()

How can I realise that with sqlalchemy?

Because it's a calculated value in the query, I don't know how to do this. In postgres I cam up with this, and that works:

SELECT * FROM time_item 
WHERE 
    (start_date_detail + INTERVAL '1 second' * (flex->'communication'->0->>'remind_date')::numeric <= NOW())
    OR (start_date_detail + INTERVAL '1 second' * (flex->'communication'->1->>'remind_date')::numeric <= NOW())

How to put this in sqlalchemy?

One more thing: In the above query I add every communication item to the where clause. How can I make this more flexible? That is that I don't need to put a where clause for every communication item.

Ilja Everilä · Accepted Answer · 2018-07-11 18:33:36Z

2

You could use jsonb_array_elements() to expand the array to a set of jsonb elements that you could then use in predicates. SQLAlchemy supports function expressions as selectables through use of alias. Using a model such as

In [4]: class TimeItem(Base):
   ...:     __tablename__ = 'time_item'
   ...:     id = Column(Integer, primary_key=True)
   ...:     start_date_detail = Column(Date)
   ...:     flex = Column(JSONB)
   ...:

the query could look like

In [39]: session.query(TimeItem).\
    ...:     select_from(TimeItem,
    ...:                 func.jsonb_array_elements(TimeItem.flex['communication']).
    ...:                     alias('comm')).\
    ...:     filter((TimeItem.start_date_detail +
    ...:             timedelta(seconds=1) *
    ...:             column('comm', type_=JSONB)['remind_date'].
    ...:                 astext.
    ...:                 cast(Integer)).
    ...:                     between(func.now() - timedelta(weeks=1),
    ...:                             func.now())).\
    ...:     all()

You can then tweak the predicates to suit your needs – I've tried to follow your examples in that remind_date is interpreted as offset seconds to start_date_detail, compared between a week preceding now and now. When querying entities such as TimeItem SQLAlchemy does its own deduplication based on object identities, so the query can omit SQL side DISTINCT, moving the array elements in an EXISTS subquery expression, or such.

edited Jul 11, 2018 at 18:33

answered Jul 11, 2018 at 16:38

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

edbras Over a year ago

Very nice, thank you very much, give me a few days to use it and I will come back to it, but I think I understand what is going on

edbras Over a year ago

The query works perfect, thanks a lot. I still find it hard to master the JSON query materie. Would you advice put an index on the start_date_detail and remind_date? And would this look like?

Ilja Everilä Over a year ago

Unfortunately you cannot index an expression that uses derived values from set returning functions, such as jsonb_array_elements(). If there's there are known limits to start_date_detail and remind_date combination, you could use a range on start_date_detail as a "coarse" predicate that's indexable.

Collectives™ on Stack Overflow

How to select Postgresql record based on calculated json value with Sqlalchemy?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related