Allow doing a substring search in JSONField array

[Dean-Christian-Armada]

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"'

[Tim Graham]

I guess may be similar to #26511.

[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')

[Simon Charette]

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.