Opened 7 years ago

Last modified 20 months ago

#28872 new Bug

JSONField __in operator breaks when given a values_list()

Reported by: Jerome Leclanche Owned by:
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: VCAMP Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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.

Change History (8)

comment:1 by Jerome Leclanche, 7 years ago

Description: modified (diff)

comment:2 by Tim Graham, 7 years ago

Description: modified (diff)
Triage Stage: Unreviewed → Accepted

comment:3 by VCAMP, 6 years ago

Owner: set to VCAMP
Status: new → assigned

comment:4 by VCAMP, 6 years ago

Cc: VCAMP added
Has patch: set

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

Last edited 6 years ago by VCAMP (previous) (diff)

comment:5 by Tim Graham, 6 years ago

Patch needs improvement: set

comment:6 by Mariusz Felisiak, 4 years ago

Version: 1.11 → master

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

comment:7 by Mariusz Felisiak, 4 years ago

Component: contrib.postgres → Database layer (models, ORM)

comment:8 by Mariusz Felisiak, 20 months ago

Owner: VCAMP removed
Status: assigned → new
Note: See TracTickets for help on using tickets.
Back to Top