Is there any alternative to JSON_TABLE?

I have a table in MySQL with a column with json like this

id col
1  [{"key": 1, "value": 9}, {"key": 2, "value": 8}, {"key": 3, "value": 7}, ...]
2  [{"key": 1, "value": 6}, {"key": 2, "value": 5}, {"key": 3, "value": 4}, ...]
...

I need to transform it in

id key value
1   1    9
1   2    8
1   3    7
2   1    6
2   2    5
2   3    4

Because of company and server, I cannot create temporary table. Also I cannot use JSON_TABLE. It should be in one query. Is there any solution? OR I should update mysql to version 8?

Solution

Here's a solution I tested on MySQL 5.7:

select * from (
  select id,
   json_unquote(json_extract(col, concat('$[',n.i,'].key'))) as `key`,
   json_unquote(json_extract(col, concat('$[',n.i,'].value'))) as `value`
  from mytable
  cross join (select 0 as i union select 1 union select 2 union select 3 union select 4 union select 5) as n
) as t
where t.`key` is not null
order by id, `key`;

Output given your test data:

+------+------+-------+
| id   | key  | value |
+------+------+-------+
|    1 | 1    | 9     |
|    1 | 2    | 8     |
|    1 | 3    | 7     |
|    2 | 1    | 6     |
|    2 | 2    | 5     |
|    2 | 3    | 4     |
+------+------+-------+

It's up to you to make the subquery with the union's have enough terms to account for the longest JSON array you might encounter.

If that seems too difficult, then maybe JSON is not the right way to store your data. You should store them on one individual row per object, with normal columns key and value.