#27257 closed Bug (fixed)
Use the ->> operator when filtering builtin text lookups on JSONField keys
Reported by: | Nick Stefan | Owned by: | Simon Charette |
---|---|---|---|
Component: | contrib.postgres | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Given a model with a jsonb field, 'content', and it has a key called 'altText'.
queryset.filter(content__altText__iregex="bob")
creates this SQL
"content" -> 'altText'::text ~* 'bob'
Which creates a type error
ERROR: operator does not exist: jsonb ~* unknown at character 2792 postgres | HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL actually needed is
content ->> altText ~* 'bob'
So I had to use:
queryset.extra(where=['content ->> %s ~* %s'], params=['altText', 'bob'])
Is this something to be filed under QuerySet.extra or is this more of a bug just with JSONField ?
Change History (9)
follow-up: 2 comment:1 by , 8 years ago
Component: | Database layer (models, ORM) → contrib.postgres |
---|---|
Easy pickings: | unset |
Summary: | JSONB regex and iregex → regex and iregex lookups crash on JSONField |
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → Bug |
comment:2 by , 8 years ago
Replying to Tim Graham:
Is the discussion from #26511 relevant here? I'd at least call it a bug that a lookup is available that crashes.
Yes! That other discussion is relevant. Especially this comment: https://code.djangoproject.com/ticket/26511#comment:10
The error I got was distinct, but the needed solution is similar.
comment:3 by , 8 years ago
Keywords: | QuerySet.extra removed |
---|---|
Owner: | changed from | to
Status: | new → assigned |
Summary: | regex and iregex lookups crash on JSONField → Use the ->> operator when filtering builtin text lookups on JSONField keys |
Version: | 1.9 → master |
The operator precedence crash was fixed by 2eb7d6e6d41480f21305fc6abe2f1a443491ddec but we should really be using the ->>
for text lookups as ::text
will use the text representation of the accessed key.
comment:5 by , 8 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:9 by , 4 years ago
Alym, please don't edit old comments. If you want to report any regression in Django 3+, create a new ticket.
Is the discussion from #26511 relevant here? I'd at least call it a bug that a lookup is available that crashes.