9

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?

Donato Perconti
  • 814
  • 2
  • 11
  • 28

1 Answers1

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.

Falloutcoder
  • 991
  • 6
  • 19
  • 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. – Donato Perconti Nov 22 '16 at 06:37
  • 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 https://stackoverflow.com/a/54805003/2848256 – Искрен Станиславов Mar 05 '19 at 13:16