Di Jerry Fan

Importazione ed esportazione JSONL nei database: PostgreSQL, MongoDB e altri

Una guida completa per importare file JSONL nei database ed esportare i record in formato JSONL. Troverai i comandi COPY di PostgreSQL, gli strumenti da riga di comando di MongoDB, JSON_TABLE di MySQL, gli script per SQLite e le tecniche di importazione in blocco da usare in produzione.

Perché usare JSONL per importare ed esportare dati dai database?

JSONL (JSON Lines) è diventato uno dei formati di riferimento per trasferire dati tra database e sistemi esterni. Ogni riga è un oggetto JSON indipendente e completo: puoi quindi leggere i record in streaming, uno alla volta, senza caricare l’intero dataset in memoria. È una caratteristica fondamentale quando migri milioni di righe tra istanze PostgreSQL, sincronizzi collezioni MongoDB con un data warehouse o usi i dati esportati da un database come input per pipeline di machine learning.

Rispetto a CSV, JSONL mantiene i tipi di dati, supporta oggetti e array nidificati ed elimina le ambiguità legate all’escape dei delimitatori. Un file JSONL può contenere una colonna jsonb di PostgreSQL così com’è, mentre con CSV dovresti appiattire la struttura nidificata o applicarvi l’escape. A differenza di un unico array JSON, JSONL si presta all’elaborazione in streaming: puoi elaborare, validare o trasformare ogni record appena viene letto, senza aspettare che il parsing dell’intero file sia completato.

Tutti i principali database dispongono ormai di strumenti per JSONL. PostgreSQL può caricare le righe con COPY tramite un cast a jsonb; MongoDB include mongoimport e mongoexport, che usano JSONL come formato predefinito; MySQL 8.0 ha introdotto JSON_TABLE per estrarre dati strutturati; SQLite dispone dell’estensione JSON1. Nelle prossime sezioni troverai i comandi da eseguire, gli script e le best practice per ciascun database.

PostgreSQL: importare ed esportare dati JSONL

PostgreSQL offre diversi modi per lavorare con JSONL. Il comando COPY è la soluzione più veloce per l’importazione in blocco, mentre il tipo jsonb consente di memorizzare e interrogare dati semistrutturati in modo nativo. Se usi tabelle strutturate, puoi estrarre i campi JSONL e inserirli in colonne tipizzate durante l’importazione.

Il modo più veloce per importare JSONL in PostgreSQL è caricare ogni riga come valore jsonb con il comando COPY. Crea una tabella con una sola colonna jsonb, poi usa COPY FROM per caricare il file in streaming direttamente nel database.

Importare JSONL in una colonna jsonb
-- Create a staging table with a single jsonb column
CREATE TABLE staging_import (data jsonb);


-- Import the JSONL file (each line becomes one row)
\COPY staging_import (data) FROM 'users.jsonl';


-- Verify the import
SELECT count(*) FROM staging_import;
SELECT data->>'name' AS name, data->>'email' AS email
FROM staging_import
LIMIT 5;


-- Move data into a structured table
INSERT INTO users (name, email, age)
SELECT
  data->>'name',
  data->>'email',
  (data->>'age')::int
FROM staging_import;

Per esportare i dati in formato JSONL, usa row_to_json() o to_jsonb() per convertire ogni riga in un oggetto JSON, poi salva il risultato in un file con COPY. A ogni riga della tabella corrisponde una riga nel file di output.

Esportare le righe di PostgreSQL in formato JSONL
-- Export entire table as JSONL
\COPY (
  SELECT row_to_json(t)
  FROM (SELECT id, name, email, created_at FROM users) t
) TO 'users_export.jsonl';


-- Export with filtering and transformation
\COPY (
  SELECT row_to_json(t)
  FROM (
    SELECT id, name, email,
           created_at::text AS created_at
    FROM users
    WHERE active = true
    ORDER BY id
  ) t
) TO 'active_users.jsonl';


-- Verify: each line is valid JSON
-- {"id":1,"name":"Alice","email":"[email protected]",...}

