[Fixed]-How to filter JSON Array in Django JSONField

by

22👍

✅

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"}]))
👤TrungVK

6👍

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.

👤iankit

1👍

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

0👍

I see two problems here.

  1. The Django filter options are there to filter for Django objects, not objects within a field. You could definitely filter for an object that contains a task "test" but you cannot filter for the specific task within the JSONField in the object (you need to first retrieve the content of the django object and then query in an additional step)

  2. As far as I understand the django documentation on JSONField, the contains operator only checks for keys in a dictionary or elements in a list. Appending it to a lookup query in hope that it compares a value like I understand your examples will thus not work. However, it is possible to query a dictionary with contains. In your case, this should work for querying the django object:

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

If you are only interested in the one dictionary and not the others, you will need to expand this query by afterwards extracting the correct object:

matching_objects = myModel.objects.filter(tasks__contains={"task": "test"})
for matching_object in matching_objects:
    for matching_task in [task for task in matching_object.tasks if "task" in task and task["task"] == "test" ]:
        print "found task", matching_task

See also this related stackoverflow answer for lookups in JSONFields with contains.

Update: Django versions 3.1+

Later Django versions (3.1+) have a generally available JSONField.
This field is not purely bound to Postgres anymore. Instead, it works (according to the Django documentation for version 4.0) with

MariaDB 10.2.7+, MySQL 5.7.8+, Oracle, PostgreSQL, and SQLite (with the JSON1 extension enabled)

The contains operator will check for matching key/value pairs on the root level of the dictionary here. Still, it would not pick up test 123 as the question asked for.

👤ingofreyer

Leave a comment