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!

Answer 1

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