时间戳

首页 > 解决方案 > JSON_TABLE 有什么替代品吗？

mysql - JSON_TABLE 有什么替代品吗？

问题描述

我在 MySQL 中有一个表，其中有一列带有这样的 json

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}, ...]
...

我需要将其转换为

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

由于公司和服务器，我无法创建临时表。我也不能使用JSON_TABLE. 它应该在一个查询中。有什么解决办法吗？或者我应该将 mysql 更新到版本 8？

标签： mysqlmysql-json

解决方案

这是我在 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`;

给定测试数据的输出：

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

由您决定使用 ' 的子查询union有足够的术语来说明您可能遇到的最长 JSON 数组。

如果这看起来太难了，那么 JSON 可能不是存储数据的正确方式。您应该将它们存储在每个对象的一个​​单独的行中，具有普通列key和value.

推荐阅读