Door Jerry Fan

JSONL importeren en exporteren: PostgreSQL, MongoDB en meer

Een uitgebreide gids voor het importeren van JSONL-bestanden in databases en het exporteren van databaserecords als JSONL. Met COPY-commando’s voor PostgreSQL, CLI-tools voor MongoDB, JSON_TABLE in MySQL, SQLite-scripts en bulkimportpatronen voor productiegebruik.

Waarom JSONL gebruiken voor database-import en -export?

JSONL (JSON Lines) is een geliefd formaat geworden voor het uitwisselen van gegevens tussen databases en externe systemen. Elke regel bevat een zelfstandig, volledig JSON-object. Daardoor kun je records één voor één streamen zonder de hele dataset in het geheugen te laden. Dat is essentieel wanneer je miljoenen rijen tussen PostgreSQL-instanties migreert, MongoDB-collecties met een datawarehouse synchroniseert of database-exports als invoer voor machinelearning-pipelines gebruikt.

JSONL behoudt gegevenstypen, ondersteunt geneste objecten en arrays en voorkomt de onduidelijkheid rond het escapen van scheidingstekens die bij CSV kan ontstaan. Met een JSONL-bestand kun je de inhoud van een PostgreSQL-kolom van het type jsonb ongewijzigd overbrengen. Bij CSV moet je de geneste structuur eerst platmaken of escapen. Anders dan een volledige JSON-array kun je JSONL als stream verwerken: zodra je een record hebt gelezen, kun je het verwerken, valideren of transformeren. Je hoeft dus niet te wachten tot het hele bestand is geparseerd.

Alle grote databasesystemen hebben inmiddels tools voor JSONL. PostgreSQL kan rijen met COPY laden door ze naar jsonb te casten. MongoDB levert mongoimport en mongoexport mee, die standaard JSONL gebruiken. MySQL 8.0 introduceerde JSON_TABLE om gestructureerde gegevens uit JSON te halen, en SQLite heeft de JSON1-extensie. In de volgende onderdelen vind je per database de precieze commando’s, scripts en best practices.

PostgreSQL: JSONL importeren en exporteren

In PostgreSQL kun je op verschillende manieren met JSONL werken. Het COPY-commando is de snelste manier om grote hoeveelheden gegevens te importeren. Met het gegevenstype jsonb kun je semigestructureerde gegevens rechtstreeks opslaan en bevragen. Bij gestructureerde tabellen kun je de JSONL-velden tijdens het importeren omzetten naar kolommen met een vast gegevenstype.

De snelste manier om JSONL in PostgreSQL te importeren is elke regel met het COPY-commando als jsonb-waarde te laden. Maak een tabel met één jsonb-kolom en gebruik vervolgens COPY FROM om het bestand rechtstreeks naar de database te streamen.

JSONL importeren in een jsonb-kolom
-- 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;

Gebruik row_to_json() of to_jsonb() om elke rij naar een JSON-object om te zetten en schrijf het resultaat vervolgens met COPY naar een bestand. Zo exporteer je gegevens als JSONL: elke rij wordt één regel in het uitvoerbestand.

PostgreSQL-rijen exporteren als 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]",...}

Gebruik voor productieworkloads een Python-script met psycopg2 om een JSONL-bestand te lezen en records in kolommen met een vast gegevenstype in te voegen. Zo heb je volledige controle over de validatie, foutafhandeling en batchgrootte.

Python-script voor import in gestructureerde 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: ingebouwde ondersteuning voor JSONL

MongoDB ondersteunt JSONL volledig via de Database Tools voor de command line. De tools mongoimport en mongoexport gebruiken standaard JSONL. Daarmee is MongoDB een van de eenvoudigste databases voor gegevensuitwisseling via JSONL. Elke regel in een JSONL-bestand komt rechtstreeks overeen met een MongoDB-document.

mongoimport leest een JSONL-bestand en voegt elke regel als document toe aan de opgegeven collectie. De tool ondersteunt een upsert-modus, conversie van veldtypen en het parallel invoegen van documenten voor een hoge verwerkingssnelheid.

