Acquire / file · All operations

Read a JSON file

Read JSON, NDJSON.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a new table, from no input table
Level
one clause of SQL

The DuckDB step

Read newline-delimited JSON

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 orders TO '<your_scratch_dir>/orders.json' (FORMAT json);
CREATE TEMP TABLE result AS
SELECT count(*) AS n, sum(amount) AS total FROM read_json('<your_scratch_dir>/orders.json');
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 10 AND total = 1900.74 FROM result)) THEN 'as expected' ELSE error('the JSON file did not read back as 10 rows totalling 1900.74') 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 orders.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 JSON file did not read back as 10 rows totalling 1900.74”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Pick rows out with a path expression

Choose which parts of the document become rows and columns by JSONPath expressions.

Walk a JSON value into key and value rows

Turn the members of a JSON value into one row per key, with its value, type and path.

Read a JSON file into rows and columns

Read a JSON document or array of objects into a table, one row per object and one column per field.

Read only the lines added since the last run

Read an append-only JSON file from where the previous run stopped, or as a stream.

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 (1)
  • JSONread 2026-09-27

    Read newline-delimited JSON files as a table

Apache Hop (2)
  • JSON Inputread 2026-09-27

    Read JSON from files, URLs or a field, extracting rows with JSONPath

  • JSON Normalize Inputread 2026-09-27

    Read a JSON array as rows, flattening nested objects into dotted column paths

Apache NiFi (2)
  • JsonPathReaderread 2026-09-27

    Read JSON into records by evaluating user-defined JsonPath expressions per object

  • JsonTreeReaderread 2026-09-27

    Read JSON into records by walking the JSON tree

Apache SeaTunnel (5)
  • Canal Formatread 2026-09-27

    Read Canal JSON change messages as insert, update and delete rows, and write them back

  • Debezium Formatread 2026-09-27

    Read Debezium JSON change messages as insert, update and delete rows, and write them back

  • Kafka source compatible kafka-connect-jsonread 2026-09-27

    Read Kafka messages that a Kafka Connect source produced

  • MaxWell Formatread 2026-09-27

    Read Maxwell JSON change messages as insert, update and delete rows, and write them back

  • Ogg Formatread 2026-09-27

    Read Oracle GoldenGate JSON change messages as insert, update and delete rows

AWS Glue (1)
  • JSON (read)read 2026-09-27

    Read JSON data from Amazon S3 and other supported connection types

Azure Data Factory (1)
DuckDB (9)
  • json_eachread 2026-09-27

    json_each table function

  • json_treeread 2026-09-27

    json_tree table function

  • read_jsonread 2026-09-27

    read_json table function

  • read_json_autoread 2026-09-27

    read_json_auto table function

  • read_json_objectsread 2026-09-27

    read_json_objects table function

  • read_json_objects_autoread 2026-09-27

    read_json_objects_auto table function

  • read_ndjsonread 2026-09-27

    read_ndjson table function

  • read_ndjson_autoread 2026-09-27

    read_ndjson_auto table function

  • read_ndjson_objectsread 2026-09-27

    read_ndjson_objects table function

Duckle (3)
  • JSONread 2026-09-25

    read JSON files with read_json

  • JSONL / NDJSONread 2026-09-25

    read newline-delimited JSON with read_json

  • Spool (tail NDJSON)read 2026-09-25

    tail an append-only NDJSON file from the byte offset of the last successful run

EasyMorph (1)
KNIME Analytics Platform (1)
  • JSON Readerread 2026-09-27

    Read .json files into a JSON column

OpenRefine (1)
  • JSONread 2026-09-27

    Read JSON into rows and columns

Pentaho Data Integration (1)
  • JSON Inputread 2026-09-27

    Extract parts of JSON structures from a file or field and emit rows

Polars (3)
  • read_jsonread 2026-09-27

    Read a JSON file into a frame

  • 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

Power Query M (1)
  • Json.Documentread 2026-09-27

    Parse JSON text or binary into nested records, lists and values

