Mysql爆炸json数组的行

Question

从一个带有json数组的列的表中，我需要提取键"user_id"的所有值。每行一个。如果为null或空数组返回null。类似于python pandas的爆炸方法。

数组长度未知。

原始表:

| id | users                                                           |
|----|-----------------------------------------------------------------|
|  1 |[{"id": 2, "mail": "[e-mail]"}, {"id": 3, "mail": "[e-mail]"}] |
|  2 |[{"id": 5, "email": "[e-mail]"}]"                            |
|  3 | []
|

加工表:

| id | users    |
|----|----------|
|  1 | 2        |
|  1 | 3        |
|  2 | 5        |
|  3 | NULL     |

Answer 1

select id, j.user_id from mytable left outer join 
json_table(users, '$[*]' columns (user_id int path '$.user_id')) as j on true;
+------+---------+
| id   | user_id |
+------+---------+
|    1 |       2 |
|    1 |       3 |
|    2 |       5 |
|    3 |    NULL |
+------+---------+

阅读https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html获取更多关于JSON_TABLE()函数的信息。