JSONField __in operator breaks when given a values_list()

[Jerome Leclanche]

Prerequisites

1. Create a model MyModel1 with the field resource = JSONField().
2. Create another model MyModel2 with a field id = CharField(max_length=255)

Reproduce by doing MyModel1.objects.filter(resource__foo__id__in=MyModel2.objects.all().values_list("id"))

Expected result: Should look at MyModel1 for objects with resource matching {"foo": {"id": <id>}}.

Actual result:

ERROR:  operator does not exist: jsonb = character varying
LINE 1: ...AND ("resource" #> '{foo,id}') IN (SELECT...

The actual issue is that when passing a values_list() to __in, the values list is not cast to jsonb (unlike when passing a text type). In the resulting sql, we can see IN (SELECT U0."id" AS Col1 FROM mymodel2 U0 ...). Wrapping to_json(U0."id") solves the issue.

[VCAMP]

​https://github.com/django/django/pull/10597

[Mariusz Felisiak]

With the new implementation of JSONField this issue is PostgreSQL-specific.

[Simon Charette]

Patch had a few issues notably

• It mutated a source expression during compilation
• It altered sql.Query.select and .annotations in a way that breaks referenceing (group_by, order_by)

I suggested an alternative approach using a subquery pushdown instead.