Acquire / file · All operations

Read newline-delimited JSON

A variant of Read a JSON file

Read a file holding one JSON object per line, as JSON Lines or NDJSON, into rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Read a file with one JSON object per line

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
COPY (SELECT order_id, customer, order_date, amount, string_split(tags, '|') AS tags FROM orders) TO '<your_scratch_dir>/lines.ndjson' (FORMAT json);
CREATE TEMP TABLE result AS
SELECT * FROM read_json('<your_scratch_dir>/lines.ndjson', format = 'newline_delimited');
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FROM read_text('<your_scratch_dir>/lines.ndjson') t, unnest(string_split(trim(content, chr(10)), chr(10)))) = 10 AND (SELECT count(*) = 10 AND count(amount) = 9 AND round(sum(amount), 2) = 1900.74 AND count(tags) = 8 FROM result) AND (SELECT list(column_type) FROM (DESCRIBE result)) = ['BIGINT', 'VARCHAR', 'DATE', 'DOUBLE', 'VARCHAR[]'] AND (SELECT tags FROM result WHERE order_id = 10) = ['a', 'b', 'c']) THEN 'as expected' ELSE error('the file did not read as one row for each of its 10 lines, with the tags a list and the date a DATE') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes lines.ndjson itself, in <your_scratch_dir>.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the file did not read as one row for each of its 10 lines, with the tags a list and the date a DATE”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
  • JSONread 2026-09-27

    Read newline-delimited JSON files as a table

DuckDB (2)
  • read_ndjsonread 2026-09-27

    read_ndjson table function

  • read_ndjson_autoread 2026-09-27

    read_ndjson_auto table function

Duckle (1)
  • JSONL / NDJSONread 2026-09-25

    read newline-delimited JSON with read_json

Polars (2)
  • read_ndjsonread 2026-09-27

    Read a newline-delimited JSON file into a frame

  • scan_ndjsonread 2026-09-27

    Lazily read one or many newline-delimited JSON files, by glob pattern

VisiData (2)

Background

JSON Lines, or newline-delimited JSON (NDJSON), holds one JavaScript Object Notation (JSON) value per line, so data may be processed one record at a time.[1][2] Because a newline delimits each record, NDJSON works very well with traditional line-oriented Unix tools.[2]

When to use it

JSON Lines works well with Unix-style text processing tools and shell pipelines, for log files, and for passing messages between cooperating processes.[1] A common use of NDJSON is delivering multiple JSON texts through streaming protocols such as the Transmission Control Protocol (TCP) or Unix pipes, and it can also store semi-structured data.[3] In Amazon Web Services (AWS) Glue, the multiline option is false by default, which allows more aggressive file-splitting during parsing, and it must be set to true if any record spans multiple lines.[4] When you control how a large or growing file is written, prefer one record per line. (Meridian's view)

In NDJSON, a JSON text must not contain newlines or carriage returns.[3] Concatenated JSON, which sends objects with no delimiters for a streaming parser to extract, cannot be read by a line-delimited JSON parser when it contains newlines within an object.[2] The terms line-delimited JSON and newline-delimited JSON are often used without clarifying whether embedded newlines are supported.[2] In JSON Lines, each line must be a valid JSON value, so null is a valid line but a blank line is not.[1] An NDJSON parser may silently ignore empty lines, but that behaviour must be documented and should be configurable by the user of the parser.[3] Check what your reader does with an empty line before trusting a row count. (Meridian's view) In DuckDB, the COPY statement expects newline-delimited JSON by default when it reads or writes FORMAT json.[5]

The method

NDJSON and JSON Lines (JSONL) are two terms for equivalent formats of line-delimited JSON, and the old name for NDJSON was line-delimited JSON (LDJSON).[2] Newlines can delimit records because JSON does not allow return and newline characters inside primitive values, and in strings they must be escaped as \r and \n.[2] In NDJSON, each JSON text must conform to the Request for Comments (RFC) 8259 standard and be followed by the newline character \n, which may be preceded by a carriage return \r.[3] NDJSON data must use the UTF-8 encoding, and the media type application/x-ndjson and the file extension .ndjson are recommended.[3] Thorsten Hoeger, Chris Dew, Finn Pauls and Jim Wilson are named as the authors responsible for the NDJSON core specification.[3]

JSON Lines has three requirements: UTF-8 encoding, a valid JSON value on each line, and \n as the line terminator.[1] JSON Lines files may be saved with the file extension .jsonl, and a line terminator after the last value is strongly recommended but not required.[1] In DuckDB, read_ndjson is an alias for read_json with the format parameter set to newline_delimited.[5]

Compared with the alternatives

Concatenated JSON works with pretty-printed JSON but requires more effort and complexity to parse, and does not work well with traditional line-oriented tools.[2] Line-delimited JSON can be read by a parser that can handle concatenated JSON, since concatenated JSON streaming is a superset of line-delimited JSON streaming.[2] Length-prefixed JSON states the length of each message, which can speed up parsing because the parser need not search for delimiters.[2]

Sources

  1. [1]JSON Linessecondary source
  2. [2]JSON streaming - Wikipediasecondary source
  3. [3]GitHub - ndjson/ndjson-spec: Specification · GitHubstandard
  4. [4]Using the JSON format in AWS Glue - AWS Gluedocumentation
  5. [5]Loading JSON – DuckDBdocumentation