Bug: error when trying to filter using regex/iregex on a key in a django.contrib.postgres.fields.JSONField

[jrhouston]

When trying use regex to query on a key within a postgres JSONField.

TestModel.objects.filter(field__subkey__regex=r'test')

Generates SQL which produces the following error:

LINE 1: ...WHERE "test_testmodel"."field" -> 'subkey'::text ~ 'test'
                                                                                                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

This seems to be a precedence issue, and adding parenthesis before the extracted key is cast to ::text allows the query to run successfully. i.e:

WHERE ("test_testmodel"."field" -> 'subkey')::text ~ 'test'

Adding the parenthesis here seems to fix the issue: ​https://github.com/django/django/blob/master/django/contrib/postgres/fields/jsonb.py#L109

[DavidFozo]

Someone wrote a patch, before I did.

[Simon Charette]

While working on #26511 I also noticed we should wrap the key access expression ​in parentheses.

@jrhouston you might be interested to know that (field -> key)::text is going to get you the text representation of the JSON key you're accessing ('(('{"foo": "bar"}'::jsonb) -> 'bar')::text will get you '"bar"'. What you want to use here is the ->> operator (('{"foo": "bar"}'::jsonb) ->> 'bar' == 'bar').

I'm not sure how we can teach the ORM to use ->> in a backward compatible way at this point, we might have to introduce a new expression for this purpose like I suggested in #26511.

In the mean time I propose ​we add these parentheses but it would be great to have regression tests not relying on built-in lookups meant to be only available on CharField or TextField as their removal or replacement are tracked in another ticket if I'm not mistaken.

[jrhouston]

@charettes in your last comment are you referring to the use of the regex/iregex lookup used in the pull request, and if so could you suggest an alternative lookup that I could use to test this?

[Simon Charette]

@jrhouston, the following should do:

from django.contrib.postgres.fields.jsonb import KeyTransform
from django.db.functions import Cast

instance = JSONModel.objects.create(field={'foo': 123})
foo = JSONModel.objects.annotate(
    foo=Cast(KeyTransform('foo', 'field'), models.IntegerField())
).values_list('foo', flat=True).get(pk=instance.pk)
self.assertEqual(foo, 123)

[Tim Graham]

A related ticket is #26908. My ​PR for that ticket also adds the parentheses.

[Simon Charette]

I think we can close as duplicate is this case. The proposed tests don't add much value to the suite.