Von Jerry Fan

JSONL in Datenbanken importieren und exportieren: PostgreSQL, MongoDB und mehr

Ein umfassender Leitfaden zum Import von JSONL-Dateien in Datenbanken und zum Export von Datensätzen als JSONL. Mit COPY-Befehlen für PostgreSQL, CLI-Tools für MongoDB, JSON_TABLE für MySQL, SQLite-Skripten und bewährten Verfahren für Massenimporte im Produktivbetrieb.

Warum JSONL für den Datenbankimport und -export?

JSONL (JSON Lines) hat sich als Format für den Datenaustausch zwischen Datenbanken und externen Systemen etabliert. Jede Zeile enthält ein eigenständiges, in sich abgeschlossenes JSON-Objekt. Dadurch lassen sich Datensätze einzeln streamen, ohne den gesamten Datenbestand in den Arbeitsspeicher laden zu müssen. Das ist entscheidend, wenn Sie Millionen von Zeilen zwischen PostgreSQL-Instanzen migrieren, MongoDB-Collections mit einem Data Warehouse synchronisieren oder Datenbankexporte in Machine-Learning-Pipelines einspeisen.

Im Vergleich zu CSV erhält JSONL die Datentypen, unterstützt verschachtelte Objekte und Arrays und vermeidet Unklarheiten beim Escaping von Trennzeichen. Eine JSONL-Datei kann den Inhalt einer PostgreSQL-Spalte vom Typ jsonb unverändert übertragen. Bei CSV müssten Sie die verschachtelte Struktur dagegen abflachen oder maskieren. Anders als ein vollständiges JSON-Array lässt sich JSONL als Stream verarbeiten: Sie können jeden Datensatz direkt beim Einlesen verarbeiten, validieren oder transformieren, ohne darauf warten zu müssen, dass die gesamte Datei geparst ist.

Alle gängigen Datenbanksysteme bieten mittlerweile Tools für JSONL. PostgreSQL kann Zeilen mit COPY laden und dabei in jsonb umwandeln. MongoDB bringt mongoimport und mongoexport mit, die standardmäßig JSONL verwenden. MySQL bietet seit Version 8.0 JSON_TABLE zum Extrahieren strukturierter Daten, und für SQLite gibt es die JSON1-Erweiterung. Die folgenden Abschnitte zeigen Ihnen die konkreten Befehle, Skripte und Best Practices für jede Datenbank.

PostgreSQL: JSONL importieren und exportieren

PostgreSQL bietet mehrere Möglichkeiten, mit JSONL zu arbeiten. Mit COPY lassen sich Massenimporte am schnellsten durchführen. Der Datentyp jsonb ermöglicht es, semistrukturierte Daten nativ zu speichern und abzufragen. Beim Import in strukturierte Tabellen können Sie die JSONL-Felder direkt in Spalten mit festgelegten Datentypen übernehmen.

Am schnellsten importieren Sie JSONL in PostgreSQL, indem Sie jede Zeile mit COPY als jsonb-Wert laden. Erstellen Sie dazu eine Tabelle mit einer einzigen jsonb-Spalte und streamen Sie die Datei anschließend mit COPY FROM direkt in die Datenbank.

JSONL in eine jsonb-Spalte importieren
-- 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;

Wandeln Sie zum Export als JSONL jede Tabellenzeile mit row_to_json() oder to_jsonb() in ein JSON-Objekt um und schreiben Sie das Ergebnis anschließend mit COPY in eine Datei. Jede Tabellenzeile ergibt genau eine Zeile in der Ausgabedatei.

PostgreSQL-Zeilen als JSONL exportieren
-- 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]",...}

Für den Produktivbetrieb können Sie mit einem Python-Skript und psycopg2 eine JSONL-Datei einlesen und die Datensätze in Spalten mit festgelegten Datentypen einfügen. So haben Sie die volle Kontrolle über Validierung, Fehlerbehandlung und Batch-Größe.

Python-Skript für den Import in strukturierte PostgreSQL-Tabellen
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: Native Unterstützung für JSONL

Die Kommandozeilentools der MongoDB Database Tools bieten umfassende Unterstützung für JSONL. mongoimport und mongoexport verwenden standardmäßig das JSONL-Format. Damit gehört MongoDB zu den Datenbanken, mit denen sich der Datenaustausch über JSONL besonders einfach umsetzen lässt. Jede Zeile einer JSONL-Datei entspricht direkt einem MongoDB-Dokument.

mongoimport liest eine JSONL-Datei ein und fügt jede Zeile als Dokument in die angegebene Collection ein. Das Tool unterstützt den Upsert-Modus, die Konvertierung von Feldtypen und paralleles Einfügen für einen hohen Durchsatz.

