How to filter JSON Array in Django JSONField

Question

i am getting crazy with filtering a (postgres) JSONField in Django 2.0.3. The json is stored as an array. E.g.

tasks = [{"task":"test","level":"10"},{"task":"test 123","level":"20"}]

What i've tried:

myModel.objects.filter("tasks__task__contains"="test")
myModel.objects.filter("tasks_task__0__contains"="test")
myModel.objects.filter("tasks__0__task__contains"="test")
myModel.objects.filter("tasks_task__0_x__contains"="test")
myModel.objects.filter("tasks__0_x__task__contains"="test")

What goes wrong? What i want to do is a icontains - but as i already read there is not support for icontains on jsonfields in Django right now...

Answer 1

The right answer should be:

myModel.objects.filter(tasks__contains=[{"task":"test"}])

You may want to add more filters to narrow down and speed up the query if needed, something like

myModel.objects.filter(Q(tasks_level=10, tasks__contains=[{"task":"test"}]))

Answer 2

The contains keyword in filter is very powerful. You can use the following command to filter out rows in MyModel from any of your fields in the array of dictionaries in the Jsonb column type.

MyModel.objects.filter(tasks__contains=[{"task":"test"}])

This is the most ORM friendly solution I have found to work here, without the case insensitive approach. For case insentitive, as you rightly said, Django does not have icontains for json, use

MyModel.objects.extra("") for that by inserting the SQL query for "ILIKE" operator in postgres.

Answer 3

myModel.objects.filter(tasks__contains=["task":"test"])