2

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

Arnis Juraga
  • 1,027
  • 14
  • 31

2 Answers2

6

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().

Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • Thanks! In other words, I need to select `select json_extract(@json, "$.link") like '"http%' ; ` ! I believe, using `JSON_UNQUOTE()` will be an additional step and not recommended for performance though. – Arnis Juraga Aug 14 '19 at 08:45
  • It depends on what you want. The *formally correct* approach is to always use `JSON_UNQUOTE` when handling strings, because there is no way to guarantee that the JSON string does not look like `{"link": "\u0068\u0074\u0074\u0070\u0073\u003a\u002f\u002f\u0077\u0077\u0077\u002e\u0067\u006f\u006f\u0067\u006c\u0065\u002e\u0063\u006f\u006d"}`. Once that's decoded, it's `{"link":"https://www.google.com"}`, but only the comparison with `JSON_UNQUOTE` will succeed. It's an extreme example, but it's completely valid and your code should not bug out on it. – Tomalak Aug 14 '19 at 08:53
  • `JSON_EXTRACT` is an expensive operation already. It involves interpreting the path, parsing the JSON, running the path to get results, and serializing those results as JSON again - for every row in your query. Don't assume that `JSON_UNQUOTE` will add too much to that already bad balance. Also - incorrect code that runs fast is not worth a whole lot. – Tomalak Aug 14 '19 at 08:57
  • Thinking about it, since JSON_EXTRACT involves a JSON serialization step, it might be that you will never actually get `"\u0068\u0074\u0074\u0070..."` out of it, no matter how the input string looks like. But you are still at the mercy of whatever the JSON serializer built into MySQL thinks it should do to strings, and there is more than one way to represent a string in JSON, for example when it comes to diacritics. – Tomalak Aug 14 '19 at 09:16
  • This is even more interesting with select on equal. Setting json value to `'{"link": "a"}';` will return `true` for `select json_extract(@json, "$.link") = 'a' ; select json_extract(@json, "$.link") = "a";` but not `select json_extract(@json, "$.link") = '"a"';` – Arnis Juraga Aug 14 '19 at 09:30
  • 1
    That ... doesn't make any sense. How can these be different? https://www.db-fiddle.com/f/tN9Sm8XSwPq959iCdYHgZa/0 – Tomalak Aug 14 '19 at 15:34
3

Please Try this. JSON_UNQUOTE(JSON_EXTRACT(@json, '$.like')) LIKE '%com%'

Bhagwat Singh
  • 119
  • 1
  • 1