JSONL importeren met 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 schrijft elk document in een collectie als één JSON-regel. Je kunt de uitvoer filteren, velden selecteren en de resultaten sorteren. Het resultaat is een geldig JSONL-bestand dat direct door een volgend systeem kan worden verwerkt.

MongoDB-gegevens exporteren naar JSONL met 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: JSONL met JSON_TABLE en scripts

MySQL heeft geen ingebouwd JSONL-importcommando zoals MongoDB. Sinds MySQL 8.0 kun je wel met JSON_TABLE gestructureerde gegevens uit JSON-strings halen. Voor bulkimport van JSONL is de betrouwbaarste aanpak om de ruwe gegevens met LOAD DATA INFILE te laden en ze vervolgens met JSON_TABLE uit te lezen, of om een scripttaal zoals Python te gebruiken.

Laad het JSONL-bestand als ruwe tekst in een stagingtabel. Gebruik vervolgens JSON_TABLE om de velden uit te lezen en in een gestructureerde tabel te plaatsen. Beide stappen voer je volledig binnen MySQL uit.

JSONL importeren met een stagingtabel en 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;

Wil je meer flexibiliteit, gebruik dan een Python-script met mysql-connector-python om een JSONL-bestand te lezen en records in batches in te voegen. Deze aanpak verwerkt grote bestanden efficiënt en geeft gedetailleerde foutmeldingen.

Python-script voor 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: lichtgewicht JSONL-import

SQLite is een uitstekende keuze voor lokale ontwikkeling en kleinere datasets. Er is geen ingebouwd JSONL-importcommando, maar de JSON1-extensie biedt json_extract() om met JSON-gegevens te werken. Met een eenvoudig Python-script kun je JSONL-bestanden efficiënt importeren dankzij de ingebouwde transactieondersteuning van SQLite.

Gebruik de ingebouwde sqlite3-module van Python om een JSONL-bestand te lezen en records in een SQLite-database in te voegen. Door alle inserts binnen één transactie uit te voeren, verbeter je de prestaties bij grote bestanden aanzienlijk.

Python-script voor 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 voor bulkimport

Bij het importeren van grote JSONL-bestanden in een database moet je zorgvuldig omgaan met transacties, fouten en systeemresources. Deze best practices gelden voor alle databasesystemen en helpen je betrouwbare importpipelines te bouwen die geschikt zijn voor productiegebruik.

Transacties in batches

Prestaties

Voeg nooit maar één rij per transactie in. Bundel inserts in batches van 500-5000 rijen per transactie. Dat levert 10-100x minder schijf-I/O en overhead van locks op. Bij de meeste databases levert een batchgrootte van 1000 de grootste winst op. Grotere batches leveren steeds minder extra voordeel op en maken een rollback duurder als er iets misgaat.

Foutafhandeling per regel

Betrouwbaarheid

Parseer en valideer elke JSONL-regel afzonderlijk. Log het regelnummer en de foutmelding als de validatie of het invoegen van een regel mislukt. Gebruik ON CONFLICT / ON DUPLICATE KEY om schendingen van unique constraints af te handelen zonder de hele import af te breken. Bewaar de mislukte regels in een apart foutenbestand, zodat je ze later kunt nalopen.

Voortgang bijhouden

Observability

Log bij bestanden met miljoenen regels de voortgang na elke N records, bijvoorbeeld elke 10.000. Houd bij hoeveel regels er in totaal zijn gelezen, hoeveel inserts zijn geslaagd, hoeveel duplicaten zijn overgeslagen en hoeveel fouten er zijn opgetreden. Bereken en toon de importsnelheid in records/seconde om knelpunten in de prestaties op te sporen. Deze feedback is onmisbaar om langlopende imports te monitoren.

Dit algemene patroon combineert verwerking in batches, foutafhandeling en voortgangsregistratie. Je kunt het voor elke database aanpassen door de functie voor het invoegen van records te vervangen.

Batchimportpatroon voor productiegebruik
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

Online met JSONL-bestanden werken

Bekijk, valideer en converteer JSONL-bestanden tot 1GB rechtstreeks in je browser. Je hoeft niets te uploaden: alles blijft 100% privé.

Veelgestelde vragen

Bronnen