Opened 4 years ago

Closed 4 years ago

#27205 closed New feature (duplicate)

Allow doing a substring search in JSONField array

Reported by: Dean-Christian-Armada Owned by: nobody
Component: contrib.postgres Version: 1.10
Severity: Normal Keywords: postgresql, arrayfield
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

This is my models.py:

class Dog(models.Model):
    name = models.CharField(max_length=200)
    data = JSONField()

    def __unicode__(self):
        return self.name

I did this in the django shell:

Dog.objects.create(name='Rufus', data={ 'breed': 'labrador', 'owner': { 'name': 'Bob', 'other_pets': [{  'name': 'Fishy',  }], }, })
Dog.objects.create(name='Meg', data={'breed': 'collie'})
Dog.objects.filter(data__breed__contains='l')

However when I did the last command it gave me an empy queryset return:

<QuerySet []>

The two objects (Meg and Rufus) should have both returned because they both contain l

This is the query of the ORM:

SELECT "post_tagging_dog"."id", "post_tagging_dog"."name", "post_tagging_dog"."data" FROM "post_tagging_dog" WHERE "post_tagging_dog"."data" -> 'breed' @> '"l"'

Change History (4)

comment:1 Changed 4 years ago by Tim Graham

I guess may be similar to #26511.

comment:2 Changed 4 years ago by Simon Charette

I guess may be similar to #26511.

It's the same underlying issue as #26511.

What we'd need here is a way to express we want the text value of a key (using the ->> operator instead of ->). Ideally the __contains lookup wouldn't be overloaded for containment (@>) for the JSONField and we'd define a special lookup that takes care of using the correct operator but it's too late now.

#26511 suggests introducing a transform to deal with this issue but at this point I can't think of a way to expose it as a lookup (e.g data__breed__text__contains='collie') since these fields are completely unstructured and could include a key conflicting with the chosen lookup name (e.g. {'data': {'breed': {'text': 'terrier'}}}).

If we were to introduce this transform the reported queryset could be rewritten as:

Dog.objects.annotate(breed=KeyTextTransform('breed', 'data')).filter(breed__contains='l')

Edit: By the way, if we were to document the KeyTransform and KeyTextTransform APIs I'd suggest they allow multiple keys to be provided and their ordering reversed (e.g. KeyTransform('data', 'nested', 'deep')).

Last edited 4 years ago by Simon Charette (previous) (diff)

comment:3 Changed 4 years ago by Tim Graham

Component: Database layer (models, ORM)contrib.postgres
Summary: Django Postgres ArrayField __contain lookup does not behave expectedlyAllow doing a substring search in ArrayField
Triage Stage: UnreviewedAccepted
Type: UncategorizedNew feature

comment:4 Changed 4 years ago by Simon Charette

Resolution: duplicate
Status: newclosed
Summary: Allow doing a substring search in ArrayFieldAllow doing a substring search in JSONField array

I'll close as duplicate of #26511 as this ticket has nothing to do with ArrayField; it's asking about allowing to perform a search against a JSONField array which is the same underlying issue as the aforementioned ticked.

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