Django, JSONField, Postgres, and F() object comparison

Question

I am hoping to make a Django query by comparing two values within a JSONField class. I ran across Django F() Objects for references fields on the model, but it doesn't appear to work with JSONField as it tries to do a JOIN with the later section. So, for example:

class Event(models.Model):
    data = JSONField(default=None)

Let's assume the data field looks something like this:

{  
   "value_1":20,
   "value_2":25
}

I was hoping to query it like such:

events = Event.objects.filter(data__value_2__gte=F('data__value_1'))

However, the error is something like this:

Cannot resolve keyword 'value_1' into field. Join on 'data' not permitted.

Also have tried:

events = Event.objects.filter(data__value_2__gte=F('data')['value_1'])

But am given the error:

TypeError: 'F' object has no attribute '__getitem__'

Also; Django 1.10, Python 2.7.11, PG Version: 9.4.9

Any idea how to filter based on a comparison of value_1 and value_2?

This is just a guess, but try data__value_2__gte=F('data')['value_1']. — Sergey Gornostaev
– Sergey Gornostaev, Commented Nov 16, 2016 at 7:56
Didn't work : TypeError: 'F' object has no attribute 'getitem' — Donato Perconti
– Donato Perconti, Commented Nov 16, 2016 at 16:54

Falloutcoder · Accepted Answer · 2016-11-16 18:35:46Z

4

The way it looks, it can be said that F expressions doesnt support json field lookup. As you can see below the sql query generated for below django query

print Event.objects.filter(data__value_1=F('data')).query
SELECT "test_event"."id", "test_event"."data" FROM "test_event" WHERE "test_event"."data" -> 'value_1' >= ("test_event"."data")

In order for this to work on postgres, the query should be below:

SELECT "test_event"."id", "test_event"."data" FROM "test_event" WHERE "test_event"."data" -> 'value_1' >= "test_event"."data" -> 'value_2'

Whatever you try with F expression, it doesn't generate the format "test_mymodal"."data" -> 'value_2' for the expression.

edited Nov 16, 2016 at 18:35

answered Nov 16, 2016 at 18:27

Falloutcoder

1,0117 silver badges19 bronze badges

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

2 Comments

Donato Perconti Over a year ago

I ended up re-modeling my data structures so that I can use F objects. Regardless, this appears to be a correct explanation and workaround, though, I have not tested.

Искрен Станиславов Over a year ago

Did you try to CAST the JSON field? I successully managed to do my annotation and filter on it. It only required to CAST() try with something similar to my answer here stackoverflow.com/a/54805003/2848256

Collectives™ on Stack Overflow

Django, JSONField, Postgres, and F() object comparison

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related