How do I write a query in Django to filter queryset on the basis of JSON data.?

Question

I have a JSON field in my model which stores data like this:

{  "old_val": {"status": value1}, 
   "new_val": {"status": value2}
}

Now I want to refine my select query so that the result contains all those tuples whose JSON field has,

["new_val"]["status"] = value2 and ["old_val"]["status"] !=value1

how do I write this query in django. ???

Answer 1

This depends on what JSONField you are using and what database. Some of them just save json to text field. If this is the case when you can't access parts of data in the database and hence can't filter by it. If you are however using PostgreSQL 9.3+ than you can use its JSON support and its operators with extra:

Something.objects.extra(where=["data->'new_val'->>'status' = %s"], params=["foo"])

Note that PostgreSQL 9.4 has more operators than 9.3.

You may also take a look at django-pgjson, it encapsulates the use of some of postgresql json operators into custom lookups (new in Django 1.7):

Something.objects.filter(data__at_new_val__at_status="foo")

Answer 2

jsonField is basically a String. So, you have got to perform the queries that you would perform on any StringField.

needed_objects = YourModel.objects.filter(jsonfield__contains={"status": value2}).exclude(jsonfield__contains={"status": value1})

Hope this does the trick for you.