2

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?

Community
  • 1
  • 1
Quy Tang
  • 3,929
  • 1
  • 31
  • 45
  • I've just tried the exact code above and it works just fine for me in Postgresql. Are you using Postgresql? – solarissmoke Oct 27 '17 at 11:59
  • @solarissmoke it was my mistake. I've just updated the question – Quy Tang Oct 27 '17 at 12:17
  • Your snippet for the model has an error: it should be `tags = JSONField()` instead of `tags: JSONField()`. – cezar Oct 27 '17 at 12:28
  • thanks @cezar, just updated. It's my pseudo code. – Quy Tang Oct 27 '17 at 12:32
  • 1
    Maybe you could implement validation that enforces always all caps terms in the JSONField. That would simplify the things, if the data format is consistent. – cezar Oct 27 '17 at 12:43
  • 2
    The official documentation for `JSONField()` talks only about key-value pairs. However I would try the [values lookup](https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/fields/#arrayfield). There are also the [`ArrayField()`](https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/fields/#arrayfield) examples (for only postgresql) that discuss tags. It does not mention `icontains` though. I can only hope that `icontains` works too. – Ravi Teja Oct 27 '17 at 14:04
  • What version of Django are you using. In Django 1.11 you can simply do `Q(tags__icontains='Tech')` and it works. This – solarissmoke Oct 28 '17 at 05:08
  • I used Django 1.10. Btw, the owner of `django-mysql` confirmed it didn't support icontains yet. – Quy Tang Oct 28 '17 at 07:32

2 Answers2

1

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.

cezar
  • 11,616
  • 6
  • 48
  • 84
  • I just checked but it threw this exception `Lookup 'icontains' doesn't work with JSONField`. btw, thanks for the suggestion. – Quy Tang Oct 27 '17 at 12:30
  • Yes, you're write. I didn't pay attention. That's really annoying! – cezar Oct 27 '17 at 12:33
0

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

Adam Johnson
  • 471
  • 1
  • 5
  • 11