著者: Jerry Fan

JSONLのデータベースへのインポートとエクスポート:PostgreSQL、MongoDBほか

JSONLファイルをデータベースにインポートする方法と、データベースのレコードをJSONL形式でエクスポートする方法を詳しく解説します。PostgreSQLのCOPYコマンド、MongoDBのCLIツール、MySQLのJSON_TABLE、SQLite用のスクリプトから、本番環境で使える一括インポートの実装パターンまで取り上げます。

データベースのインポートとエクスポートにJSONLを使う理由

JSONL(JSON Lines)は、データベースと外部システムの間でデータをやり取りする形式として広く使われています。各行がそれだけで完結した独立したJSONオブジェクトなので、データセット全体をメモリに読み込まずに、1レコードずつストリーム処理できます。PostgreSQLのインスタンス間で数百万行を移行する場合や、MongoDBのコレクションをデータウェアハウスに同期する場合、データベースからエクスポートしたデータを機械学習パイプラインに渡す場合には、この特性が欠かせません。

JSONLはCSVと異なり、データ型を保持でき、ネストしたオブジェクトや配列にも対応しています。区切り文字のエスケープに伴う曖昧さもありません。PostgreSQLのjsonbカラムの内容をそのままJSONLファイルに格納できますが、CSVではネストした構造をフラット化するか、エスケープする必要があります。また、JSON配列全体を扱う場合と異なり、JSONLはストリーム処理が可能です。ファイル全体のパースが終わるのを待たずに、レコードを読み込んだ時点で処理、検証、変換できます。

現在では、主要なデータベースはいずれもJSONLを扱うツールを備えています。PostgreSQLではjsonbへのキャストを介してCOPYで行を取り込めます。MongoDBにはJSONLをデフォルト形式とするmongoimportとmongoexportが付属しています。MySQL 8.0にはJSONから構造化データを抽出するJSON_TABLEが追加され、SQLiteにはJSON1拡張があります。以降のセクションでは、各データベースで使う具体的なコマンドやスクリプト、ベストプラクティスを紹介します。

PostgreSQL:JSONLのインポートとエクスポート

PostgreSQLでJSONLを扱う方法はいくつかあります。一括インポートにはCOPYコマンドが最も高速です。また、jsonb型を使えば、半構造化データをそのまま保存し、クエリを実行できます。型が定義されたカラムを持つテーブルには、インポート時にJSONLのフィールドをパースして格納できます。

JSONLをPostgreSQLに最も高速にインポートするには、COPYコマンドで各行をjsonb値として読み込みます。単一のjsonbカラムを持つテーブルを作成し、COPY FROMでファイルをデータベースに直接ストリームとして取り込みます。

JSONLを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;

データをJSONL形式でエクスポートするには、row_to_json()またはto_jsonb()で各行をJSONオブジェクトに変換し、COPYで結果をファイルに書き出します。テーブルの各行が、出力ファイルの1行に対応します。

PostgreSQLの行を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]",...}

本番環境では、psycopg2を使ったPythonスクリプトでJSONLファイルを読み込み、型が定義されたカラムにレコードを挿入します。この方法なら、検証、エラー処理、バッチサイズを細かく制御できます。

Pythonで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:JSONLを標準でサポート

MongoDBは、コマンドラインのDatabase ToolsでJSONLを手厚くサポートしています。mongoimportとmongoexportはデフォルトでJSONL形式を使うため、MongoDBはJSONLでのデータ交換を特に手軽に行えるデータベースの1つです。JSONLファイルの各行が、そのままMongoDBのドキュメントに対応します。

mongoimportはJSONLファイルを読み込み、各行をドキュメントとして指定のコレクションに挿入します。upsertモード、フィールドの型変換、スループットを高めるための並列挿入に対応しています。

mongoimportでJSONLをインポートする
# 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は、コレクション内の各ドキュメントを一行のJSONとして書き出します。出力するドキュメントの絞り込み、フィールドの選択、並べ替えも可能です。出力結果は正しい形式のJSONLファイルなので、後続のどのシステムでもそのまま処理できます。

