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

Question

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?

Out of curiosity, why are you storing data in a JSON array if you have this type of range query? It's much easier if you store data in normal rows and columns. — Bill Karwin
– Bill Karwin, Commented Sep 15, 2022 at 14:24
@BillKarwin This is sort of a temporary table. Not much processing included. Once saved, just return this data for a GET call. The only complicated processing is to get the limit and offset based retrieval. — AKA
– AKA, Commented Sep 16, 2022 at 3:04

Akina · Accepted Answer · 2022-09-15 11:28:16Z

2

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.

answered Sep 15, 2022 at 11:28

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

2 Comments

AKA Over a year ago

I have improved the solution by making the following changes: SELECT JSON_EXTRACT('[10, 20, 30, 40,50, 60]', CONCAT('$[', idx, ']')) FROM ( SELECT seq as idx FROM seq_0_to_2) src; Note: I am using mariadb.

Akina Over a year ago

@AKA You have not marked your question with mariadb tag... next time always provide precise DBMS version in your question. And I doubt that this really improves the query - it increases its readability, of course, but does not effect the performance and decreases the compatibility.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related