Per i carichi di lavoro in produzione, usa uno script Python con psycopg2 per leggere un file JSONL e inserire i record in colonne tipizzate. In questo modo hai il pieno controllo sulla validazione, sulla gestione degli errori e sulle dimensioni dei batch.

Script Python per importare dati in colonne strutturate in PostgreSQL
import json
import psycopg2
from psycopg2.extras import execute_values


def import_jsonl_to_postgres(file_path, conn_string):
    conn = psycopg2.connect(conn_string)
    cur = conn.cursor()
    batch = []
    batch_size = 1000
    total = 0


    with open(file_path, 'r') as f:
        for line_num, line in enumerate(f, 1):
            line = line.strip()
            if not line:
                continue
            try:
                record = json.loads(line)
                batch.append((
                    record['name'],
                    record['email'],
                    record.get('age'),
                ))
            except (json.JSONDecodeError, KeyError) as e:
                print(f"Skipping line {line_num}: {e}")
                continue


            if len(batch) >= batch_size:
                execute_values(
                    cur,
                    "INSERT INTO users (name, email, age) "
                    "VALUES %s ON CONFLICT (email) DO NOTHING",
                    batch
                )
                total += len(batch)
                batch = []
                print(f"Imported {total} records...")


    if batch:
        execute_values(
            cur,
            "INSERT INTO users (name, email, age) "
            "VALUES %s ON CONFLICT (email) DO NOTHING",
            batch
        )
        total += len(batch)


    conn.commit()
    cur.close()
    conn.close()
    print(f"Done. Imported {total} records.")


# Usage
import_jsonl_to_postgres('users.jsonl', 'postgresql://localhost/mydb')

MongoDB: supporto nativo per JSONL

MongoDB offre un supporto completo per JSONL attraverso i Database Tools da riga di comando. Le utility mongoimport e mongoexport usano JSONL come formato predefinito: questo rende MongoDB uno dei database più semplici da usare per scambiare dati in questo formato. Ogni riga di un file JSONL corrisponde direttamente a un documento MongoDB.

mongoimport legge un file JSONL e inserisce ogni riga come documento nella collezione specificata. Supporta la modalità upsert, la conversione automatica dei tipi dei campi e l’inserimento in parallelo per ottenere un throughput elevato.

Importare JSONL con mongoimport
# Basic import: each line becomes a document
mongoimport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --file=users.jsonl


# Upsert mode: update existing documents by _id
mongoimport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --file=users.jsonl \
  --mode=upsert \
  --upsertFields=email


# Drop collection before import (clean slate)
mongoimport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --file=users.jsonl \
  --drop


# Import with parallel workers for large files
mongoimport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --file=large_users.jsonl \
  --numInsertionWorkers=4

mongoexport scrive ogni documento di una collezione su una singola riga JSON. Puoi filtrare i documenti, selezionare i campi da includere e ordinare l’output. Il risultato è un file JSONL valido, pronto per essere elaborato da qualsiasi sistema a valle.

Esportare dati da MongoDB in JSONL con mongoexport
# Export entire collection as JSONL (default format)
mongoexport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --out=users_export.jsonl


# Export with query filter
mongoexport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --query='{"active": true}' \
  --out=active_users.jsonl


# Export specific fields only
mongoexport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --fields=name,email,created_at \
  --out=users_partial.jsonl


# Export with sorting
mongoexport \
  --uri="mongodb://localhost:27017/mydb" \
  --collection=users \
  --sort='{"created_at": -1}' \
  --out=users_sorted.jsonl

MySQL: lavorare con JSONL tramite JSON_TABLE e script

A differenza di MongoDB, MySQL non dispone di un comando integrato per importare JSONL. MySQL 8.0 ha però introdotto JSON_TABLE, che permette di estrarre dati strutturati da stringhe JSON. Per importare JSONL in blocco, la soluzione più affidabile è combinare LOAD DATA INFILE per il caricamento dei dati grezzi con JSON_TABLE per l’estrazione, oppure ricorrere a un linguaggio di scripting come Python.

Carica il file JSONL come testo grezzo in una tabella di staging, poi usa JSON_TABLE per estrarre i campi e inserirli in una tabella strutturata. Questi due passaggi si eseguono interamente in MySQL.

