CODEWITHSUNDEEP
djangodjango-mysql
Quy Tang
Quy Tang

Reputation: 3979

Filter on JSONField array

I have a book model

class Book():
  ...
  tags=JSONField()

I have some records:

Book(..., tags=['TECH', 'BUSINESS'])
Book(..., tags=['MARKETING'])

I want to filter out the books that have tag 'Tech' or 'Business'

query = Q (
    Q(tags__contains='Tech') |
    Q(tags__contains='Business')
)

I've tried to use contains, contained_by, has_key, has_any_keys but got no luck. The result is always empty.

Update

It was my mistake! I found the problem, JSONField is case sensitive.

The values saved in DB were ["TECH", "BUSINESS"] instead of ["Tech", "Business"].

Now the question turns out How to search in a case-insensitive manner?

Upvotes: 2

Views: 1837

Answers (2)

Adam Johnson
Adam Johnson

Reputation: 531

I've replied on your issue on the Django-MySQL repo: https://github.com/adamchainz/django-mysql/issues/401 . Basically it seems this can't be done as MySQL JSON values are case sensitive only, as per https://dev.mysql.com/doc/refman/5.7/en/json.html

Upvotes: 0

cezar
cezar

Reputation: 12012

Case insensitive search can be performed with icontains:

query = Q(
    Q(tags__icontains='tech') |
    Q(tags__icontains='business')
)

Here is a link to the official documentation.

Upvotes: 1

Related Questions