MySQL 8: How to export a database to a JSON file

Introduction

Working with databases often requires the ability to share data in a commonly readable format. JSON, which stands for JavaScript Object Notation, is a lightweight data interchange format that’s easy to read for humans and simple to parse and generate for machines. It has become a popular format for API responses and configuration files. In this tutorial, you’ll learn how to export data from a MySQL 8 database into a JSON file using various methods. We’ll start with basic techniques and move towards more advanced examples.

Prerequisites

• MySQL Server 8+ installed
• A user account with privileges to access your MySQL database
• The MySQL command-line tools or a graphical interface such as MySQL Workbench
• Knowledge of executing SQL queries
• A database with some tables and data to export

Basic Export with the SELECT INTO OUTFILE Statement

This is the easiest method you can use to export a table to a JSON file.

SELECT * FROM your_table_name 
INTO OUTFILE '/path/to/your_file.json' 
FIELDS ESCAPED BY '' 
LINES TERMINATED BY '
';

Note that you need to replace your_table_name with the actual name of your table, and /path/to/your_file.json with the path where you want to store your JSON file.

Exporting with JSON Functions

In MySQL, you have powerful JSON functions at your disposal that allow you to manipulate and create JSON objects directly with your queries.

Here’s an example of extracting the entire table as a JSON array.

SELECT JSON_ARRAYAGG(JSON_OBJECT('key1', value1, 'key2', value2)) INTO OUTFILE '/path/to/your_file.json' FIELDS ESCAPED BY '' LINES TERMINATED BY '
' FROM your_table;

You need to replace key1, value1, key2, and value2 with your actual column names and respective values. The result of this query is a JSON array with each row as a JSON object.

Structured Export with Stored Procedures

If you foresee frequently exporting your data and possibly adding additional logic to the process, you might consider writing a stored procedure.

DELIMITER $
CREATE PROCEDURE Export_JSON()
BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE a CURSOR FOR SELECT JSON_OBJECT('col1', col1, 'col2', col2) FROM your_table;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN a;
 read_loop: LOOP
 FETCH a INTO @json;
 IF done THEN
 LEAVE read_loop;
 END IF;
 SELECT @json INTO OUTFILE '/path/to/your_file.json' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
';
 END LOOP;
 CLOSE a;
END$
DELIMITER ;

This creates a stored procedure that uses a cursor to loop through each row in your table, converts it to a JSON object, and writes it to an output file line by line.

Advanced Export Using Custom Scripts

If the above methods do not satisfy your needs, you may opt for using a custom script in a language such as Python or PHP, utilizing their MySQL connectivity and JSON manipulation capabilities.

# Python example using pymysql and json libraries
import pymysql.cursors
import json

# Connect to the database
connection = pymysql.connect(host='your_host',
 user='your_user',
 password='your_password',
 db='your_db',
 charset='utf8mb4',
 cursorclass=pymysql.cursors.DictCursor)

try:
 with connection.cursor() as cursor:
 sql = "SELECT * FROM `your_table`"
 cursor.execute(sql)
 result = cursor.fetchall()
 with open('/path/to/your_file.json', 'w') as outfile:
 json.dump(result, outfile)
finally:
 connection.close()

This Python script makes use of the pymysql library to connect to a MySQL database, execute a query, fetch the results, and then uses the json library to dump the result into a JSON file.

Using mysqldump with JSON formatting

mysqldump is common for MySQL backups, but you can also use it with the --tab option to dump table data as TSV (tab-separated values) files along with the corresponding schema in SQL format. While it does not support direct JSON conversion, one can post-process these TSV files using command-line tools like awk.

Using mysqldump to generate TSV (Tab-Separated Values) files and then converting these files into JSON format requires a two-step process. First, you’ll use mysqldump with the --tab option to create TSV files, and then you’ll use a command-line tool like awk to convert the TSV data to JSON format.

Step 1: Dump Data as TSV Using mysqldump

The mysqldump command with the --tab option creates TSV files and schema files. Ensure you have the necessary permissions and that the directory you’re writing to is writable by MySQL.

mysqldump -u [username] -p --tab=/path/to/dump/dir your_database_name

• Replace [username] with your MySQL username.
• /path/to/dump/dir is the directory where you want to store the TSV and SQL files.
• your_database_name is the name of your database.

This command will create a .txt file with the TSV data and a .sql file with the table schema for each table in your database.

Step 2: Convert TSV to JSON Using awk

After generating the TSV files, you can use awk to convert them to JSON. The following is a basic awk script for converting TSV to JSON. This script assumes the first row contains the column names:

awk 'BEGIN {
    FS="\t"; 
    print "["; 
    first_record=1; 
}
{
    if (NR>1) { 
        if (!first_record) {
            print ",";
        } else {
            first_record=0;
        }
        printf "{";
        for (i=1; i<=NF; i++) {
            printf "\"%s\":\"%s\"", $i, $(i+1);
            if (i<NF) {
                printf ",";
            }
        }
        printf "}";
    }
}
END {
    print "\n]";
}' /path/to/dump/dir/your_table.txt > /path/to/dump/dir/your_table.json

• Replace /path/to/dump/dir/your_table.txt with the path to your TSV file.
• The script processes each line, converting fields separated by tabs into JSON key-value pairs.
• The output is redirected to a JSON file.

Explanation:

1. mysqldump Command: Dumps the data of each table in TSV format. Each field’s value is separated by a tab, and each record is separated by a newline.
2. awk Script:
   • Begins with initializing the field separator as a tab (FS="\t").
   • It then iterates over each line (record) of the input TSV file.
   • The first line is expected to contain column headers, which are used as keys in the JSON objects.
   • Each subsequent line is converted into a JSON object, with fields corresponding to column headers.
   • The script handles commas and formatting to ensure valid JSON output.
   • Outputs a JSON array of objects, each object representing a record from the TSV.

Conclusion

To recap, we’ve covered a handful of methods to export data from MySQL 8 to a JSON file, ranging from simple SQL queries to using programming languages for more control and customization. It is essential to choose a method that aligns with your project requirements and expertise level.