Importare JSONL con una tabella di staging e JSON_TABLE
-- Step 1: Create a staging table for raw lines
CREATE TABLE jsonl_staging (
  id INT AUTO_INCREMENT PRIMARY KEY,
  raw_line TEXT NOT NULL
);


-- Step 2: Load the JSONL file as raw text
LOAD DATA INFILE '/var/lib/mysql-files/users.jsonl'
INTO TABLE jsonl_staging
LINES TERMINATED BY '\n'
(raw_line);


-- Step 3: Extract structured data with JSON_TABLE
INSERT INTO users (name, email, age)
SELECT jt.name, jt.email, jt.age
FROM jsonl_staging,
  JSON_TABLE(
    raw_line, '$'
    COLUMNS (
      name VARCHAR(255) PATH '$.name',
      email VARCHAR(255) PATH '$.email',
      age INT PATH '$.age'
    )
  ) AS jt;


-- Step 4: Clean up staging table
DROP TABLE jsonl_staging;


-- Verify the import
SELECT * FROM users LIMIT 5;

Se ti serve più flessibilità, usa uno script Python con mysql-connector-python per leggere un file JSONL e inserire i record in batch. Questo approccio gestisce in modo efficiente anche i file di grandi dimensioni e fornisce segnalazioni dettagliate degli errori.

Script Python per importare JSONL in MySQL
import json
import mysql.connector


def import_jsonl_to_mysql(file_path, config):
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    batch = []
    batch_size = 1000
    total = 0


    insert_sql = (
        "INSERT INTO users (name, email, age) "
        "VALUES (%s, %s, %s) "
        "ON DUPLICATE KEY UPDATE name=VALUES(name)"
    )


    with open(file_path, 'r') as f:
        for line_num, line in enumerate(f, 1):
            line = line.strip()
            if not line:
                continue
            try:
                record = json.loads(line)
                batch.append((
                    record['name'],
                    record['email'],
                    record.get('age'),
                ))
            except (json.JSONDecodeError, KeyError) as e:
                print(f"Skipping line {line_num}: {e}")
                continue


            if len(batch) >= batch_size:
                cursor.executemany(insert_sql, batch)
                conn.commit()
                total += len(batch)
                batch = []
                print(f"Imported {total} records...")


    if batch:
        cursor.executemany(insert_sql, batch)
        conn.commit()
        total += len(batch)


    cursor.close()
    conn.close()
    print(f"Done. Imported {total} records.")


# Usage
import_jsonl_to_mysql('users.jsonl', {
    'host': 'localhost',
    'user': 'root',
    'password': 'secret',
    'database': 'mydb'
})

SQLite: una soluzione leggera per importare JSONL

SQLite è un’ottima scelta per lo sviluppo locale e per dataset di dimensioni contenute. Non dispone di un comando nativo per importare JSONL, ma l’estensione JSON1 mette a disposizione json_extract() per lavorare con i dati JSON. Un semplice script Python può importare i file JSONL in modo efficiente sfruttando il supporto integrato di SQLite per le transazioni.

Usa il modulo sqlite3 incluso in Python per leggere un file JSONL e inserire i record in un database SQLite. Racchiudere tutti gli inserimenti in un’unica transazione migliora notevolmente le prestazioni con i file di grandi dimensioni.

Script Python per importare JSONL in SQLite
import json
import sqlite3


