JsonField icontains filter generates invalid SQL

[cofiem]

The issue I'm facing is similar to these tickets:

• Case insensitive lookups on JSONField nested values https://code.djangoproject.com/ticket/27693
• Use the ->> operator when filtering builtin text lookups on JSONField keys https://code.djangoproject.com/ticket/27257
• Document how to do a substring search in JSONField https://code.djangoproject.com/ticket/26511

I have a JsonField in a model

data = JSONField(null=True, blank=True)

It contains this json:

{"name":"Hello"}

My filter is:

models.MyModel.objects.filter(data__name__icontains='el')

I expect the query to return the objects that have json where the "name" contains 'el' (case-insensitive).

Instead, I get this:

ProgrammingError at /url/
function upper(jsonb) does not exist LINE 1: ... UPPER("my_model"."data"... 
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The SQL that causes the error is

 WHERE UPPER(("my_model"."data" -> 'name')::text) LIKE UPPER(%el%)

The working SQL is (note added quotes around LIKE text):

WHERE UPPER(("my_model"."data" -> 'name')::text) LIKE UPPER('%el%')

Is there a fix for this? Am I missing something?

Any help would be appreciated.

[Simon Charette]

Hello there,

I'm not sure where you determined the SQL that caused the error but I'm pretty

sure this was caused by "my_model"."data" -> 'name' not being wrapped in a

parenthesis before the ::text cast.

This has been fixed in Django 1.11[0].

Cheers,

Simon

[0] https://github.com/django/django/commit/2eb7d6e6d41480f21305fc6abe2f1a443491ddec#diff-57ceaae67721b3f8d5729222c032af8eR109