Django JSONField and searching through the list of dictionaries using ILIKE

Question

Is it possible to search against one key value in the list of dictionaries using ILIKE (icontains) operator? My json field looks like this:

object = MyModel()
object.json_data = [
    {
        "type": 1,
        "results": [
            {
                "score": 1,
                "comment": "Some text comment 1",
            },
            {
                "score": 2,
                "comment": "Some text comment 2",
            },
            {
                "score": 3,
                "comment": "Some text comment 3",
            }
        ]
    },
    {
        "type": 2,
        "results": [
            {
                "score": 4,
                "comment": "Some text comment 4",
            },
            {
                "score": 5,
                "comment": "Some text comment 5",
            },
            {
                "score": 6,
                "comment": "Some text comment 6",
            }
        ]
    }
]
object.save()

And now, how to write the query to search in a "comment" key?

MyModel.objects.filter(json_data__??__results__??__comment__icontains="text comment")

I'm using Django 1.9.

Thanks!

You will have to write your own custom filter function to filter the results with a regular expression. What is the exact result that you need? queryset? list of matches? — Aviah Laor, Feb 20 '16 at 12:06

score 19 · Answer 1 · edited May 24 '18 at 19:33

19

this works for me (note the [])

query = User.objects.filter(data__campaigns__contains=[{'key': 'value'}])

edited May 24 '18 at 19:33

Brown Bear

19,655
10
58
76

answered May 12 '17 at 09:35

Theo

495
4
12

2

This is a workable solution except requirement of case insensitive. – iankit Aug 07 '18 at 14:46
I can't believe the solution was easy like this.... Thanks a lot... – allexiusw Dec 21 '21 at 02:16

T. Opletal · Answer 2 · 2016-02-20T19:12:36.063

15

You should be able to search simply by chaining it, django style:

MyModel.objects.filter(json_data__results__contains={"comment":"text comment"})

check out the documentation for JSON field in Django 1.9: https://docs.djangoproject.com/es/1.9/ref/contrib/postgres/fields/#querying-jsonfield

which includes contains lookup: https://docs.djangoproject.com/es/1.9/ref/contrib/postgres/fields/#std:fieldlookup-hstorefield.contains

If this doesn't work for case-insensitive, then I would see what query it produces, and simply rework it with extra where:

MyModel.objects.extra(where=["json_data->>'results'->'comment' ILIKE %s"], params=["%text comment%"])

or you can use the specific symbols for json as stated in postgres documentation, like <@

http://www.postgresql.org/docs/9.5/static/functions-json.html

edited Feb 20 '16 at 19:12

answered Feb 20 '16 at 18:13

T. Opletal

2,124
1
15
23

2

The thing is that I can't filter over all items in the list. When I specify the index = "json_data__0__results__0__comment__contains='Text'" everything works, but how to filter over all items in the list? – User Feb 20 '16 at 21:25
1

That seems really weird, it should definitely go over all the list values. Here the writer demonstrates it works for him... http://orokusaki.posthaven.com/djangos-new-jsonfield-is-awesome If something like `comment__contains='text'` doesn't work for you, then I suggest using the custom where, as I wrote in my answer. Or writing completely raw sql. – T. Opletal Feb 20 '16 at 22:08
Nope. Works: `Select * from xxx WHERE json_data::jsonb->0->'results'->0->'comment' = '"Text"';` ***** Does not Work: `Select * from xxx WHERE json_data::jsonb->'results'->'comment' = '"Text"';` ***** Works: `Select * from xxx WHERE json_data::jsonb #> ARRAY['0', 'results', '0', 'comment'] = '"Text"';` ***** Does not Work: `Select * from xxx WHERE json_data::jsonb #> ARRAY['results', 'comment'] = '"Text"';` ***** Does not Work: `Select * from xxx WHERE json_data::jsonb @> '{"comment": "Text"}'::jsonb;` ***** So I have to specify list index each time, :-( – User Feb 21 '16 at 17:38
1

Hm, I really don't understand this behavior. Here is another user suggesting the same approach which seems to be working http://stackoverflow.com/a/19868697/3221452. I'm really clueless why it searches only with indexes for you. Do you have the proper column type in your database? Check directly in database. – T. Opletal Feb 21 '16 at 17:44
Ok, there is no way to use a wilcard in the path -> http://stackoverflow.com/questions/21245997/is-there-a-way-to-address-all-elements-of-json-array-when-creating-a-constraint/21267504#21267504 .. So I'm getting ready to play with `jsonb_populate_recordset`, `jsonb_array_elements` etc. Thanks for your help. – User Feb 21 '16 at 18:00

Django JSONField and searching through the list of dictionaries using ILIKE

2 Answers2