直接导出MySQL json数据类型字段到CSV

Question

考虑MySQL数据库(8.x)的JSON字段:

mysql>  desc users_health;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | int          | NO   |     | NULL    |                |
| data       | json         | YES  |     | NULL    |                |
| created_at | timestamp    | YES  |     | NULL    |                |
| updated_at | timestamp    | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

在这个答案中有一种方法可以将列导出到csv字段:

如何以CSV格式输出MySQL查询结果?

我想要实现的是将data列中的ONLY数据导出到CSV。该数据由键值对组织和组成，如下所示:

{"email": "[e-mail]", "user_id": 100, "ivr_used": false, "ivr_enabled": true, "callerids_used": true, "call-queue_used": false ...}

我想把它导出为CSV文件，看起来像这样:

+--------------+-----------+----------+-------------+----------+
| email        | user_id   | ivr_used | ivr_enabled | ...      |
+--------------+----------+-----------+-------------+----------+
| [e-mail]| 100       | false    | true        | ...      |
+--------------+----------+-----------+-------------+----------+
| [e-mail]| 101       | true     | true        | ...      |
+--------------+----------+-----------+-------------+----------+
....

这甚至可能使用mysql解决方案，还是我必须获取数据并在其他地方处理?

对我来说部分有效的解决方案是可能将数据导出到JSON文件。

Answer 1

这有点像如果数据以列为单位，你必须从每一行数据列中提取你想要的每个值

SELECT JSON_EXTRACT(data, '$.email') as email,
JSON_EXTRACT(data, '$.user_id') as user_id,
. . .
FROM users_health
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';