Por Jerry Fan

Importação e exportação de JSONL em bancos de dados: PostgreSQL, MongoDB e mais

Um guia completo para importar arquivos JSONL em bancos de dados e exportar registros no formato JSONL. Inclui comandos COPY do PostgreSQL, ferramentas de linha de comando do MongoDB, JSON_TABLE do MySQL, scripts para SQLite e padrões de importação em massa prontos para produção.

Por que usar JSONL para importar e exportar dados em bancos de dados?

JSONL (JSON Lines) se tornou um dos formatos preferidos para transferir dados entre bancos de dados e sistemas externos. Cada linha contém um objeto JSON independente e completo, o que permite processar os registros em streaming, um por vez, sem carregar todo o conjunto de dados na memória. Isso é essencial ao migrar milhões de linhas entre instâncias do PostgreSQL, sincronizar coleções do MongoDB com um data warehouse ou usar dados exportados de bancos de dados em pipelines de aprendizado de máquina.

Em comparação com CSV, JSONL preserva os tipos de dados, aceita objetos aninhados e arrays e elimina ambiguidades no uso de escapes para delimitadores. Um arquivo JSONL pode transportar o conteúdo de uma coluna jsonb do PostgreSQL sem nenhuma alteração; em CSV, seria necessário transformar a estrutura aninhada em uma estrutura plana ou usar escapes. Em comparação com um array JSON completo, JSONL permite processamento em streaming: você pode processar, validar ou transformar cada registro assim que ele é lido, sem esperar pela análise de todo o arquivo.

Todos os principais sistemas de banco de dados já contam com ferramentas para JSONL. O PostgreSQL permite copiar linhas com COPY e conversão para jsonb; o MongoDB inclui mongoimport e mongoexport, que usam JSONL por padrão; o MySQL 8.0 introduziu JSON_TABLE para extração de dados estruturados; e o SQLite conta com a extensão JSON1. Nas próximas seções, você encontrará os comandos, scripts e boas práticas para cada banco de dados.

PostgreSQL: importação e exportação de JSONL

O PostgreSQL oferece várias formas de trabalhar com JSONL. O comando COPY é a opção mais rápida para importação em massa, enquanto o tipo jsonb permite armazenar e consultar dados semiestruturados de forma nativa. Em tabelas estruturadas, você pode extrair os campos do JSONL e convertê-los para os tipos das colunas durante a importação.

A maneira mais rápida de importar JSONL no PostgreSQL é usar o comando COPY para carregar cada linha como um valor jsonb. Crie uma tabela com uma única coluna jsonb e use COPY FROM para carregar o arquivo diretamente no banco de dados, em streaming.

Importar JSONL em uma coluna 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;

Para exportar dados como JSONL, use row_to_json() ou to_jsonb() para converter cada linha em um objeto JSON e, em seguida, grave o resultado em um arquivo com COPY. Cada linha da tabela corresponde a uma linha no arquivo de saída.

Exportar linhas do PostgreSQL como 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]",...}

Para cargas de trabalho em produção, use um script Python com psycopg2 para ler um arquivo JSONL e inserir os registros em colunas tipadas. Assim, você tem controle total sobre a validação, o tratamento de erros e o tamanho dos lotes.

Script Python para importar dados estruturados no 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: suporte nativo a JSONL

O MongoDB oferece amplo suporte a JSONL por meio do pacote de ferramentas de linha de comando Database Tools. Os utilitários mongoimport e mongoexport usam JSONL por padrão, o que torna o MongoDB um dos bancos de dados mais simples de usar para trocar dados nesse formato. Cada linha de um arquivo JSONL corresponde diretamente a um documento do MongoDB.

O mongoimport lê um arquivo JSONL e insere cada linha como um documento na coleção especificada. Ele oferece modo upsert, coerção de tipos dos campos e inserção em paralelo para processar grandes volumes de dados.

Importar JSONL com 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

