Opened 8 years ago

Closed 8 years ago

Last modified 4 years ago

#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)

comment:1 by Tim Graham, 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

Is the discussion from #26511 relevant here? I'd at least call it a bug that a lookup is available that crashes.

in reply to:  1 comment:2 by Nick Stefan, 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 Simon Charette, 8 years ago

Keywords: QuerySet.extra removed
Owner: changed from nobody to Simon Charette
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:4 by Simon Charette, 8 years ago

Has patch: set

​PR

comment:5 by Tim Graham, 8 years ago

Triage Stage: Accepted → Ready for checkin

comment:6 by Simon Charette <charette.s@…>, 8 years ago

Resolution: → fixed
Status: assigned → closed

In cecef94:

Fixed #27257 -- Fixed builtin text lookups on JSONField keys.

Thanks Nick Stefan for the report and Tim for the review.

comment:7 by Simon Charette <charette.s@…>, 8 years ago

In d9767602:

Fixed #27693, Refs #27257 -- Fixed iexact lookup on JSONField keys.

Thanks Harris Lapiroff for the report.

Version 0, edited 8 years ago by Simon Charette <charette.s@…> (next)

comment:8 by Alym, 4 years ago

Problem repeats in django 3

comment:9 by Mariusz Felisiak, 4 years ago

Alym, please don't edit old comments. If you want to report any regression in Django 3+, create a new ticket.

Note: See TracTickets for help on using tickets.
Back to Top