def import_jsonl_to_sqlite(jsonl_path, db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()


    # Create table if it doesn't exist
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE,
            age INTEGER,
            raw_json TEXT
        )
    """)


    total = 0
    errors = 0


    with open(jsonl_path, 'r') as f:
        # Use a transaction for bulk insert performance
        conn.execute('BEGIN TRANSACTION')
        for line_num, line in enumerate(f, 1):
            line = line.strip()
            if not line:
                continue
            try:
                record = json.loads(line)
                cursor.execute(
                    "INSERT OR IGNORE INTO users "
                    "(name, email, age, raw_json) "
                    "VALUES (?, ?, ?, ?)",
                    (
                        record['name'],
                        record.get('email'),
                        record.get('age'),
                        line,  # Store original JSON
                    )
                )
                total += 1
            except (json.JSONDecodeError, KeyError) as e:
                errors += 1
                print(f"Line {line_num}: {e}")


        conn.commit()


    conn.close()
    print(f"Imported {total} records, {errors} errors")


# Usage
import_jsonl_to_sqlite('users.jsonl', 'app.db')


# Query with json_extract (SQLite JSON1 extension)
# SELECT json_extract(raw_json, '$.name') FROM users;

Best practice per l’importazione in blocco

Quando importi file JSONL di grandi dimensioni in un database, devi prestare particolare attenzione alle transazioni, alla gestione degli errori e all’uso delle risorse. Queste best practice valgono per tutti i database e ti aiutano a creare pipeline di importazione affidabili, pronte per la produzione.

Raggruppare gli inserimenti in transazioni

Prestazioni

Non usare mai una transazione per ogni singola riga. Raggruppa gli inserimenti in batch di 500-5000 righe per transazione: l’I/O su disco e l’overhead dei lock si riducono di un fattore di 10-100x. Nella maggior parte dei database, i benefici maggiori si ottengono con batch di 1000 righe. Aumentare ulteriormente le dimensioni offre vantaggi via via minori e rende più costoso il rollback in caso di errore.

Gestire gli errori riga per riga

Affidabilità

Esegui il parsing e la validazione di ogni riga JSONL separatamente. Per ogni riga che non supera la validazione o non viene inserita, registra il numero di riga e il messaggio di errore. Usa ON CONFLICT / ON DUPLICATE KEY per gestire le violazioni dei vincoli di unicità senza interrompere l’intera importazione. Salva le righe che hanno generato errori in un file separato, così da poterle esaminare in seguito.

Monitorare l’avanzamento

Osservabilità

Per i file con milioni di righe, registra l’avanzamento ogni N record (ad esempio, ogni 10.000). Tieni traccia del totale delle righe lette, degli inserimenti riusciti, dei duplicati ignorati e degli errori. Calcola e mostra la velocità di importazione (record/secondo) per individuare i colli di bottiglia. Queste informazioni sono essenziali per monitorare le importazioni di lunga durata.

Questo pattern generico combina batch, gestione degli errori e monitoraggio dell'avanzamento. Adattalo a qualsiasi database sostituendo la funzione di inserimento.

Pattern di Import in Batch Pronto per la Produzione
import json
import time


def batch_import_jsonl(file_path, insert_fn, batch_size=1000):
    """Generic JSONL batch importer with error handling."""
    batch = []
    stats = {
        'total_lines': 0,
        'imported': 0,
        'skipped': 0,
        'errors': 0
    }
    start_time = time.time()
    error_file = open(file_path + '.errors', 'w')


    with open(file_path, 'r') as f:
        for line_num, line in enumerate(f, 1):
            stats['total_lines'] = line_num
            line = line.strip()
            if not line:
                stats['skipped'] += 1
                continue


            try:
                record = json.loads(line)
                batch.append(record)
            except json.JSONDecodeError as e:
                stats['errors'] += 1
                error_file.write(f"{line_num}\t{e}\t{line}\n")
                continue


            if len(batch) >= batch_size:
                inserted = insert_fn(batch)
                stats['imported'] += inserted
                batch = []


                if line_num % 10000 == 0:
                    elapsed = time.time() - start_time
                    rate = stats['imported'] / elapsed
                    print(
                        f"Progress: {line_num:,} lines | "
                        f"{stats['imported']:,} imported | "
                        f"{rate:,.0f} records/sec"
                    )


    # Flush remaining batch
    if batch:
        inserted = insert_fn(batch)
        stats['imported'] += inserted


    error_file.close()
    elapsed = time.time() - start_time
    print(f"\nComplete in {elapsed:.1f}s")
    print(f"  Lines: {stats['total_lines']:,}")
    print(f"  Imported: {stats['imported']:,}")
    print(f"  Errors: {stats['errors']:,}")
    return stats

Lavora con i file JSONL online

Visualizza, valida e converti file JSONL fino a 1GB direttamente nel browser. Non devi caricare nulla su un server: i tuoi dati restano privati al 100%.

Domande frequenti

Riferimenti