mysql - 如何使用 MySQL select 语句为 JSON 列表中的每个条目生成行？

Question

我在 MySQL 表中有一个 JSON 列。

列包含字典列表。我希望列表中的每个项目都有自己的行，并带有行 ID。请参阅下面的示例以更好地理解。

例子:

mysql> select id, geography from region;
+------|-------------------------------------------------------------------------------------------------------------+
|  id  | geography                                                                                                   |
+------|-------------------------------------------------------------------------------------------------------------+
|  1   | [{"state": "SC", "county": "BERKELEY"}}]                                                                    |
|  2   | [{"county": "Placer", "state": "CA"}, {"county": "Sacramento", "state": "CA"}]                              |
|  3   | [{"county": "Jeff", "state": "MO"},{"county": "Charles", "state": "MO"},{"county": "Louis", "state": "MO"}] |
+------|-------------------------------------------------------------------------------------------------------------+

期望的输出:

+------|--------------|-------------+
| id   | county       |  state      |
+------|--------------|-------------|
| 1    | BERKELEY     |  SC         |
| 2    | Placer       |  CA         |
| 2    | Sacramento   |  CA         |
| 3    | Jeff         |  MO         |
| 3    | Charles      |  MO         |
| 3    | Louis        |  MO         |
+------|--------------|-------------+

Answer 1

这样做似乎是个坏主意，但我想有时你不得不这样做。

我想到了 3 个选项:

1. 如果您使用的是 MySQL >=5.7.8，请使用内部 JSON 相关函数
2. MySQL 的 JSON UDF
3. 使用 common_schema

您最好的选择可能是第三个选项。您应该查找 common_schema.extract_json_value()。