Can I do case insensitive search with JSON_EXTRACT in MySQL?

Question

I am running SELECT * FROM mytable WHERE LOWER(JSON_EXTRACT(metadata, "$.title")) = 'hello world' with the intent that hello world is data from a user that I will flatten to all lowercase. The actual value in my db is "Hello World", but this search comes back empty every time.

If I do a SELECT LOWER(JSON_EXTRACT(metadata, "$.title")) FROM mytable, it certainly comes back lowercase as hello world. Not sure what I'm missing here.

Queries to get actual values:

SELECT JSON_EXTRACT(metadata, "$.title") FROM mytable gets me "Hello World"

SELECT LOWER(JSON_EXTRACT(metadata, "$.title")) FROM mytable gets me "hello world"

Queries trying to find the right row

Gets me value

SELECT * FROM mytable WHERE JSON_EXTRACT(metadata, "$.title") = "Hello World"

SELECT * FROM mytable WHERE metadata->"$.title" = "Hello World"

SELECT * FROM ututs WHERE LOWER(metadata->"$.title") LIKE "%hello world%"

Gets me nothing

SELECT * FROM mytable WHERE JSON_EXTRACT(metadata, "$.title") = "hello world"

SELECT * FROM mytable WHERE JSON_EXTRACT(metadata, "$.title") LIKE "%hello world%"

SELECT * FROM ututs WHERE LOWER(metadata->"$.title") = "hello world"

SELECT * FROM ututs WHERE LOWER(metadata->"$.title") LIKE "hello"

So it looks like the result is giving back the value, including quotes. That doesn't appear to be the issue though, given I get a result when I match the case. I am also confused why the % at the start is solving my issue. There is no space between the " and H. I typed the JSON out myself.

I also updated metadata column straight to {"title":"Hello World"} by manually typing. MySQL automatically adds a space after colon to make it {"title": "Hello World"}, which is fine, but was just sanity checking any spaces.

Answer 1

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.

Answer 2

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           |
|---------------------|------------------|---------------------|