Is there any alternative to JSON_TABLE?

Question

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?

How can you not be able to create a temporary table whilst still being able to update to MySQL8? Are you sure you have this right? — Tangentially Perpendicular, Sep 14 '21 at 16:17
mysql v5.7 supports json_keys and json_extract functions, which can return keys and values from a json document, but you would still need to convert the results into multiple rows as opposed to a list of values in a single field value. — Shadow, Sep 14 '21 at 16:25
Seems the data should have been stored in a child table. JSON in a column may sound great but in reality just makes life difficult in 90% of actual implementations — RiggsFolly, Sep 14 '21 at 16:36
*Is there any solution?* Not a problem. What is maximal key:value objects per one row? — Akina, Sep 14 '21 at 17:10

score 2 · Accepted Answer · answered Sep 14 '21 at 18:02

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.

Wow that's great example with expected result for key and value pair to extract data in a table format if your mysql version is with 5.7 .... Thanks @Bill Karwin — Rajendra Badri, Dec 23 '22 at 12:24

Is there any alternative to JSON_TABLE?

1 Answers1

Linked