Publish / file · All operations

Write a JSON file

Write JSON, NDJSON.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

the table sent out, with nothing new to look at

Level

one clause of SQL

The DuckDB step

Write a JSON array

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, amount FROM orders) TO '<your_scratch_dir>/out.json' (FORMAT json, ARRAY true);
CREATE TEMP TABLE result AS
SELECT count(*) AS n, sum(amount) AS total, (SELECT left(content, 1) FROM read_text('<your_scratch_dir>/out.json')) AS opens_with FROM read_json('<your_scratch_dir>/out.json');
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 10 AND total = 1900.74 AND opens_with = '[' FROM result)) THEN 'as expected' ELSE error('the file is not a JSON array of 10 rows') 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 out.json 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 is not a JSON array of 10 rows”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache DataFusion (2)

Data Insert: JSONread 2026-09-27
Write results as JSON
write_jsonread 2026-09-27
DataFrame method: Execute and write the results to JSON files

Apache Hop (2)

Enhanced JSON outputread 2026-09-27
Build nested JSON blocks from grouped rows and write them to a file or field
JSON Outputread 2026-09-27
Build JSON blocks from rows and write them to a file or field

Apache NiFi (1)

JsonRecordSetWriterread 2026-09-27
Write a record set as a JSON array or as one JSON object per line

Apache SeaTunnel (1)

CDC Compatible Debezium-jsonread 2026-09-27
Publish change records to Kafka as Debezium JSON messages

AWS Glue (1)

JSON (write)read 2026-09-27
Write JSON files and folders to Amazon S3

Azure Data Factory (1)

JSON format (sink)read 2026-09-27
Write JSON files in copy activity and mapping data flow

Duckle (2)

JSONread 2026-09-25
write JSON with COPY
JSONL / NDJSONread 2026-09-25
write newline-delimited JSON with COPY

KNIME Analytics Platform (2)

JSON Row Combiner and Writerread 2026-09-27
Combine the JSON values of all rows into one document and write it to a file
JSON Writerread 2026-09-27
Write each JSON value of a column to a file in a directory

Pentaho Data Integration (1)

JSON outputread 2026-09-27
Build a JSON block from rows and write it to a field or file

Polars (3)

DataFrame.write_jsonread 2026-09-27
Write a frame as JSON
DataFrame.write_ndjsonread 2026-09-27
Write a frame as newline-delimited JSON
LazyFrame.sink_ndjsonread 2026-09-27
Run a lazy query in streaming mode and write the result to newline-delimited JSON

Talend (1)

tFileOutputJSONread 2026-09-27
Write rows as a JSON structure in an output file

VisiData (3)

json (saver)read 2026-09-23
Save as JSON with typed values
jsonl (saver)read 2026-09-23
Save as JSON Lines with typed values; also filetype ldjson, ndjson
jsonla (saver)read 2026-09-23
Save as JSON Lines of arrays

Background

In DuckDB, the COPY statement writes the contents of a table or the result of a query to a JavaScript Object Notation (JSON) file.^[1]^[2] JSON is a text format for the serialisation of structured data, derived from the object literals of JavaScript.^[2] DuckDB writes either a JSON array of records or newline-delimited JSON (NDJSON), and writes NDJSON unless its ARRAY option is true.^[3]

When to use it

JSON Lines, also called newline-delimited JSON, is a convenient format for structured data that may be processed one record at a time.^[4] JSON Lines works well with Unix-style text processing tools and shell pipelines, and is a flexible format for passing messages between cooperating processes.^[4] A JSON Lines file of objects lets applications split the file on newline boundaries for parallel loading, with each line fully describing one object.^[5] Reach for a JSON file when its reader is a program that expects JSON, or when rows carry nested lists and objects that a flat file would have to flatten. (Meridian's view)

In DuckDB, COPY to a file named cities.json writes one object per line, such as {"name":"Amsterdam","id":1}, rather than one array.^[1] Name the layout the file's reader expects, one array or one value per line, rather than trusting the .json extension to say which it is. (Meridian's view)

