Type conversion should be possible with OuterRef

[Étienne Beaulé]

With the query:

subquery = SentMessageRecord.objects.filter(tournament=self.tournament, email=OuterRef('email'), context__key=OuterRef('url_key'), event=SentMessageRecord.EVENT_TYPE_URL)
people = self.tournament.participants.filter(url_key__isnull=False, email__isnull=False).exclude(email__exact="").annotate(already_sent=Exists(subquery)).filter(already_sent=already_sent)

where url_key is a CharField and context is a JsonField (using Postgres), the __key is a string that corresponds to url_key. However, I get the error:

django.db.utils.ProgrammingError: operator does not exist: jsonb = character varying
LINE 1: ...tmessagerecord" U0 WHERE ((U0."context" -> 'key') = ("partic...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Wrapping the OuterRef with str() prevents the error, but seems to be ineffective.

[Simon Charette]

Hey tienne-B,

Unfortunately it's not possible for Django, nor PostgreSQL, to automatically determine that context__key ("context" -> 'key') is a string by the untyped and dynamic nature of the jsonb type. From Django's perspective context__key is a JSONField and from PostgreSQL it is jsonb.

This is the case because it's not possible to determine whether url_key should be converted to jsonb or "context" -> 'key' to text so it is necessary to provide an explicit cast as PostgreSQL suggests. I'd say that here both Django and PostgreSQL refuse the temptation to guess.

Unfortunately calling str on OuterRef won't work as the Django ORM doesn't implement this kind of high level magic like ORM such as Peewee provide. In these cases you'll have to rely on either a Cast ​function or KeyTextTransform annotations instead.

For example using Cast.

subquery = SentMessageRecord.objects.annotate(
    context_key=Cast('context__key', models.CharField()),
).filter(
    tournament=self.tournament,
    email=OuterRef('email'),
    context_key=OuterRef('url_key'),
    event=SentMessageRecord.EVENT_TYPE_URL,
)

Given this isn't a defect with Django I'd say this would qualify as a usage question and would better be addressed through support channels. Without deterring you from reporting legitimate bugs it's always better to confirm whether or not an issue can be resolved on support channels before escalating to this tracker which is used to track defect and feature requests.