JOIN multiple unions using a loop-CodePudding

I'm facing the following issue: I have a JSON array such as this:

[{"from":"09:25","to":"14:00"},{"from":"15:05","to":"16:10"},{"from":"17:40","to":"17:50"},{"from":"19:00","to":"19:10"},{"from":"19:30","to":"19:50"}]

And I want to have a MySQL query that returns a row for each of the intervals, containing the 'from' and 'to' as columns. So far I have tried this:

SELECT 
    idx,
    REPLACE(JSON_EXTRACT(JSON_EXTRACT(json, CONCAT('$[', idx, ']')), CONCAT('$.from')), '"', '') AS 'from',
    REPLACE(JSON_EXTRACT(JSON_EXTRACT(json, CONCAT('$[', idx, ']')), CONCAT('$.to')), '"', '') AS 'to',
    json
FROM test.json
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION    
  SELECT  3 AS idx UNION
  SELECT  4
  ) AS indexes

And it does work. I get the following result: this is the desired output. The problem is the set number of SELECTS in the join.

The issue is that I have to do this:

  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION    
  SELECT  3 AS idx UNION
  SELECT  4

to insert as many 'idx' as there are items in the JSON array. Is there any way to create do this with a loop? The count of the items will be stored in a separate column, 'howmany' in the table that contains the JSON.

This is how the table I extract data from looks like:

I've tried iterating with a while:

declare counter int unsigned default 0;
SELECT 
    idx,
    REPLACE(JSON_EXTRACT(JSON_EXTRACT(json, CONCAT('$[', idx, ']')), CONCAT('$.from')), '"', '') AS 'from',
    REPLACE(JSON_EXTRACT(JSON_EXTRACT(json, CONCAT('$[', idx, ']')), CONCAT('$.to')), '"', '') AS 'to',
    json
FROM test.json
JOIN ( 
  (while counter < howmany do
    SELECT counter AS idx UNION
    set counter=counter 1;
  end WHILE)
  ) AS indexes

and it fails. I am 100% certain that the way I tried is not the way to do it, but I am out of ideas.

Edit: I think it's worth mentioning that we cannot use JSON_TABLE as our MariaDB version is a slightly earlier one than when JSON_TABLE was introduced.

Edit2: I'm using Apache XAMPP's MySQL server.

Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $

MariaDB version 10.3.32

CodePudding user response：

WITH RECURSIVE
cte AS (
    SELECT id,
           jsonvalue,
           0 num,
           JSON_UNQUOTE(JSON_EXTRACT(jsonvalue, CONCAT('$[', 0, '].from'))) `from`,
           JSON_UNQUOTE(JSON_EXTRACT(jsonvalue, CONCAT('$[', 0, '].to'))) `to`
    FROM test
    UNION ALL
    SELECT id,
           jsonvalue,
           1   num,
           JSON_UNQUOTE(JSON_EXTRACT(jsonvalue, CONCAT('$[', 1   num, '].from'))),
           JSON_UNQUOTE(JSON_EXTRACT(jsonvalue, CONCAT('$[', 1   num, '].to')))
    FROM cte
    WHERE JSON_EXTRACT(jsonvalue, CONCAT('$[', 1   num, '].from')) IS NOT NULL
)
SELECT id,
       `from`,
       `to`
FROM cte;

DEMO