Opened 4 years ago

Closed 4 years ago

Last modified 3 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: master
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 (7)

comment:1 Changed 4 years ago by Tim Graham

Component: Database layer (models, ORM)contrib.postgres
Easy pickings: unset
Summary: JSONB regex and iregexregex and iregex lookups crash on JSONField
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

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

comment:2 in reply to:  1 Changed 4 years ago by Nick Stefan

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 Changed 4 years ago by Simon Charette

Keywords: QuerySet.extra removed
Owner: changed from nobody to Simon Charette
Status: newassigned
Summary: regex and iregex lookups crash on JSONFieldUse the ->> operator when filtering builtin text lookups on JSONField keys
Version: 1.9master

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 Changed 4 years ago by Simon Charette

Has patch: set

comment:5 Changed 4 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:6 Changed 4 years ago by Simon Charette <charette.s@…>

Resolution: fixed
Status: assignedclosed

In cecef94:

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

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

comment:7 Changed 3 years ago by Simon Charette <charette.s@…>

In d9767602:

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

Thanks Harris Lapiroff for the report.

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