#32411: Case-insensitive lookups on JSONField doesn't work on MySQL.
-------------------------------------+-------------------------------------
Reporter: elonzh | Owner: Hasan
Type: | Ramezani
Cleanup/optimization | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: JSONField mysql | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by elonzh):
Replying to [comment:2 Carlton Gibson]:
> Hi. Thanks for the report.
>
> Your usage is wrong for what you're trying to do. See the
[https://docs.djangoproject.com/en/3.2/topics/db/queries/#querying-
jsonfield Querying JSONField topic docs].
>
> For JSONField `contains` takes key pairs, rather than a string:
>
> {{{
> >>> Dog.objects.filter(data__contains={'owner': 'Bob'})
> }}}
>
> `icontains` is not explicitly implemented on the field itself, so it's
falling back to the base implementation (wrongly — the
[https://database.guide/json_unquote-remove-quotes-from-a-json-document-
in-mysql/ JSON_UNQUOTE] gives us a string but we didn't generate the
`LOWER`) **but** it's not clear that makes much sense. (Mariusz: I'm
closing this as invalid, but do we want to accept in order to raise an
error here? 🤔)
>
> As you've discovered the correct usage is to query by field
([https://docs.djangoproject.com/en/3.1/topics/db/queries/#key-index-and-
path-transforms as per here]):
>
> {{{
> >>> Dog.objects.filter(data__owner__name__icontains='bob')
> }}}
>
> For your example
`Paper.objects.filter(authors__name__icontains="rehmann")` should give you
what you want.
> I hope that helps.
>
> This seems related to the documentation issue #26511.
Thanks for your information, I know how to query field in a json object,
but the problem is the `authors` fields is a list and Django does not
support filter it.
`Paper.objects.filter(authors__name__icontains="rehmann")` won't give me
what I want.
{{{
>>> print(Paper.objects.filter(authors__name__icontains="rehmann").query)
SELECT `scholardata_paper`.`id`, `scholardata_paper`.`created`,
`scholardata_paper`.`modified`, `scholardata_paper`.`doi`,
`scholardata_paper`.`s2_id`, `scholardata_paper`.`wanfang_id`,
`scholardata_paper`.`corpus_id`, `scholardata_paper`.`pm_id`,
`scholardata_paper`.`arxiv_id`, `scholardata_paper`.`acl_id`,
`scholardata_paper`.`mag_id`, `scholardata_paper`.`language`,
`scholardata_paper`.`url`, `scholardata_paper`.`abstract`,
`scholardata_paper`.`title`, `scholardata_paper`.`authors`,
`scholardata_paper`.`keywords`, `scholardata_paper`.`issued`,
`scholardata_paper`.`venue`, `scholardata_paper`.`journal_id`,
`scholardata_paper`.`volume`, `scholardata_paper`.`issue`,
`scholardata_paper`.`page`, `scholardata_paper`.`references_count`,
`scholardata_paper`.`citations_count`, `scholardata_paper`.`refreshed`,
`scholardata_paper`.`references_refreshed`,
`scholardata_paper`.`citations_refreshed` FROM `scholardata_paper` WHERE
LOWER(JSON_UNQUOTE(JSON_EXTRACT(`scholardata_paper`.`authors`, $."name")))
LIKE LOWER(%rehmann%) ORDER BY `scholardata_paper`.`id` ASC
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32411#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.6919c17ba447802b45ca0fe2c804220d%40djangoproject.com.