mongoexportでMongoDBのデータをJSONL形式でエクスポートする
# 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:JSON_TABLEとスクリプトでJSONLを扱う

MySQLにはMongoDBのようなJSONL専用のインポートコマンドは組み込まれていませんが、MySQL 8.0で導入されたJSON_TABLEを使えば、JSON文字列から構造化データを抽出できます。JSONLを一括インポートするには、LOAD DATA INFILEでデータをそのまま読み込み、JSON_TABLEでフィールドを抽出する方法か、Pythonなどのスクリプト言語を使う方法が最も確実です。

JSONLファイルをそのままのテキストとしてステージングテーブルに読み込んでから、JSON_TABLEで各フィールドを抽出し、カラムが定義されたテーブルに格納します。この2段階の処理は、MySQL内だけで完結します。

ステージングテーブルとJSON_TABLEでJSONLをインポートする
-- 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;

より柔軟に処理したい場合は、mysql-connector-pythonを使ったPythonスクリプトでJSONLファイルを読み込み、レコードをバッチで挿入します。大きなファイルを効率よく処理でき、エラーの詳細も出力できます。

PythonでJSONLを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:軽量なJSONLインポート

SQLiteは、ローカルでの開発や小規模なデータセットの処理に適しています。JSONL専用のインポートコマンドはありませんが、JSON1拡張のjson_extract()でJSONデータを扱えます。また、SQLiteに組み込まれたトランザクション機能を使えば、シンプルなPythonスクリプトでJSONLファイルを効率よくインポートできます。

Python標準のsqlite3モジュールでJSONLファイルを読み込み、SQLiteデータベースにレコードを挿入します。すべての挿入を単一のトランザクションにまとめると、大きなファイルを処理する際のパフォーマンスが大幅に向上します。

PythonでJSONLを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;

一括インポートのベストプラクティス

大きなJSONLファイルをデータベースにインポートする際は、トランザクション、エラー処理、リソース管理に十分な配慮が必要です。ここで紹介するベストプラクティスは、どのデータベースにも共通して適用でき、本番環境で安定して動作するインポートパイプラインの構築に役立ちます。

トランザクションをバッチ単位にまとめる

パフォーマンス

1行ごとに1トランザクションを実行しないでください。1トランザクションあたり500〜5000行のバッチにまとめて挿入します。これにより、ディスクI/Oとロックのオーバーヘッドを10〜100分の一に抑えられます。多くのデータベースでは、バッチサイズを1000にしたときに最も大きな効果が得られます。それ以上大きくしても追加の効果は小さくなり、失敗時のロールバックの負担が増えます。

行単位でエラーを処理する

信頼性

JSONLの各行を個別にパースし、検証します。検証や挿入に失敗した場合は、その行番号とエラーメッセージをログに記録します。ユニーク制約違反でインポート全体が中断しないよう、ON CONFLICT / ON DUPLICATE KEYで適切に処理してください。失敗した行は、後で確認できるように別のエラーファイルに保存します。

進捗を記録する

可観測性

数百万行あるファイルでは、N件ごと(例:10,000件ごと)に進捗をログに記録します。読み込んだ総行数、挿入に成功した件数、重複のためスキップした件数、エラー件数を記録してください。インポート速度(レコード/秒)を計算して表示すると、パフォーマンスのボトルネックを特定できます。こうした情報は、長時間かかるインポートの監視に欠かせません。

この汎用パターンは、バッチ処理、エラー処理、進捗追跡を組み合わせています。insert関数を入れ替えることで、任意のデータベースに適応できます。

本番環境対応のバッチインポートパターン
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ツールでデータを準備する

JSONLをデータベースにインポートする前に、ファイル形式を検証し、必要に応じて形式を変換しましょう。当サイトの無料オンラインツールはすべての処理をブラウザ内で行うため、データが端末の外に送信されることはありません。

JSONLファイルをオンラインで扱う

最大1GBのJSONLファイルをブラウザで表示、検証、変換できます。アップロードは不要で、プライバシーは100%保護されます。

よくある質問

参考資料