MySQL – How to use JSON_EXTRACT to get values between two indices

I am trying to extract values between 2 indices in a JSON array using mysql JSON_EXTRACT.

SELECT JSON_EXTRACT('[10, 20, 30, 40,50, 60]', '$[1]');

This query will smoothly return 20 as result. But if I want to get all the numbers between, say, 1st and 3rd indices, how do I query it?

I was expecting something like:

SELECT JSON_EXTRACT('[10, 20, 30, 40,50, 60]', '$[1]..$[3]'); // Not the proper syntax

which will return 20,30,40. But not working.

How do I achieve this?

Answer

SELECT JSON_EXTRACT('[10, 20, 30, 40,50, 60]', CONCAT('$[', idx, ']'))
FROM ( SELECT 1 idx UNION SELECT 2 UNION SELECT 3 ) src;

Of course the indices range can be provided as list/range and converted to rowset in CTE/subquery.