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.
-- 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.
-- 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.
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.
# 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=4mongoexport 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.
# 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.jsonlMySQL: 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.
-- 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.
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.
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
PrestatiesVoeg 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
BetrouwbaarheidParseer 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
ObservabilityLog 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.
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 statsBereid je gegevens voor met gratis JSONL-tools
Controleer het bestandsformaat en converteer waar nodig naar een ander formaat voordat je JSONL in je database importeert. Onze gratis online tools verwerken alles lokaal in je browser. Je gegevens verlaten dus nooit je computer.
Exporteer JSONL-data naar CSV-spreadsheets voor analyse in Excel of Google Sheets.
Valideer JSONL-bestanden regel voor regel. Spoor syntaxfouten, tekencoderingsproblemen en fouten in het formaat direct op.
Leer hoe je JSONL-bestanden valideert met JSON Schema. Behandelt Python, Node.js, CLI-tools en CI/CD-automatisering.
Strategieën om gigabytes aan JSONL-data te verwerken. Met aandacht voor lezen via streaming, geheugenbeheer, het splitsen van bestanden, compressie en online tools.
Leer hoe je JSONL als uitwisselingsformaat in ETL-pipelines gebruikt. Met uitleg over streaming met Apache Kafka, Airflow-DAG’s, het laden van data in Snowflake en BigQuery en het ontwerpen van fouttolerante pipelines.