JSON allows implementations to set limits on the range and precision of the numbers they accept.^[2] Software that implements IEEE 754 binary64 (double precision) numbers is generally available and widely used.^[2] Where software uses binary64 numbers, integers in the range [-(2**53)+1, (2**53)-1] are interoperable, in the sense that implementations agree exactly on their values.^[2] Consider writing very large identifiers as JSON strings rather than numbers, so a reader that holds numbers in double precision cannot change them. (Meridian's view) The JSON grammar does not permit numeric values such as Infinity and NaN (not-a-number).^[2]

The names within a JSON object should be unique, and software that receives an object with repeated names behaves unpredictably.^[2] Give every column a distinct name before writing a JSON file. (Meridian's view) JSON text exchanged between systems that are not part of a closed ecosystem must be encoded in UTF-8.^[2] A JSON Lines file is UTF-8 encoded, and a byte order mark (U+FEFF) must not be included.^[4]

The method

JSON can represent four primitive types (strings, numbers, booleans and null) and two structured types (objects and arrays).^[2] A JSON object is an unordered collection of zero or more name/value pairs, and a JSON array is an ordered sequence of zero or more values.^[2] Choose one format for dates and timestamps, and tell the file's readers which it is. (Meridian's view) In DuckDB, the DATEFORMAT and TIMESTAMPFORMAT options set the format used when writing dates and timestamps to a JSON file.^[3] In DuckDB, writing a column num_list holding the list [1, 2, 3] gives {"num_list":[1,2,3]}, a JSON array inside the row's object.^[3]

In a JSON string, the quotation mark, the reverse solidus and the control characters U+0000 to U+001F must be escaped.^[2] In a JSON Lines file, each line is a valid JSON value, so null is a valid line but a blank line is not.^[4] A line terminator after the last value in a JSON Lines file is strongly recommended but not required, and makes generating and concatenating files easier.^[4] JSON Lines files may be saved with the file extension .jsonl, and stream compressors like gzip or bzip2 are recommended for saving space.^[4]

In DuckDB, COPY ... TO overwrites the destination file if it already exists, without raising an error.^[3] By default, DuckDB writes to a temporary file first and replaces an existing destination only once the write completes, so an interrupted write does not corrupt the previous file.^[3] In DuckDB, the compression of a JSON file is detected from its extension by default, so file.json.gz is written with gzip and file.json.zst with zstd.^[3]

Douglas Crockford originally specified the JSON format in the early 2000s.^[6] JSON was first standardised in 2013 as ECMA-404, and Request for Comments (RFC) 8259, published in 2017, is the current version of the Internet Standard.^[6]^[2] NDJSON and JSON Lines are two terms for equivalent formats of line-delimited JSON.^[7]

Compared with the alternatives

JSON is non-concatenative: concatenating two JSON objects does not produce a valid JSON object.^[7] Line-delimited JSON is compatible with traditional line-oriented tools, while concatenated JSON works with pretty-printed JSON but needs more effort to parse.^[7] RFC 7464 defines a JSON text sequence as JSON texts each preceded by the record separator character and followed by a line feed.^[7]

JSON Lines records may use the standard JSON types, whereas in comma-separated values (CSV) string is the only type supported for cell values.^[5] Prefer JSON Lines to CSV when the reader must get numbers, booleans and nulls back as typed values rather than guessing them from text. (Meridian's view)

Sources

[1]Writing JSON – DuckDBdocumentation
[2]The JavaScript Object Notation (JSON) Data Interchange Formatstandard
[3]COPY Statement – DuckDBdocumentation
[4]JSON Linessecondary source
[5]JSON Lines | Examplessecondary source
[6]JSON - Wikipediasecondary source
[7]JSON streaming - Wikipediasecondary source