MySQL LIKE with json_extract

user_3299693 0
Viewed 1276

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.

collate mysql-json sql-like mysql sql
2 Answers

Default collation of MySQL is latin1_swedish_ci before 8.0 and utf8mb4_0900_ai_ci since 8.0. So non-binary string comparisons are case-insensitive by default in ordinary columns.

However, as mentioned in MySQL manual for JSON type

MySQL handles strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation.".

Therefore, your JSON value is in utf8mb4_bin collation and you need to apply a case insensitive collation to either operand to make the comparison case insensitive.

E.g.

WHERE username COLLATE XXX LIKE '...'

where XXX should be a utf8mb4 collation (such as the utf8mb4_general_ci you've mentioned.).

Or

WHERE username LIKE '...' COLLATE YYY

where YYY should be a collation that match the character set of you connection.

For equality comparison, you should unquote the JSON value with JSON_UNQUOTE() or the unquoting extraction operator ->>

E.g.

JSON_UNQUOTE(JSON_EXTRACT(payload, '$.username'))

Or simply

payload->>'$.username'

The JSON type and functions work way different from ordinary data types. It appears that you are new to it. So I would suggest you to read the manual carefully before putting it into a production environment.

user_10892420

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.

user_32996930
Related