Export MySQL json data type field directly to CSV

Question

Consider MySQL database (8.x) with JSON field:

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    |                |
+------------+--------------+------+-----+---------+----------------+

In this answer there is a way to export colums to csv fields:

How to output MySQL query results in CSV format?

What I would like to achieve is to export ONLY data in data column to CSV. This data is well organized and composed of key-value pairs like so:

{"email": "x@example.com", "user_id": 100, "ivr_used": false, "ivr_enabled": true, "callerids_used": true, "call-queue_used": false ...}

I would like that to be exported to CSV file looking like so:

    +--------------+-----------+----------+-------------+----------+
    | email        | user_id   | ivr_used | ivr_enabled | ...      |
    +--------------+----------+-----------+-------------+----------+
    | x@example.com| 100       | false    | true        | ...      |
    +--------------+----------+-----------+-------------+----------+
    | y@example.com| 101       | true     | true        | ...      |
    +--------------+----------+-----------+-------------+----------+
    ....

Is this even possible using MySQL-only solution, or do I have to fetch the data and process it somewhere else?

Partially valid solution for me would be possibility to export the data to JSON file.

Yes, exactly. One key is one column. Every record in this table has the same exact structure in `data` column — Gacek, Jan 28 '21 at 12:46
Maybe you should just show us an example of the output you want in the CSV file — RiggsFolly, Jan 28 '21 at 12:46

score 3 · Accepted Answer · answered Jan 28 '21 at 12:57

3

Well its a bit like if the data were in columns, you have to extract each value you want from each rows data column

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';

answered Jan 28 '21 at 12:57

RiggsFolly

93,638
21
103
149

Thanks, that does the trick. Is there a way to prepend the file with row headers? – Gacek Jan 29 '21 at 10:10

Export MySQL json data type field directly to CSV

1 Answers1