Bash: how to mport data from a JSON file into a MySQL database

Importing data from a JSON file into a MySQL database can be done in a variety of ways, but using a Bash script is a powerful and automatable solution. In this article, we will walk you through the steps required to complete this task using basic tools such as jq and mysql.

Prerequisites

Before you begin, make sure you have the following tools installed:

MySQL: The target database.
jq: A command-line JSON manipulation tool.
Bash: The command shell.

Preparing the JSON file

Let's say we have a JSON file named data.json containing the data we want to import. Here's an example of what this might look like:


[
    {
      "id": 1,
      "name": "Alice",
      "email": "alice@example.com"
    },
    {
      "id": 2,
      "name": "Bob",
      "email": "bob@example.com"
}
]

Creating the MySQL table

Before we import the data, we need to create a table in the MySQL database that matches the structure of the JSON. For example:


CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

You can run this command directly in the MySQL console or through a client like mysql:


mysql -u yourusername -p -e "source create_table.sql"

Write the Bash script


#!/bin/bash

# Setting up MySQL credentials
MYSQL_USER="yourusername"
MYSQL_PASSWORD="yourpassword"
MYSQL_DATABASE="mydatabase"
MYSQL_TABLE="users"

# Checking for the presence of the JSON file
if [[ ! -f "data.json" ]]; then
    echo "Data.json file not found!"
exit 1
fi

# Function to import JSON data into MySQL
import_json_to_mysql() {
    local id=$1
    local name=$2
    local email=$3

    local query="INSERT INTO ${MYSQL_TABLE} (id, name, email) VALUES (${id}, '${name}', '${email}');"
    mysql -u "${MYSQL_USER}" -p"${MYSQL_PASSWORD}" "${MYSQL_DATABASE}" -e "${query}"
}

# Read and import data from JSON
cat data.json | jq -c '.[]' | while read -r row; do
    id=$(echo "${row}" | jq -r '.id')
    name=$(echo "${row}" | jq -r '.name')
    email=$(echo "${row}" | jq -r '.email')

    import_json_to_mysql "${id}" "${name}" "${email}"
done

echo "Import completed!"

Script Details

MySQL Credentials Setup: The variables MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE, and MYSQL_TABLE are set to contain the connection information and the name of the target table.
JSON File Check: The script checks if data.json exists in the current directory.
Import Function: The import_json_to_mysql function takes the data and inserts it into the MySQL table by running an insert query.
JSON File Read: jq -c '.[]' reads the JSON file and extracts each object as a separate line.
Field Extraction: For each line, jq -r extracts the values of the id, name, and email fields.
Data Import: The extracted data is passed to the import function, which runs the insert query into the database.

Conclusion

This method provides a simple and automated way to import data from a JSON file into a MySQL database using a Bash script. By using jq for JSON manipulation and mysql for database interaction, you can easily manage the data import process.