JSON in MySQL : get data from an object within a range of keys

Question

What I have:

• A mysql table, say table1
• table1 contains two columns viz. id and data
• id is int but the twist is data is JSON type and its keys are comparable
• the table1 contains only one row (for the sake of this question)

table1

id	data
1	{'1': 'content1', '2': 'content2', '3':'content3',.......,'10000':'content10000' }

What I want to have:

I want a query such that it returns me key-value pairs within a range of keys, say, 100 to 200.

What I'm getting on searching on internet:

Everywhere I got only the answers where one can get rows which have the values within a range, but here the case is I want values of keys within a range.

• nbk: for starters which mysql version are you using? basically json is bad for such problems, mysql 8 has json_table which simplifies it somewhat, still json is bad if you want that information directly from mysql, store tem in a normal relational table and it gets easy
• Deepam Gupta: I'm using MariaDB 10.4
• Akina: Synthetic numbers list and JOIN.
• nbk: @Akina the oath must be strings or not`?
• Akina: @nbk Generated numbers will be used for to build JSON paths - so number during generation and string finally.

Answer 1

Possible realization:

WITH RECURSIVE cte AS ( SELECT @from num
 UNION ALL
 SELECT num+1 FROM cte WHERE num < @till )
SELECT CONCAT('key', num) `key`, 
 JSON_EXTRACT(test.val, CONCAT('$.key', num)) `value`
FROM test
CROSS JOIN cte
HAVING `value` IS NOT NULL;

fiddle

• Deepam Gupta: Thanks @Akina, learned something new today.