Reading JSON Files

DuckDB reads JSON files directly through the json extension, which is installed and loaded automatically the first time a JSON function is used. JSON objects map naturally to SQL rows: each object becomes a row and each key becomes a column, with automatic type detection that works like the CSV reader.

Reading JSON with read_json_auto()

The read_json_auto() function samples the file, infers the structure and column types, and returns a queryable table:

SELECT * FROM read_json_auto('events.json');
-- equivalent shorthand for .json and .ndjson files
SELECT * FROM 'events.json';

For a newline-delimited JSON (.ndjson) file with one object per line, DuckDB produces one row per object, with columns such as id (BIGINT), event (VARCHAR) and ts (VARCHAR or TIMESTAMP) depending on the sampled values.

Choosing a JSON Format

The format option tells the reader what shape the file has. read_json() behaves like read_json_auto() but lets you pass options explicitly.

SELECT * FROM read_json('events.json',
    format = 'newline_delimited');

Overriding the Detected Columns

Sampling occasionally guesses wrong, for example reading big IDs as DOUBLE or dates as strings with the dateformat unset. Pass an explicit columns structure to fix the schema:

SELECT * FROM read_json('orders.json', columns = {
    id: 'INTEGER',
    placed_at: 'TIMESTAMP',
    total: 'DOUBLE'
});

Querying Non-Tabular JSON

For JSON that is not a flat list of records, read the raw text with read_text() or load it into a single VARCHAR column, then navigate with JSON path expressions:

SELECT json_extract_string(content, '$.settings.theme') AS theme
FROM read_text('config.json');

SELECT json_extract(t.content, '$.items[0].sku') AS first_sku
FROM read_text('order-42.json') t;

json_extract() keeps the value as JSON, while json_extract_string() and json_extract_int() coerce the result to a scalar SQL type. The path starts at the root with $.

Copying JSON In and Out

The COPY statement also understands JSON, which is handy for loading into an existing table with fixed types or exporting query results:

COPY events FROM 'events.ndjson' (FORMAT JSON);
COPY (SELECT * FROM events) TO 'out.json' (FORMAT JSON);

Summary

DuckDB queries JSON files as easily as CSV: read_json_auto() infers columns and types, the format option covers arrays, newline-delimited and unstructured files, an explicit columns structure overrides inference, and JSON path functions such as json_extract() reach into nested values. Combined with COPY ... (FORMAT JSON), DuckDB covers both entry and exit points of a JSON-based pipeline without any external preprocessing.