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.

Tags:

Mysql

Json