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.

[Antoliny]

Attempting to assign to Pycon Korea Sprints to make it easier to reserve tickets. Pycon Korea ends August 17, this is available for someone else to pick up on the 18th.