18

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...

nrhode
  • 913
  • 1
  • 9
  • 27
  • how wired seems to you changing field type to ArrayField containing JSONField? but on the other hand read this https://stackoverflow.com/a/41134445/953553 – andilabs Mar 14 '18 at 10:59

4 Answers4

20

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"}]))
Arount
  • 9,853
  • 1
  • 30
  • 43
TrungVK
  • 222
  • 2
  • 3
  • I know this is answered a long back, is the syntax same for startswith? as startswith seems like not working. Help pls. – Thebestshoot Jul 05 '19 at 14:37
  • All other answers on this page have minor syntax errors. This is the one that will work for this data structure. – ChidG Feb 12 '20 at 07:39
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.

Florian
  • 2,562
  • 5
  • 25
  • 35
iankit
  • 8,806
  • 10
  • 50
  • 56
1
myModel.objects.filter(tasks__contains=["task":"test"])
Girish Gupta
  • 1,241
  • 13
  • 27
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
  • 1,086
  • 15
  • 27
  • 1
    this is poor... the question is about using power of `postgres` rather than `python` ;) – andilabs Mar 14 '18 at 11:29
  • 2
    @andilabs read carefully. the question is regarding the django filter logic - which translates to postgres. and for everything that cannot be done with standard filter logic, you should resort to python, not to custom SQL, throwing the django ORM mapper away. The remark about postgres is here since the JSONField is only available in django on postgres databases – ingofreyer Mar 14 '18 at 14:05
  • have you heard about django's `Func` which allows you to annotate the queryset with arbitrary postgres function? After successful modifing of your data you can filter on that annotated field. And you are wrong also in respect to JSONField being limited to postgres. It is also available in MySQL via `django-mysql` see http://django-mysql.readthedocs.io/en/latest/model_fields/json_field.html – andilabs Mar 14 '18 at 14:15
  • 1
    @andilabs We are talking about pure django here, not addons. I am aware that addons may add further functionality. I also heard of `Func` but personally prefer not to use custom code for everything that a framework can do for me. But feel free to write your own answer with a solution using `Func` - I am writing my answers based on what to my experience works best. The answer works, is portable (in case someone decides to use a JSONField with another database, for example with your addon) and is readable. – ingofreyer Mar 14 '18 at 14:32
  • 1
    For those browsing this question more recently, JSONField docs can now be found at https://docs.djangoproject.com/en/4.0/topics/db/queries/#querying-jsonfield – VBobCat Jan 07 '22 at 15:29
  • Thank you @VBobCat - I updated my answer to contain the new link as well (and added an edit, since the previous link pointed to the postgres-specific JSONField and yours to the generally available one (starting Django 3.1) and its behaviour seems to be slightly better documented. – ingofreyer Jan 10 '22 at 06:29