Is it possible to do JSON_EXTRACT with multiple keys?

Question

First of all, I am not sure if my title explains this question in the correct way but I think my code will.

Instead of doing this:

SELECT * FROM mytable WHERE JSON_EXTRACT(`cars`,'$."65"') = 'Toyota' OR JSON_EXTRACT(`cars `,'$."66"') = 'Toyota' OR JSON_EXTRACT(`cars `,'$."67"') = 'Toyota'

I want to do this:

SELECT * FROM mytable WHERE JSON_EXTRACT(`cars`,any of these 65,66,67) = 'Toyota'

Can this be done?

Provide table structure and example data.. Place it on sqlfiddle.com.. Then we can help you. — Raymond Nijland
– Raymond Nijland, Commented May 28, 2018 at 11:19
JSON_EXTRACT(json_doc, path[, path] ...) source dev.mysql.com/doc/refman/8.0/en/… .. Looks like it's possible to include multiple paths.. — Raymond Nijland
– Raymond Nijland, Commented May 28, 2018 at 11:22

Tim Biegeleisen · Accepted Answer · 2018-05-28 11:40:38Z

6

According to the documentation, you may pass any number of paths as arguments 2 onward to the JSON_EXTRACT function. So the following should work:

SELECT *
FROM yourTable
WHERE JSON_UNQUOTE(JSON_EXTRACT(`cars`, '$."65"', '$."66"', '$."67"')) LIKE '%"Toyota"%';

Demo

Note that the WHERE clause will actually return string values in the form ["some_value"], hence I compare against this. Someone with more experience with MySQL's JSON API can probably do better than what I wrote above. But, this does at least partially answer your question; yes, you can extract multiple paths in a single call to JSON_EXTRACT.

edited May 28, 2018 at 11:40

answered May 28, 2018 at 11:23

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

C3roe Over a year ago

Slightly wondering why OP needs to be told again today though ... stackoverflow.com/q/50528364/1427878

Tim Biegeleisen Over a year ago

@CBroe I'm part of the problem; I haven't tested this yet...I'm setting up a query now.

Raymond Nijland Over a year ago

This question is little bit different.. Because JSON_EXTRACT() with multiple paths returns a JSON ARRAY..Guess he should not figure it out on how to match that with the string Toyota @CBroe ... TimBiegeleisen answer looks like it's solving that problem

Raymond Nijland Over a year ago

"Someone with more experience with MySQL's JSON API can probably do better than what I wrote above" MySQL JSON API is a bit bit i would expect JSON_UNQUOTE(JSON_EXTRACT(cars, '$."65"', '$."66"', '$."67"')) = JSON_ARRAY("Toyota") would also select rextester.com/UXF29788.. Your query might be the best solution.

Peter Karlsson Over a year ago

Tim, your solution is pretty close to what I want. The only thing I don't want is the LIKE at the end. I tried to use = instead but that gives me no rows.

|

Collectives™ on Stack Overflow

Is it possible to do JSON_EXTRACT with multiple keys?

1 Answer 1

Demo

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related