I have a table with JSON data like this:
{"a": [{"color": "blue", "value": 15}, {"color": "red", "value": 30}]}
I need to get the "value" that is inside the same object of "blue".
I thought to use the code below:
SELECT JSON_EXTRACT(my_data, '$.a[0].value');
The problem is that the "blue" object can be in any index of the array.
So, is there a way to retrieve the index first and then i'll query using the right index?
UPDATE
The Barmar's answer works but it needs to wrap in JSON_UNQUOTE()
解决方案
Use JSON_SEARCH() to find the path to blue.
SELECT JSON_EXTRACT(my_data, REPLACE(JSON_SEARCH(my_data, 'one', 'blue'), '.color', '.value'))
JSON_SEARCH will return a string like $.a[0].color. REPLACE changes that to $.a[0].value, then you extract that element.