CODEWITHSUNDEEP
pythondjangopostgresql
Donato Perconti
Donato Perconti

Reputation: 813

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

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?

Upvotes: 9

Views: 2528

Answers (1)

Falloutcoder
Falloutcoder

Reputation: 1011

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.

Upvotes: 4

Related Questions