Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Import JSON Data

Understand how to import line-delimited JSON data into a staging table using LOAD DATA INFILE, extract JSON fields for updating supplier records, and validate the results. This lesson covers JSON functions, conditional updates, and alternate parsing methods to handle JSON imports safely and effectively.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
CREATE TABLE IF NOT EXISTS SupplierKpi_Stage (
  RawDoc JSON NOT NULL
);
LOAD DATA INFILE '/var/lib/mysql-files/supplier_kpis.jsonl'
INTO TABLE SupplierKpi_Stage
LINES TERMINATED BY '\n'
(@json)
SET RawDoc = CAST(@json AS JSON);
UPDATE Suppliers AS s
JOIN   SupplierKpi_Stage AS st
ON s.Email = TRIM(REPLACE(JSON_UNQUOTE(JSON_EXTRACT(st.RawDoc, '$.Email')), CONVERT(0xC2A0 USING utf8mb4), ''))
SET    s.OnTimeDeliveryRate = CAST(JSON_EXTRACT(st.RawDoc, '$.OnTimeRate') AS DECIMAL(10,3)) * 100,
       s.AvgLeadTime =
              CASE
                WHEN NULLIF(JSON_UNQUOTE(JSON_EXTRACT(st.RawDoc, '$.AvgLeadTimeDays')), '') IS NULL
                  THEN s.AvgLeadTime
                ELSE CAST(JSON_UNQUOTE(JSON_EXTRACT(st.RawDoc, '$.AvgLeadTimeDays')) AS SIGNED)
              END;
/* Verification */
SELECT   SupplierName, Email, OnTimeDeliveryRate, AvgLeadTime
FROM     Suppliers
WHERE    Email IN ('info@techcorp.com', 'contact@autopro.com')
ORDER BY SupplierName;

Explanation

The explanation of the solution code is given below:

  • Lines 1–3: A staging table SupplierKpi_Stage is created if it does not already exist.

    • The table contains a single JSON column named RawDoc to store each raw JSON document as received.

    • Using a staging table isolates the import process from the main Suppliers table and allows safe parsing and validation.

  • Lines 5–9: The LOAD DATA INFILE statement imports the JSONL file into the staging table.

    • Each line of the file represents one JSON document.

    • LINES TERMINATED BY '\n' ensures that each line is treated as a separate record.

    • The raw line is captured in the user variable @json and then cast to valid JSON before being stored in RawDoc.

  • Lines 11–20: An UPDATE ... JOIN statement applies the imported KPI values to the Suppliers table.

    • The join condition matches suppliers using the email address extracted from the JSON document with JSON_EXTRACT and JSON_UNQUOTE. The join condition also removes non-breaking space characters that ...