Re: JsonField icontains filter generates invalid SQL

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

Le dimanche 9 avril 2017 21:48:57 UTC-4, cofiem a écrit :

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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a4cab7dd-027d-4eea-b55c-55348b24b78e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.