Talend (2)
VisiData (3)

Background

In DuckDB, the read_json function loads a JavaScript Object Notation (JSON) file as a table and deduces the types of its columns.[1] JSON is an open standard file format that uses human-readable text to store and transmit data objects made of name–value pairs and arrays.[2] In DuckDB, a file holding a JSON array of objects can be queried exactly the same way as a file holding the objects one after another.[1]

When to use it

The DuckDB JSON reader can infer which configuration flags to use by analysing the file, which works in most situations, and in rare ones it can be configured by hand.[1] Start with the reader's own detection, and name the format and the columns yourself only when it guesses wrong. (Meridian's view)

In DuckDB, read_json detects types from a sample of 20,480 objects by default, and setting its sample_size parameter to -1 scans the entire input file.[1] Where a field first appears, or changes type, deep inside a large file, widen the sample or state the columns rather than trust the sampled guess. (Meridian's view) In DuckDB, when read_json is given types for only a subset of columns through its columns parameter, it leaves out the columns that were not specified.[1] In DuckDB, keys missing from an object become NULL in the table read_json returns.[1] In DuckDB, when automatic detection would infer a STRUCT type for a field with more than 200 subfields, read_json infers a MAP type instead, a threshold set by its map_inference_threshold parameter.[1]

When the names within a JSON object are not unique, the behaviour of software that receives the object is unpredictable, and many implementations report only the last name/value pair.[3] JSON makes no distinction between integer and floating-point values, so some implementations may treat 42, 42.0 and 4.2E+1 as the same number while others may not.[2] The widely used JavaScript implementation stores JSON numbers as Institute of Electrical and Electronics Engineers (IEEE) 754 binary64 quantities, so applications should avoid transmitting numbers it cannot represent, such as 1E400.[2] Dates are not one of JSON's data types, so exchanging one unambiguously needs the producer and the consumer to agree on how it is serialised.[2] In DuckDB, the default iso setting of read_json's timestampformat parameter infers ISO 8601 timestamps with time zone offsets, such as 2024-01-01T12:00:00+05:00, as TIMESTAMPTZ.[1] Check the column types the reader chose before relying on them, above all for large integers, exact decimals and dates. (Meridian's view)

The method

JSON is a lightweight, text-based, language-independent data interchange format derived from the ECMAScript Programming Language Standard.[3] Douglas Crockford originally specified the JSON format in the early 2000s.[2] 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 STD 90 and remains consistent with ECMA-404.[2] JSON can represent four primitive types (strings, numbers, booleans and null) and two structured types (objects and arrays).[3] In JSON, an object is an unordered collection of zero or more name/value pairs, and an array is an ordered sequence of zero or more values.[3]

In DuckDB, read_json's format parameter can be auto, unstructured, newline_delimited or array, and with auto the reader attempts to determine the file's format itself.[1][4] In DuckDB, the unstructured format is for a file whose JSON is neither newline-delimited nor an array, such as pretty-printed objects written one after another.[4] In DuckDB, read_json's records parameter says whether the JSON holds records whose fields should be unpacked into individual columns, and DuckDB attempts to detect this too.[1] In DuckDB, JSON arrays can be converted directly to DuckDB's internal LIST type.[1]

Apache Hop's JSON Normalize Input transform emits one output row per element of an array of records, flattening nested objects into column paths joined by a separator that defaults to a dot, as in customer.name.[5]

Compared with the alternatives

In Apache Hop, the JSON Input transform suits one row per document with each column filled by its own path into the file, while JSON Normalize Input suits a document holding an array of similar objects, giving one row per element.[5]

Sources

  1. [1]Loading JSON – DuckDBdocumentation
  2. [2]JSON - Wikipediasecondary source
  3. [3]The JavaScript Object Notation (JSON) Data Interchange Formatstandard
  4. [4]JSON Format Settings – DuckDBdocumentation
  5. [5]JSON Normalize Input | Apache Hopdocumentation

Used in

4 Protocols use this operation: