MySQL JSON Import Export
Introduction
JSON (JavaScript Object Notation) has become an essential data format for web applications and APIs due to its lightweight, human-readable structure and language independence. Since MySQL 5.7, the database system has included native JSON support, allowing you to store and manipulate JSON data efficiently.
In this guide, we'll explore how to import JSON data into MySQL tables and export MySQL data as JSON. This knowledge is valuable when integrating MySQL with web applications, processing API data, or performing data migrations.
Understanding JSON in MySQL
Before diving into import and export operations, let's understand how MySQL handles JSON data:
- MySQL provides a native JSON data type
- It validates JSON documents on insertion
- It offers JSON-specific functions for manipulation and query
- It can store complex nested structures within a single column
Importing JSON Data into MySQL
There are several methods to import JSON data into MySQL:
- Using the JSON_TABLE function
- Loading JSON files with LOAD DATA INFILE
- Parsing JSON in application code
- Using mysqlimport utility with JSON data
Let's explore each method with examples.
Method 1: Using JSON_TABLE Function
The JSON_TABLE function allows you to transform JSON data into a relational format.
SELECT *
FROM JSON_TABLE(
'[
{"id": 1, "name": "John", "skills": ["PHP", "JavaScript"]},
{"id": 2, "name": "Mary", "skills": ["Python", "SQL"]}
]',
"$[*]" COLUMNS(
id INT PATH "$.id",
name VARCHAR(50) PATH "$.name",
skills JSON PATH "$.skills"
)
) as user_data;
Output:
+----+------+------------------------+
| id | name | skills |
+----+------+------------------------+
| 1 | John | ["PHP", "JavaScript"] |
| 2 | Mary | ["Python", "SQL"] |
+----+------+------------------------+
This is useful when you have JSON data and want to convert it into a tabular format before inserting.
Method 2: Loading JSON Files with LOAD DATA INFILE
For larger JSON datasets stored in external files, you can use MySQL's LOAD DATA INFILE:
-- First, create a table to store the JSON data
CREATE TABLE users_json (
user_data JSON
);
-- Load the JSON file
LOAD DATA INFILE 'C:/data/users.json'
INTO TABLE users_json
(user_data);
The file users.json might contain:
{"id": 1, "name": "John", "skills": ["PHP", "JavaScript"]}
{"id": 2, "name": "Mary", "skills": ["Python", "SQL"]}
After loading, you can extract specific values:
-- Extract values from JSON and insert into a structured table
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
skills JSON
);
INSERT INTO users (id, name, skills)
SELECT
JSON_EXTRACT(user_data, '$.id'),
JSON_EXTRACT(user_data, '$.name'),
JSON_EXTRACT(user_data, '$.skills')
FROM users_json;
Method 3: Using MySQL Procedures to Parse JSON
For more complex JSON structures, you can create a stored procedure:
DELIMITER //
CREATE PROCEDURE import_json_users(IN json_data JSON)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE json_length INT;
SET json_length = JSON_LENGTH(json_data);
WHILE i < json_length DO
INSERT INTO users(id, name, skills)
VALUES(
JSON_EXTRACT(json_data, CONCAT('$[', i, '].id')),
JSON_UNQUOTE(JSON_EXTRACT(json_data, CONCAT('$[', i, '].name'))),
JSON_EXTRACT(json_data, CONCAT('$[', i, '].skills'))
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- Call the procedure with your JSON data
CALL import_json_users('[{"id": 1, "name": "John", "skills": ["PHP", "JavaScript"]}, {"id": 2, "name": "Mary", "skills": ["Python", "SQL"]}]');
Exporting MySQL Data as JSON
Now let's look at techniques for exporting MySQL data to JSON format:
Method 1: Using JSON Functions
MySQL provides functions to construct JSON directly in SQL queries:
-- Create a simple user table for demonstration
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
age INT
);
-- Insert sample data
INSERT INTO users VALUES
(1, 'John Smith', '[email protected]', 28),
(2, 'Sarah Jones', '[email protected]', 35),
(3, 'Michael Brown', '[email protected]', 42);
-- Export as JSON array of objects
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'id', id,
'name', name,
'email', email,
'age', age
)
) AS users_json
FROM users;
Output:
[
{"id": 1, "name": "John Smith", "email": "[email protected]", "age": 28},
{"id": 2, "name": "Sarah Jones", "email": "[email protected]", "age": 35},
{"id": 3, "name": "Michael Brown", "email": "[email protected]", "age": 42}
]
Method 2: Using SQL Outfile with JSON Format
You can export directly to a file:
SELECT JSON_OBJECT(
'id', id,
'name', name,
'email', email,
'age', age
) AS user_json
FROM users
INTO OUTFILE 'C:/data/exported_users.json';
Note: The MySQL server must have write permissions to the specified directory.
Method 3: Export Using Programming Languages
For more flexibility, you can use programming languages like PHP or Python:
<?php
// PHP example for exporting MySQL data to JSON file
$conn = new mysqli("localhost", "username", "password", "database");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$result = $conn->query("SELECT id, name, email, age FROM users");
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
file_put_contents('users.json', json_encode($data, JSON_PRETTY_PRINT));
echo "Data exported to users.json successfully!";
$conn->close();
?>
Handling Nested JSON Structures
One of JSON's strengths is its ability to represent nested data structures. MySQL can handle these effectively:
Importing Nested JSON
Consider this complex JSON with nested objects and arrays:
{
"order_id": 1001,
"customer": {
"id": 500,
"name": "Jane Doe",
"contact": {
"email": "[email protected]",
"phone": "555-1234"
}
},
"items": [
{"product_id": 101, "name": "Laptop", "price": 999.99},
{"product_id": 102, "name": "Mouse", "price": 24.99}
],
"total": 1024.98
}
To import and query this structure:
-- Create a table to store order data
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
order_data JSON
);
-- Insert the JSON data
INSERT INTO orders (order_data) VALUES (
'{
"order_id": 1001,
"customer": {
"id": 500,
"name": "Jane Doe",
"contact": {
"email": "[email protected]",
"phone": "555-1234"
}
},
"items": [
{"product_id": 101, "name": "Laptop", "price": 999.99},
{"product_id": 102, "name": "Mouse", "price": 24.99}
],
"total": 1024.98
}'
);
-- Query nested data
SELECT
JSON_EXTRACT(order_data, '$.order_id') AS order_id,
JSON_EXTRACT(order_data, '$.customer.name') AS customer_name,
JSON_EXTRACT(order_data, '$.customer.contact.email') AS email,
JSON_EXTRACT(order_data, '$.total') AS total_amount
FROM orders;
Output:
+----------+---------------+-------------------+--------------+
| order_id | customer_name | email | total_amount |
+----------+---------------+-------------------+--------------+
| 1001 | "Jane Doe" | "[email protected]"| 1024.98 |
+----------+---------------+-------------------+--------------+
Exporting with Nested Structures
You can create complex nested JSON structures from multiple tables:
-- Create sample tables
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT,
product_name VARCHAR(100),
price DECIMAL(10,2),
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(id)
);
-- Insert sample data
INSERT INTO customers VALUES
(1, 'John Smith', '[email protected]'),
(2, 'Sarah Jones', '[email protected]');
INSERT INTO orders VALUES
(101, 1, '2023-01-15', 129.98),
(102, 2, '2023-01-16', 499.95);
INSERT INTO order_items VALUES
(1, 101, 'Keyboard', 49.99, 1),
(2, 101, 'Mouse', 29.99, 1),
(3, 101, 'Mouse Pad', 9.99, 5),
(4, 102, 'Monitor', 249.99, 2);
-- Export nested JSON structure
SELECT
JSON_OBJECT(
'customer', JSON_OBJECT(
'id', c.id,
'name', c.name,
'email', c.email
),
'order', JSON_OBJECT(
'id', o.id,
'date', o.order_date,
'total', o.total,
'items', (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'product', oi.product_name,
'price', oi.price,
'quantity', oi.quantity,
'subtotal', oi.price * oi.quantity
)
)
FROM order_items oi
WHERE oi.order_id = o.id
)
)
) AS order_json
FROM customers c
JOIN orders o ON c.id = o.customer_id;
Practical Applications
1. API Integration
When working with external APIs, you often need to import JSON responses into MySQL:
-- Create a table to store API responses
CREATE TABLE api_weather_data (
id INT AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(100),
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
weather_data JSON
);
-- Store JSON response from a weather API
INSERT INTO api_weather_data (city, weather_data)
VALUES (
'New York',
'{
"temp": 72.5,
"humidity": 65,
"conditions": "Partly Cloudy",
"forecast": [
{"day": "Monday", "high": 75, "low": 65},
{"day": "Tuesday", "high": 78, "low": 67},
{"day": "Wednesday", "high": 80, "low": 68}
]
}'
);
-- Query specific weather data
SELECT
city,
JSON_EXTRACT(weather_data, '$.temp') AS temperature,
JSON_EXTRACT(weather_data, '$.conditions') AS conditions
FROM api_weather_data;
2. Configuration Management
JSON is excellent for storing application configuration:
CREATE TABLE app_config (
app_name VARCHAR(50) PRIMARY KEY,
config JSON
);
INSERT INTO app_config VALUES (
'my_web_app',
'{
"database": {
"max_connections": 100,
"timeout": 30,
"retry_attempts": 3
},
"ui": {
"theme": "dark",
"font_size": "medium",
"animations": true
},
"features": ["comments", "likes", "sharing"]
}'
);
-- Retrieve configuration values
SELECT
app_name,
JSON_EXTRACT(config, '$.database.max_connections') AS max_db_connections,
JSON_EXTRACT(config, '$.ui.theme') AS ui_theme,
JSON_CONTAINS(config, '"comments"', '$.features') AS has_comments_feature
FROM app_config
WHERE app_name = 'my_web_app';
3. Event Logging and Analytics
JSON is useful for logging structured events:
CREATE TABLE event_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
event_time DATETIME DEFAULT CURRENT_TIMESTAMP,
event_data JSON
);
-- Log user activity event
INSERT INTO event_logs (event_data) VALUES (
'{
"user_id": 12345,
"action": "login",
"device": {
"type": "mobile",
"os": "iOS",
"version": "15.2"
},
"location": {
"city": "San Francisco",
"country": "US",
"coordinates": [37.7749, -122.4194]
}
}'
);
-- Analyze events by type and device
SELECT
JSON_EXTRACT(event_data, '$.action') AS action,
JSON_EXTRACT(event_data, '$.device.type') AS device_type,
COUNT(*) AS count
FROM event_logs
GROUP BY JSON_EXTRACT(event_data, '$.action'), JSON_EXTRACT(event_data, '$.device.type');
Performance Considerations
When working with JSON in MySQL, consider these performance tips:
-
Indexing: Create JSON virtual columns and index them for frequently queried properties
sqlALTER TABLE users_json
ADD COLUMN user_id INT GENERATED ALWAYS AS
(JSON_EXTRACT(user_data, '$.id')) STORED,
ADD INDEX (user_id); -
Use JSON path expressions for efficient data extraction
-
Be careful with large JSON documents as they can impact performance
-
Consider normalization for data that is frequently queried or updated
-
Batch processing for large imports/exports
Common Issues and Solutions
Invalid JSON Formatting
When importing JSON, you might encounter errors due to invalid formatting:
-- This will fail due to invalid JSON
INSERT INTO users_json (user_data)
VALUES ('{name: "John"}'); -- Missing quotes around property name
-- Correct version
INSERT INTO users_json (user_data)
VALUES ('{"name": "John"}');
Handling Special Characters
Special characters in JSON strings need proper escaping:
-- Properly escape special characters in JSON
INSERT INTO users_json (user_data)
VALUES ('{"message": "This is a \"quoted\" text with a backslash \\\\ character"}');
Converting Between JSON and String Types
Sometimes you need to convert between JSON and string representations:
-- Convert string to JSON
SELECT CAST('{"name": "John", "age": 30}' AS JSON) AS json_data;
-- Convert JSON to string
SELECT CAST(user_data AS CHAR) AS json_string FROM users_json LIMIT 1;
Summary
In this guide, we've explored how to effectively import JSON data into MySQL and export MySQL data as JSON. We've covered multiple methods for both operations, including:
- Using MySQL's JSON functions like
JSON_TABLEandJSON_ARRAYAGG - Loading JSON from files with
LOAD DATA INFILE - Exporting to JSON files with
INTO OUTFILE - Handling nested JSON structures
- Working with practical applications like API integration and configuration storage
MySQL's native JSON support provides powerful capabilities for working with structured data while maintaining the flexibility of schemaless formats. By mastering these techniques, you can effectively bridge the gap between relational databases and modern JSON-based applications.
Additional Resources and Exercises
Exercises
-
Create a table to store book information in JSON format (title, author, year, genres). Write queries to:
- Find all books published after 2000
- List books by a specific author
- Find books with a specific genre in their genre array
-
Import a JSON file containing product catalog data into MySQL, then create a stored procedure that extracts specific fields from the JSON into a normalized table structure.
-
Create a SQL query that exports customer and order data from separate tables into a nested JSON structure.
Resources for Further Learning
- MySQL Documentation: JSON Functions
- MySQL Documentation: The JSON Data Type
- MySQL Documentation: LOAD DATA INFILE Syntax
By mastering JSON import and export operations in MySQL, you'll be well-equipped to integrate database operations with modern web applications and APIs that rely heavily on JSON data interchange.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)