Converting Data To and From JSONL

jq

jq -c '.[]' data.json > data.jsonl

The -c flag is not optional. Without it jq pretty-prints and you get invalid JSONL.

jq, Streaming for Files Too Big for Memory

jq -cn --stream 'fromstream(1|truncate_stream(inputs))' data.json > data.jsonl

Reconstructs one element at a time. Slower per record, but the file need not fit in RAM.

Python

import json

with open('data.json', 'r', encoding='utf-8') as fin, \
     open('data.jsonl', 'w', encoding='utf-8') as fout:
    for row in json.load(fin):
        fout.write(json.dumps(row, ensure_ascii=False) + '\n')

Set ensure_ascii=False to keep UTF-8 readable instead of escaped.

Python, Streaming with ijson

import ijson
import json

with open('data.json', 'rb') as fin, open('data.jsonl', 'w') as fout:
    for row in ijson.items(fin, 'item'):
        fout.write(json.dumps(row) + '\n')

The prefix item selects the elements of the top level array.

Node.js

const fs = require('fs');

const rows = JSON.parse(fs.readFileSync('data.json', 'utf8'));
const out = fs.createWriteStream('data.jsonl');

for (const row of rows) {
    out.write(JSON.stringify(row) + '\n');
}
out.end();

For very large inputs, swap the parse for a streaming parser such as the stream-json package.

jq

jq -s '.' data.jsonl > data.json

The slurp flag reads every line into one array, holding the whole file in memory.

Shell, Constant Memory

{ echo '['; sed '$!s/$/,/' data.jsonl; echo ']'; } > data.json

Appends a comma to every line but the last. It parses nothing, so only run it on a file you have already validated.

Python

import json

with open('data.jsonl', 'r', encoding='utf-8') as f:
    rows = [json.loads(line) for line in f if line.strip()]

with open('data.json', 'w', encoding='utf-8') as f:
    json.dump(rows, f, ensure_ascii=False, indent=2)

The if line.strip() guard skips blank trailing lines, which are common and would raise a decode error.

Node.js

import fs from 'node:fs';
import readline from 'node:readline';

const rl = readline.createInterface({
    input: fs.createReadStream('data.jsonl'),
    crlfDelay: Infinity
});

const rows = [];
for await (const line of rl) {
    if (line.trim()) rows.push(JSON.parse(line));
}

fs.writeFileSync('data.json', JSON.stringify(rows, null, 2));

Uses top level await, so save it as an ES module. crlfDelay keeps Windows line endings from producing empty lines.

DuckDB

COPY (
  SELECT * FROM read_json_auto('data.jsonl', format='newline_delimited')
) TO 'data.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

Raise the sample size, or declare columns explicitly with read_json, when the schema is not uniform.

Polars

import polars as pl

# Eager: loads the file
pl.read_ndjson('data.jsonl').write_parquet('data.parquet')

# Lazy: streams, for files larger than memory
pl.scan_ndjson('data.jsonl').sink_parquet('data.parquet')

Check your Polars version for the exact sink API, which has changed across releases.

pandas with PyArrow

import pandas as pd

df = pd.read_json('data.jsonl', lines=True)
df.to_parquet('data.parquet', engine='pyarrow', compression='zstd')

PyArrow Directly

import pyarrow.json as pj
import pyarrow.parquet as pq

table = pj.read_json('data.jsonl')
pq.write_table(table, 'data.parquet', compression='zstd')

PyArrow reads line delimited JSON natively and preserves structs and lists as real Parquet nested types.

DuckDB

COPY (SELECT * FROM 'data.parquet')
TO 'data.jsonl' (FORMAT JSON, ARRAY false);

DuckDB queries Parquet directly by path, so you can filter or project instead of dumping everything.

Polars

import polars as pl

pl.read_parquet('data.parquet').write_ndjson('data.jsonl')

pandas

import pandas as pd

df = pd.read_parquet('data.parquet')
df.to_json('data.jsonl', orient='records', lines=True, date_format='iso')

Set date_format='iso' or timestamps come out as epoch milliseconds, which is almost never what a consumer expects.

Load

CREATE TABLE events_raw (doc jsonb);

\copy events_raw (doc) FROM 'data.jsonl' WITH (FORMAT csv, QUOTE e'\x01', DELIMITER e'\x02')

INSERT INTO events (id, name, created_at)
SELECT (doc->>'id')::bigint,
       doc->>'name',
       (doc->>'created_at')::timestamptz
FROM events_raw;

A text mode COPY would interpret backslash sequences inside your JSON strings. Control characters avoid that.

Export

\copy (SELECT row_to_json(t)::text FROM (SELECT id, name, created_at FROM events) t) TO 'events.jsonl' WITH (FORMAT csv, QUOTE e'\x01', DELIMITER e'\x02')

Use jsonb_build_object instead of row_to_json to control key names and nesting.

Load

CREATE TABLE raw(doc TEXT);
.mode ascii
.separator "\x1f" "\n"
.import data.jsonl raw

CREATE TABLE users AS
SELECT json_extract(doc, '$.id')    AS id,
       json_extract(doc, '$.name')  AS name,
       json_extract(doc, '$.email') AS email
FROM raw;

A field separator that never appears in the data makes the importer treat each line as a single column value.

Export

sqlite3 app.db ".mode list" ".headers off" \
  "SELECT json_object('id', id, 'name', name, 'email', email) FROM users;" \
  > users.jsonl

SQLite is dynamically typed, so a column can hold an integer in one row and a string in the next. The JSONL will reflect that.