Acquire / file · All operations

Read each JSON object as one value

A variant of Read a JSON file

Read the file without unpacking it, giving one JSON value per object or document for later parsing.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Read each JSON object and each document as one value

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 WHERE order_id <= 3) TO '<your_scratch_dir>/objects.ndjson' (FORMAT json);
COPY (SELECT order_id, customer, amount FROM orders WHERE order_id <= 3) TO '<your_scratch_dir>/objects_array.json' (FORMAT json, ARRAY true);
CREATE TEMP TABLE per_line AS SELECT json FROM read_json_objects('<your_scratch_dir>/objects.ndjson');
CREATE TEMP TABLE per_element AS SELECT json FROM read_json_objects('<your_scratch_dir>/objects_array.json', format = 'array');
CREATE TEMP TABLE per_document AS SELECT json FROM read_json_objects('<your_scratch_dir>/objects_array.json', format = 'unstructured');
SELECT * FROM per_line; SELECT * FROM per_element; SELECT * FROM per_document;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE per_line)) = ['json'] AND (SELECT any_value(column_type) FROM (DESCRIBE per_line)) = 'JSON' AND (SELECT count(*) FROM per_line) = 3 AND (SELECT count(*) FROM per_element) = 3 AND (SELECT count(*) FROM per_document) = 1 AND (SELECT json_extract_string(json, '$.customer') FROM per_line WHERE json_extract(json, '$.order_id') = 3) = 'Bob  Okafor' AND (SELECT json_array_length(json) FROM per_document) = 3) THEN 'as expected' ELSE error('the objects were not read as one JSON column of 3 values, nor the array file as 3 elements or as 1 document') 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 objects.ndjson and objects_array.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 objects were not read as one JSON column of 3 values, nor the array file as 3 elements or as 1 document”.
  • 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).

DuckDB (3)
  • 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_ndjson_objectsread 2026-09-27

    read_ndjson_objects table function

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

    Read .json files into a JSON column

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

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

Background

In KNIME, the JSON Reader node reads a .json file and parses it as a JSON value, giving a table of the JSON values read.[1] In Power Query M, Json.Document returns the content of a JSON document, passed as text or as the binary value a function like File.Contents returns.[2]

When to use it

Read objects whole when their shape varies from one to the next, or when you would rather choose fields after loading than let a reader fix a schema up front. (Meridian's view) In DuckDB, read_json with its records parameter set to false does not unpack top-level objects but creates STRUCTs instead, which is especially useful for non-object JSON such as arrays.[3] Keep the raw value beside any fields you extract from it, so a field you did not expect is still there to find later. (Meridian's view)

Following PostgreSQL's conventions, DuckDB uses 1-based indexing for its ARRAY and LIST data types but 0-based indexing for the JSON data type.[4] When you pull an array element out of a JSON value, count from zero, and check again once it has become a list. (Meridian's view)

The method

In DuckDB, the format parameter of read_json_objects reads the top-level JSON values with unstructured, one value per line with newline_delimited, and each array element with array.[5] DuckDB extracts parts of a JSON value with JSONPath and JSON Pointer syntax, both of which work with the arrow operator (->) and the json_extract function.[4] In DuckDB, -> with a JSONPath expression such as '$.family' extracts a value as JSON, and ->> extracts it as a VARCHAR.[4] DuckDB supports only lookups in JSONPath, extracting fields with .<key> or array elements with [<index>], because SQL is readily available for any further transformations.[4]

Sources

  1. [1]JSON Readerdocumentation
  2. [2]Accessing data functions - PowerQuery M | Microsoft Learndocumentation
  3. [3]JSON Format Settings – DuckDBdocumentation
  4. [4]JSON Overview – DuckDBdocumentation
  5. [5]Loading JSON – DuckDBdocumentation