Search code examples
mysqlsqlsql-likemysql-jsoncollate

MySQL LIKE with json_extract

I have a MySQL query, where I filter by a json field:

SELECT id, username 
FROM   (SELECT id, 
               Json_extract(payload, '$.username') AS username 
        FROM   table1) AS tmp 
WHERE  username = 'userName1';

It returns 1 row, which looks like:

1, "userName1" See the quotes that are not in the clause?

What I need is to make the WHERE clause case insensitive. But when I do

WHERE username LIKE 'userName1';

it returns 0 rows. I don't understand why it works this way, the = clause works though it doesn't have those double quotes.

If I do

WHERE username LIKE '%userName1%'; now also returns the row, because %% takes quotes into consideration:

1, "userName1"

But when I do

WHERE username LIKE '%username1%'; it returns 0 rows, so unlike the usual MySQL LIKE it's somehow case sensitive.

What am I doing wrong and how to filter the json payload the case insensitive way? EDIT========================================= The guess is that COLLATE should be used here, but so far I don't understand how to make it work.

Solution

  • Okay, I was able to solve the case insensitivity by adding COLLATE utf8mb4_general_ci after the LIKE clause.

    So the point here is to find a working collation, which in its turn can be found by researching the db you work with.