O mongoexport grava cada documento de uma coleção em uma única linha JSON. Você pode filtrar os documentos, selecionar os campos e ordenar a saída. O resultado é um arquivo JSONL válido, pronto para ser processado por qualquer sistema nas próximas etapas do fluxo.

Exportar dados do MongoDB para JSONL com 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 com JSON_TABLE e scripts

O MySQL não tem um comando integrado para importar JSONL como o MongoDB, mas o MySQL 8.0 introduziu JSON_TABLE, que permite extrair dados estruturados de strings JSON. Para importar JSONL em massa, as opções mais confiáveis são combinar LOAD DATA INFILE para carregar o texto bruto com JSON_TABLE para extrair os dados, ou usar uma linguagem de script como Python.

Carregue o arquivo JSONL como texto bruto em uma tabela de staging e use JSON_TABLE para extrair os campos e inseri-los em uma tabela estruturada. Esse processo em duas etapas é executado inteiramente no MySQL.

Importar JSONL com uma tabela de 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;

Para ter mais flexibilidade, use um script Python com mysql-connector-python para ler um arquivo JSONL e inserir os registros em lotes. Essa abordagem processa arquivos grandes com eficiência e fornece informações detalhadas sobre os erros.

Script Python para importar JSONL no 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: uma opção leve para importar JSONL

O SQLite é uma excelente opção para desenvolvimento local e conjuntos de dados menores. Embora não tenha um comando nativo para importar JSONL, sua extensão JSON1 oferece json_extract() para trabalhar com dados JSON. Com um script Python simples e o suporte nativo do SQLite a transações, você pode importar arquivos JSONL com eficiência.

Use o módulo sqlite3, que faz parte da biblioteca padrão do Python, para ler um arquivo JSONL e inserir os registros em um banco de dados SQLite. Executar todas as inserções em uma única transação melhora muito o desempenho ao importar arquivos grandes.

Script Python para importar JSONL no 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;

Boas práticas para importação em massa

Importar arquivos JSONL grandes em um banco de dados exige cuidado com as transações, o tratamento de erros e o gerenciamento de recursos. Estas boas práticas se aplicam a todos os sistemas de banco de dados e ajudam você a criar pipelines de importação confiáveis e prontos para produção.

Transações em lote

Desempenho

Nunca insira apenas uma linha por transação. Agrupe as inserções em lotes de 500-5000 linhas por transação. Isso reduz o I/O de disco e o custo de gerenciamento de locks em 10-100x. Na maioria dos bancos de dados, os maiores ganhos ocorrem com lotes de 1000 linhas. Acima disso, os ganhos diminuem e o custo de um rollback aumenta caso algo falhe.

Tratamento de erros por linha

Confiabilidade

Faça o parsing e a validação de cada linha JSONL individualmente. Se uma linha falhar na validação ou na inserção, registre seu número e a mensagem de erro. Use ON CONFLICT / ON DUPLICATE KEY para tratar violações de restrições de unicidade sem interromper toda a importação. Salve as linhas com erro em um arquivo separado para analisá-las depois.

Acompanhamento do progresso

Observabilidade

Para arquivos com milhões de linhas, registre o progresso a cada N registros (por exemplo, a cada 10.000). Acompanhe o total de linhas lidas, as inserções bem-sucedidas, as duplicatas ignoradas e os erros. Calcule e exiba a taxa de importação (registros/segundo) para identificar gargalos de desempenho. Essas informações são essenciais para monitorar importações demoradas.

Este padrao generico combina agrupamento em lotes, tratamento de erros e rastreamento de progresso. Adapte-o para qualquer banco de dados substituindo a funcao de insercao.

Padrao de Importacao em Lote Pronto para Producao
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

Trabalhe com arquivos JSONL online

Visualize, valide e converta arquivos JSONL de até 1GB diretamente no seu navegador. Sem precisar enviar arquivos, com 100% de privacidade.

Perguntas frequentes

Referências