Can I do case insensitive search with JSON_EXTRACT in MySQL?
I ran into a similar case-sensitivity issue using a like
.
I solved it by casting the output from the JSON function as a CHAR
.
This article mentioned that JSON functions return a utf8mb4 string
. This leads me to believe that a different data type is being returned (maybe a byte array) instead of a VARCHAR
. Which would explain why case-insensitive text searching was failing.
select
json_unquote(json_extract(json, '$.reservation.status')) as status,
json_unquote(json_extract(json, '$.reservation.status')) like '%cancelled%' as case_sensitive,
cast(json_unquote(json_extract(json, '$.reservation.status')) as CHAR) like '%cancelled%' as case_insensitive
from myTable
The output from this query is:
|---------------------|------------------|---------------------|
| status | case_sensitive | case_insensitive |
|---------------------|------------------|---------------------|
| Cancelled | 0 | 1 |
|---------------------|------------------|---------------------|
| New | 0 | 0 |
|---------------------|------------------|---------------------|
SELECT * FROM mytable WHERE LOWER(JSON_EXTRACT(metadata, "$.title")) = JSON_QUOTE("hello world")
and SELECT * FROM mytable WHERE LOWER(metadata->"$.title") = JSON_QUOTE("hello world")
works, but I'm not sure why I need to use JSON_QUOTE
when using LOWER
vs not needing it for an exact search.
If someone can explain why, I will gladly mark them as the answer.