mysql json类型数组索引,MySQL根据属性值返回JSON数组索引

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.