JSONL mit mongoimport importieren
# 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 schreibt jedes Dokument einer Collection in eine einzelne JSON-Zeile. Sie können die Ausgabe filtern, die auszugebenden Felder auswählen und die Sortierung festlegen. Das Ergebnis ist eine gültige JSONL-Datei, die jedes nachgelagerte System weiterverarbeiten kann.

MongoDB-Daten mit mongoexport als JSONL exportieren
# 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: JSONL mit JSON_TABLE und Skripten

MySQL hat anders als MongoDB keinen integrierten Importbefehl für JSONL. Seit MySQL 8.0 können Sie aber mit JSON_TABLE strukturierte Daten aus JSON-Strings extrahieren. Für zuverlässige JSONL-Massenimporte kombinieren Sie LOAD DATA INFILE zum Laden der Rohdaten mit JSON_TABLE zur Extraktion oder verwenden eine Skriptsprache wie Python.

Laden Sie die JSONL-Datei zunächst als unverarbeiteten Text in eine Staging-Tabelle. Extrahieren Sie anschließend die Felder mit JSON_TABLE in eine strukturierte Tabelle. Beide Schritte lassen sich vollständig in MySQL ausführen.

JSONL mit einer Staging-Tabelle und JSON_TABLE importieren
-- 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;

Mehr Flexibilität bietet ein Python-Skript mit mysql-connector-python, das eine JSONL-Datei einliest und die Datensätze in Batches einfügt. Damit lassen sich große Dateien effizient verarbeiten und Fehler detailliert protokollieren.

Python-Skript für den JSONL-Import 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: Schlanker JSONL-Import

SQLite eignet sich hervorragend für die lokale Entwicklung und kleinere Datenbestände. Einen nativen JSONL-Importbefehl gibt es zwar nicht, die JSON1-Erweiterung stellt aber json_extract() für die Arbeit mit JSON-Daten bereit. Mit einem einfachen Python-Skript und der integrierten Transaktionsunterstützung von SQLite lassen sich JSONL-Dateien effizient importieren.

Mit dem in Python enthaltenen Modul sqlite3 können Sie eine JSONL-Datei einlesen und die Datensätze in eine SQLite-Datenbank einfügen. Wenn Sie alle INSERTs in einer einzigen Transaktion ausführen, verbessert das die Performance bei großen Dateien erheblich.

Python-Skript für den JSONL-Import 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 Practices für Massenimporte

Beim Import großer JSONL-Dateien in eine Datenbank müssen Sie Transaktionen, Fehlerbehandlung und Ressourcenverwaltung sorgfältig planen. Die folgenden Best Practices gelten für alle Datenbanksysteme und helfen Ihnen, zuverlässige Import-Pipelines für den Produktivbetrieb aufzubauen.

INSERTs in Transaktionen bündeln

Performance

Fügen Sie niemals nur eine Zeile pro Transaktion ein. Bündeln Sie die INSERTs in Batches von 500-5000 Zeilen pro Transaktion. Das reduziert die Datenträgerzugriffe und den Lock-Overhead um den Faktor 10 bis 100. Bei den meisten Datenbanken bringt eine Batch-Größe von 1000 die größten Verbesserungen. Größere Batches bringen zunehmend weniger zusätzlichen Nutzen und machen einen Rollback im Fehlerfall aufwendiger.

Fehler pro Zeile behandeln

Zuverlässigkeit

Parsen und validieren Sie jede JSONL-Zeile einzeln. Protokollieren Sie bei jeder Zeile, deren Validierung oder Einfügen fehlschlägt, die Zeilennummer und die Fehlermeldung. Verwenden Sie ON CONFLICT / ON DUPLICATE KEY, um Verletzungen von Unique Constraints abzufangen, ohne den gesamten Import abzubrechen. Speichern Sie die betroffenen Zeilen zur späteren Prüfung in einer separaten Fehlerdatei.

Fortschritt protokollieren

Observability

Protokollieren Sie bei Dateien mit Millionen von Zeilen den Fortschritt alle N Datensätze, zum Beispiel alle 10.000. Erfassen Sie die Gesamtzahl der gelesenen Zeilen, erfolgreich eingefügte Datensätze, übersprungene Duplikate und Fehler. Berechnen Sie die Importrate (Datensätze/Sekunde) und geben Sie sie aus, um Performance-Engpässe zu erkennen. Diese Rückmeldungen sind für die Überwachung lang laufender Importe unverzichtbar.

Dieses allgemeine Muster kombiniert die Verarbeitung in Batches mit Fehlerbehandlung und Fortschrittsprotokollierung. Tauschen Sie die Funktion zum Einfügen der Datensätze aus, um es an die jeweilige Datenbank anzupassen.

Batch-Import für den Produktivbetrieb
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

JSONL-Dateien online bearbeiten

Zeigen Sie JSONL-Dateien bis zu 1 GB direkt im Browser an, validieren und konvertieren Sie sie. Kein Upload erforderlich, 100 % privat.

Häufig gestellte Fragen

Quellen