Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 38 ยท Reading Files and Formats

Reading JSON

Query JSON and newline-delimited JSON with DuckDB: read_json auto-detection, nested STRUCT columns, the JSON type, json_extract and arrow operators, json_transform and flattening arrays.

JSON is how APIs, logs and event streams talk, and it is awkward for classic SQL: records nest, fields come and go, arrays hold variable numbers of items. DuckDB's JSON extension (loaded automatically when you need it) lets you read JSON files like tables and query JSON stored inside text columns.

DuckDB's file readers cannot run in the page, so they are read-only. SQLite ships a JSON library (json_extract, ->>, json_each), and the runnable stand-ins use it on JSON text held in a table, which closely matches DuckDB's functions for JSON inside a column.

Two common JSON file shapes

  1. A single JSON array of objects: [ {...}, {...} ].
  2. Newline-delimited JSON (NDJSON, JSON Lines): one object per line, common for logs and exports.
JSON
{"id": 1, "user": {"name": "Ana", "country": "PT"}, "tags": ["admin", "beta"], "amount": 19.99}
{"id": 2, "user": {"name": "Ben", "country": "BR"}, "tags": [], "amount": 5.5}

Read either with the same function. DuckDB detects the format, so these work with no options:

SQL
-- DuckDB (read-only example)
SELECT * FROM 'events.json';
SELECT * FROM read_json('events.ndjson');
SELECT * FROM read_json_auto('events.jsonl');

For the two lines above, DuckDB infers this schema, which you can check with DESCRIBE:

TEXT
id      BIGINT
user    STRUCT(name VARCHAR, country VARCHAR)
tags    VARCHAR[]
amount  DOUBLE

Nested objects become STRUCT columns, arrays become LISTs: exactly the types from Lists, structs and maps. You can then use dot access and UNNEST:

SQL
-- DuckDB (read-only example)
SELECT id, "user".name, "user".country, amount FROM 'events.json';

SELECT id, UNNEST(tags) AS tag FROM 'events.json';

(user is a reserved word in some contexts, so double quotes keep it safe.)

Options for read_json

Option Meaning
format = 'auto'/'newline_delimited'/'array'/'unstructured' How records are laid out
records = true/false Treat each top-level object as a row
columns = {...} Provide the schema, skip detection
maximum_depth = 2 Detect nested structure only to a given depth; deeper values stay JSON
sample_size, maximum_sample_files How much to inspect for the schema
ignore_errors = true Skip malformed lines
union_by_name = true Merge files with differing fields
timestampformat, dateformat Parse date strings
filename = true Add the source file name

Real JSON is irregular: some records lack a field, others add one. Detection merges all sampled objects into one schema; a missing field becomes NULL. If a field has different types in different rows (a number in one, a string in another) DuckDB falls back to VARCHAR or JSON for it.

The JSON type: queries on text columns

Sometimes JSON arrives inside a column, such as an attributes column of a database export. DuckDB's JSON type is a VARCHAR that is guaranteed to be valid JSON, with extraction functions:

SQL
-- DuckDB (read-only example)
SELECT payload -> '$.user.name'     AS name_as_json,     -- "Ana" (quotes kept: still JSON)
       payload ->> '$.user.name'    AS name_as_text,     -- Ana
       json_extract(payload, '$.tags[0]')      AS first_tag,
       json_extract_string(payload, '$.amount') AS amount_text,
       json_array_length(payload -> '$.tags')   AS tag_count,
       json_keys(payload -> '$.user')           AS user_fields
FROM events;

The two arrow operators matter: -> returns a JSON value, ->> returns plain text. The path language uses $ for the root, .name for fields and [n] for array positions (0-based in JSON paths).

SQLite supports the same operators and a similar function set, so this runs for real:

SQL Stand-in: query JSON stored in a column

Press Run (or Ctrl + Enter) to see the result here.

Aggregating JSON fields

Once a field is extracted, ordinary SQL applies. Cast to a number before summing:

SQL Stand-in: aggregate on extracted fields

Press Run (or Ctrl + Enter) to see the result here.

Turning JSON into real columns

Extracting the same fields in every query is tedious and slow, since JSON text is parsed each time. DuckDB's json_transform converts a JSON value to a typed structure in one step, and from_json is an alias:

SQL
-- DuckDB (read-only example)
SELECT json_transform(payload,
         '{"user": {"name": "VARCHAR", "country": "VARCHAR"}, "amount": "DOUBLE"}') AS rec
FROM events;

-- then unpack the struct into columns
SELECT unnest(rec) FROM (SELECT json_transform(payload, '{"amount": "DOUBLE"}') AS rec FROM events);

json_structure(payload) tells you the shape DuckDB infers, which is a quick way to write the schema string. The best pattern for repeated analysis is: parse once, then store the typed result as a table or Parquet.

SQL
-- DuckDB (read-only example)
CREATE TABLE events_typed AS
SELECT id,
       payload ->> '$.user.name'          AS user_name,
       (payload ->> '$.amount')::DECIMAL(10,2) AS amount
FROM raw_events;

Arrays of objects: unnest and flatten

An order with "lines": [{"sku": "a", "qty": 2}, ...] becomes a table of line items with UNNEST:

SQL
-- DuckDB (read-only example)
SELECT o.id, line.sku, line.qty
FROM (SELECT id, unnest(lines) AS line FROM 'orders.json') AS o;

With the text-based JSON type, json_each (in SQLite) and DuckDB's json_each / unnest(json_extract(...)::JSON[]) do the same job. The stand-in unnests the array in plain SQLite:

SQL Stand-in: one row per array element

Press Run (or Ctrl + Enter) to see the result here.

Writing JSON

COPY writes JSON too, either as an array or one object per line:

SQL
-- DuckDB (read-only example)
COPY (SELECT * FROM products) TO 'products.json';                     -- newline-delimited
COPY (SELECT * FROM products) TO 'products_array.json' (ARRAY true);
SELECT to_json({'a': 1, 'b': [1,2]});
SELECT json_group_array(name) FROM products;

Pitfalls

  • Huge single-array files must be parsed completely; NDJSON is friendlier and streams well.
  • Mixed types in one field cause fallback to text; clean up with columns = {...}.
  • Large integers or precise decimals: JSON numbers have no exact type. Declare DECIMAL for money in the schema.
  • Deep nesting: set maximum_depth low and extract deeper parts on demand.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does ->> return compared with ->?

    The double arrow extracts as text, the single arrow keeps the value as JSON, with quotes around strings.
  2. 2. How does read_json treat a nested object?

    Nested objects are detected as STRUCT columns, arrays as LISTs.
  3. 3. What does NDJSON mean?

    Each line is a separate JSON object, which makes the format easy to stream and append to.
  4. 4. How can you turn an array of objects inside each record into one row per element?

    UNNEST expands the list so every element becomes its own row.

Summary

  • read_json handles JSON arrays and NDJSON; objects become STRUCT, arrays become LIST.
  • Control detection with columns, format, maximum_depth and union_by_name.
  • For JSON in text columns, -> returns JSON, ->> returns text; paths use $.
  • json_transform converts JSON to typed structures; store the typed result for repeat use.
  • UNNEST flattens arrays of objects; COPY writes JSON out.

Next: Many files, globs and Hive partitions