Query JSON and NDJSON Files with SQL in Your Browser

Query JSON and NDJSON Files with SQL in Your Browser

JSON is flexible, which is why messy exports often use it. API responses, log exports, and event streams can arrive as plain arrays or as newline-delimited records, and the SQL Workbench reads both through DuckDB without a conversion step.¹²

Two JSON shapes, one reader

JSON data comes in two common file layouts. A JSON array file wraps all records in a top-level array: the file starts with [ and ends with ].¹ Unlike the array format, an NDJSON (Newline-Delimited JSON) file places one complete JSON object on each line with no surrounding array, making it easy to stream and append without rewriting the file.² Both layouts are common: REST API exports usually produce arrays, while log aggregators and event buses (Kafka consumers, AWS Kinesis exports, Logstash outputs) typically produce NDJSON. From the .json, .jsonl, or .ndjson file extension and the file's first bytes, the workbench detects the format automatically. The workbench loads both layouts so you query them identically.

Type inference and nested field access

DuckDB infers column types from the JSON values it reads.³ String values become VARCHAR, numbers become BIGINT or DOUBLE, booleans become BOOLEAN, and null values are handled gracefully. The sampling window covers enough rows to produce a stable schema, yet very large files still load quickly because DuckDB streams the JSON rather than buffering every byte before parsing.

Nested objects, arrays, and JSONPath

Nested objects within each record (a user object inside an event record, for example) are loaded as DuckDB STRUCT columns.⁴ Access struct fields with dot notation: SELECT event.user.id, event.user.email FROM events. Arrays within records become LIST columns that you can flatten with UNNEST: SELECT UNNEST(tags) AS tag FROM articles.⁵ For deeply nested JSON where you want to extract a specific path without unnesting entire structures, json_extract_string(payload, '$.user.address.city') navigates arbitrary nesting levels in a single expression without restructuring the file.⁴ This path-based extraction is especially useful when only a handful of fields matter inside a deeply nested payload.

Mixing the two approaches in one query is common: flatten a list with UNNEST for rows you want to group, then reach into a sibling struct with dot notation for the value you want to aggregate. Because the struct and list types are real DuckDB types, you can pass them into GROUP BY or join them on a key exactly as you would with flat columns, which keeps deeply nested data as queryable as a plain table.

Practical queries for JSON data

API response files often contain pagination wrappers that put the actual records inside a nested key. If your JSON file looks like { "data": [...], "meta": {...} }, use SELECT UNNEST(data) AS record FROM api_response to flatten the records field. For log files, aggregating by event type quickly reveals the distribution: SELECT event_type, COUNT(*) FROM logs GROUP BY event_type ORDER BY COUNT(*) DESC. Filtering by timestamp works once you parse the date string: WHERE strptime(timestamp, '%Y-%m-%dT%H:%M:%SZ') > '2026-04-01'. Because DuckDB-Wasm uses a browser Worker component, the query runs without a server round trip.⁶ You can also combine multiple WHERE conditions to narrow the result set before exporting, which keeps the output file small and focused on the records that actually matter for your analysis.

Working with paginated and wrapped API responses

Working with a paginated API export where each page is a separate JSON file requires a preparation step before loading. Save each page as an NDJSON file with one record per line (using jq: jq -c '.data[]' page1.json > combined.ndjson && jq -c '.data[]' page2.json >> combined.ndjson), then load the combined NDJSON file. The workbench registers it as a single DuckDB view covering all pages. This approach avoids the one-file-per-session constraint without merging raw JSON arrays into a single enormous document that would be slow to parse and memory-intensive to load.

Verifying the flattened record schema after UNNEST

After flattening records from a wrapper with UNNEST, run DESCRIBE on the result to confirm all expected fields became top-level columns. If your JSON wrapper has deeply nested records, the unnested result may produce a struct column rather than flat columns. Access struct fields via dot notation: SELECT record.user_id, record.event_type FROM (SELECT UNNEST(data) AS record FROM api_export). Adding a LIMIT 5 before inspecting the types keeps the schema preview fast on large files.

Schema heterogeneity and missing fields in NDJSON files

In NDJSON log files from production systems, not every record shares the same set of fields. An error event might include a stack_trace field that success events omit entirely. DuckDB samples the first rows to infer the schema, then reads all records against that inferred schema.³ Fields present in the sampled rows become columns; fields that only appear in later records are not discovered and are silently excluded from the result.

Discovering late-arriving fields

For files where the first rows do not represent the full schema, discover all keys with SELECT DISTINCT UNNEST(json_object_keys(payload)) AS key FROM logs on any VARCHAR column holding raw JSON payloads. If your inferred schema is missing a field you know exists, check whether that field first appears well beyond the sample window. Increasing the sample with read_ndjson('file.ndjson', sample_size=10000) gives DuckDB more rows to infer from, and running both the discovery query and the wider sample in the workbench shows how to surface fields a JSON sample hid before you trust an aggregation built on the inferred schema.

When to use this

Use this when you have an API export, a log file, or an event stream saved as JSON or NDJSON and want to aggregate, filter, or inspect the data with SQL.

Examples

SELECT event_type, COUNT(*) AS n
FROM logs
GROUP BY event_type
ORDER BY n DESC;

SELECT payload.user_id,
       payload.action,
       payload.timestamp
FROM events
LIMIT 20;

SELECT id, UNNEST(tags) AS tag
FROM articles;

SELECT json_extract_string(metadata, '$.location.city') AS city,
       COUNT(*) AS n
FROM sessions
GROUP BY city
ORDER BY n DESC;