20

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

Improve this question
1
  • how wired seems to you changing field type to ArrayField containing JSONField? but on the other hand read this stackoverflow.com/a/41134445/953553 Commented Mar 14, 2018 at 10:59

4 Answers 4

Reset to default
24

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"}]))
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I know this is answered a long back, is the syntax same for startswith? as startswith seems like not working. Help pls.
All other answers on this page have minor syntax errors. This is the one that will work for this data structure.
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.

Improve this answer

Comments

1 
myModel.objects.filter(tasks__contains=["task":"test"])
Improve this answer

Comments

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.

Improve this answer

6 Comments

this is poor... the question is about using power of postgres rather than python ;)
@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
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 django-mysql.readthedocs.io/en/latest/model_fields/…
@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.
For those browsing this question more recently, JSONField docs can now be found at docs.djangoproject.com/en/4.0/topics/db/queries/…
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.