1

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.

Deepam Gupta
  • 2,374
  • 1
  • 30
  • 33
  • 1
    Out of curiosity, why did you store the data as a JSON object if you wanted to query ranges of keys? Why not store it in another table, with two normal columns, `id` and `content`? Then you could query rows `WHERE id BETWEEN 100 AND 200` in a conventional way. – Bill Karwin Mar 10 '21 at 13:55
  • @BillKarwin I like your question, the reason of doing this is each row in the table has a json object with almost 1K to 1M key-value pairs. And we will be having almost 1M to 10M such rows, so maintaining them will be tedious. Also our requirements restrict us to use only one table. hope this fulfils your curiosity – Deepam Gupta Mar 10 '21 at 14:07
  • 1
    Okay, well I don't know of any way to do the query you want with a JSON object. You could do it with [JSON_TABLE()](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html) if you used a JSON array instead (assuming you are using MySQL 8.0). – Bill Karwin Mar 10 '21 at 14:33
  • "Requirements restrict us to use only one table" is ridiculous for a relational database. – Bill Karwin Mar 10 '21 at 14:34

1 Answers1

3

Ok, I start with this data:

MariaDB [test]> select * from table1;
+------+---------------------------------------------------------------------------+
| id   | data                                                                      |
+------+---------------------------------------------------------------------------+
|    1 | {"1": "content1", "2": "content2", "3":"content3","10000":"content10000"} |
+------+---------------------------------------------------------------------------+

Query:

WITH RECURSIVE data AS (
  SELECT 
     JSON_VALUE(JSON_KEYS(data), '$[0]') AS k, 
     JSON_EXTRACT(data, CONCAT('$.',JSON_VALUE(JSON_KEYS(data), '$[0]'))) AS v, 
     0 AS idx 
  FROM table1
  UNION
  SELECT 
     JSON_VALUE(JSON_KEYS(data), CONCAT('$[', d.idx + 1, ']')) AS k, 
     JSON_EXTRACT(data, CONCAT('$.',JSON_VALUE(JSON_KEYS(data), CONCAT('$[', d.idx + 1, ']')))) AS v, 
     d.idx + 1 AS idx 
  FROM data AS d, table1
  WHERE d.idx < JSON_LENGTH(JSON_KEYS(data)) - 1
) SELECT * FROM data;

sample output (because if only have limited data):

+-------+----------------+------+
| k     | v              | idx  |
+-------+----------------+------+
| 1     | "content1"     |    0 |
| 2     | "content2"     |    1 |
| 3     | "content3"     |    2 |
| 10000 | "content10000" |    3 |
+-------+----------------+------+

Adding a WHERE-clause should not be a problem.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Well done, but the complex solutions required to work with JSON should convince anyone that it's better to use normal rows and columns. – Bill Karwin Mar 11 '21 at 19:15