Why JSON_EXTRACT wildcard with LIKE% or %LIKE does not work in MySQL database?

Question

I need to search for data in MYSQL table with JSON data. Using JSON_EXTRACT(@json, "$.link") LIKE '%http%' works as expected. But LIKE 'http%' or '%http' dosn't!

Does it means JSON_EXTRACT cant't be used with single wildcard select matching?

EXAMPLE:

This is my JSON

set @json = '{"link": "https://www.google.com"}' ; 

select JSON_EXTRACT(@json, "$.link") like '%com'; 
-- returns 0

select JSON_EXTRACT(@json, "$.link") like 'http%' ; 
-- returns 0

select JSON_EXTRACT(@json, "$.link") like '%google%' ; 
select JSON_EXTRACT(@json, "$.link") like '%http%' ; 
select JSON_EXTRACT(@json, "$.link") like '%com%' ; 
-- returns 1 !

And here are an example in Fiddle: https://www.db-fiddle.com/f/7yPvfa2UZsZLdYSxdsnecx/0

Answer 1

JSON_EXTRACT still returns JSON. It does not return "raw" values, although it might make that impression when you use it to extract an individual number, because the way JSON represents a number is compatible to SQL. That's not the case for strings.

In other words:

set @json = '{"link": "https://www.google.com"}' ; 

select JSON_EXTRACT(@json, "$.link"); 

returns "https://www.google.com", not https://www.google.com, and your LIKE has to account for the double quotes.

To convert a JSON string to to an actual MySQL string, use JSON_UNQUOTE().