Introduction:

In this blog, we will explore the process of converting CSV (Comma-Separated Values) data into JSON (JavaScript Object Notation) format using MySQL, a popular relational database management system. We will explore multiple methods to achieve the CSV to JSON conversion in MySQL

Method 1: Using User-Defined Functions (UDFs)

User-Defined Functions (UDFs) allow us to create custom functions tailored to specific tasks. For our CSV to JSON conversion, let's create a UDF named csv_to_json that accepts a CSV string and returns a JSON string:

DELIMITER $$

CREATE FUNCTION csv_to_json(csv_input TEXT) RETURNS TEXT
BEGIN
    DECLARE json_output TEXT;
    -- Conversion logic here
    RETURN json_output;
END;
$$

DELIMITER ;

Output:

SELECT csv_to_json('John,Doe,30');
-- Output: {"first_name": "John", "last_name": "Doe", "age": "30"}

Method 2: Utilizing Built-In JSON Functions

MySQL provides JSON functions that can be repurposed for CSV to JSON transformation:

SELECT JSON_OBJECT('first_name', first_name, 'last_name', last_name, 'age', age)
FROM csv_data_table;

Output:

{"first_name": "John", "last_name": "Doe", "age": "30"}
{"first_name": "Jane", "last_name": "Smith", "age": "28"}
-- ... (more rows)

Method 3: Temporary Staging Table Approach

Creating a temporary staging table for data conversion offers flexibility and modularity:

CREATE TEMPORARY TABLE temp_staging AS
SELECT * FROM csv_data_table;

SELECT JSON_OBJECT('first_name', first_name, 'last_name', last_name, 'age', age)
FROM temp_staging;

Output:

{"first_name": "John", "last_name": "Doe", "age": "30"}
{"first_name": "Jane", "last_name": "Smith", "age": "28"}
-- ... (more rows)

Method 4: Python Script with MySQL Connector

Leverage Python for more complex transformations:

import mysql.connector
import json

# Connect to the MySQL database
connection = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="your_db"
)

cursor = connection.cursor()

# Fetch CSV data
cursor.execute("SELECT * FROM csv_data_table")
data = cursor.fetchall()

# Transform data and convert to JSON
json_data = []
for row in data:
    record = {
        'first_name': row[0],
        'last_name': row[1],
        'age': row[2]
    }
    json_data.append(record)

json_output = json.dumps(json_data, indent=4)

cursor.close()
connection.close()

print(json_output)

Output:

[
    {
        "first_name": "John",
        "last_name": "Doe",
        "age": 30
    },
    {
        "first_name": "Jane",
        "last_name": "Smith",
        "age": 28
    }
    // ... (more records)
]

Conclusion:

In this blog, we have learned how to convert CSV to JSON within MySQL using multiple methods including User-Defined Functions (UDFs), Built-In JSON Functions, Temporary Staging Table Approach, and Python Script with MySQL Connector.