Free tools Windows power users keep installed
One-click scans. No signup required.
You can query local JSON and NDJSON files with SQL without first writing a parser. DuckDB exposes files as table functions: specify the file’s layout, inspect the inferred columns, then use normal SQL to select, filter, aggregate, and reshape records. The key first step is distinguishing a top-level JSON array from newline-delimited JSON, where each line is its own record.
Start by identifying the file’s layout
JSON is a data format, not a single file layout. A file might contain one top-level array of objects, or it might contain one independent JSON value on each line. Those layouts need different format handling. DuckDB documents both styles in its JSON loading reference and format guide.
As an Amazon Associate I earn from qualifying purchases.
- Top-level array: commonly begins with
[and contains records such as{"id":1}inside the array. Useformat = 'array'when you need to state the layout explicitly. - NDJSON (newline-delimited JSON): each line is a separate JSON record, often stored with a
.jsonlor.ndjsonextension. Useread_ndjsonor specifyformat = 'newline_delimited'.
For an initial look at a JSON file, DuckDB can infer the layout and columns:
SELECT *
FROM read_json('events.json')
LIMIT 10;For newline-delimited records, make the intended reader explicit and begin with a useful aggregate:
#1 Best Overall
SELECT event_type, count(*) AS events
FROM read_ndjson('events.jsonl')
GROUP BY event_type
ORDER BY events DESC;DuckDB also documents reading a list of files or a glob pattern. Exact defaults and accepted options can vary by DuckDB version, so check the current loading reference if a query behaves differently in your installed release.
Inspect inferred columns before relying on them
Automatic schema detection is convenient for exploration, but it is an inference, not a guarantee that every file has the schema you want. First inspect the returned names and types; then decide whether inferred types are suitable for your calculations and whether the query should read only a defined set of fields.
When a file is NDJSON and you want control over the projected columns and their types, specify the format and columns:
SELECT id, event_type
FROM read_json(
'events.jsonl',
format = 'newline_delimited',
columns = {id: 'UBIGINT', event_type: 'VARCHAR'}
);This gives the query a deliberate relational shape rather than relying entirely on inference. Choose types that fit the actual values in your data; an explicit type is not a repair for values that cannot be represented by it.
Handle changing shapes across files
For a collection of files with inconsistent fields or types, schema controls help manage the differences. DuckDB documents sample_size and maximum_depth for schema detection, and union_by_name for unifying schemas across multiple files. Consult the loading options reference for the current option syntax and defaults.
- Fields appear in only some records: when a key is missing, its value can appear as
NULLin the resulting relational data. Account for that in filters and aggregates. - Inference misses a less common shape: schema detection examines data to infer columns; its sample-size option is relevant when the inspected records do not represent the full set of shapes.
- Nested objects become too wide or deep: the maximum-depth option controls how far schema detection descends. For selected nested values, JSON extraction or transformation may be easier to manage than expanding everything into columns.
- Files have different columns: use
union_by_namewhen combining files whose schemas need to be aligned by column name.
These controls make variation manageable, but they do not make incompatible values equivalent. Decide how to treat missing keys, type changes, and malformed or unexpected values based on the meaning of the data you are analyzing.
Choose a method for nested objects and arrays
DuckDB offers several ways to work with nested JSON. Use scalar extraction for a few values, transform JSON into nested SQL types for repeated analysis, or expand objects and arrays into rows when their elements need to be examined individually. The JSON functions reference documents these tools.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsExtract a scalar with a JSON path
If payload is a JSON value containing a nested customer object, extract a string such as the customer’s name:
SELECT json_extract_string(payload, '$.customer.name') AS customer_name
FROM events;Expand an array or object into rows
json_each produces rows for the elements at a selected path. For example, this query expands each event’s items array and returns each item’s key and value alongside the event ID:
SELECT e.id, item.key, item.value
FROM events AS e,
json_each(e.payload, '$.items') AS item;The table function refers to the preceding FROM item, e; this is lateral behavior, so it can expand the path separately for each event row. For deeper traversal, json_tree walks the JSON structure depth-first.
Transform JSON for repeated analysis
json_transform (also available as from_json) converts JSON into nested DuckDB LIST and STRUCT values. This can be useful when the same nested fields will be queried repeatedly using SQL’s typed operations, rather than extracted as individual JSON scalars each time.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Keep JSON and SQL collection indexing straight
DuckDB uses different indexing conventions for JSON and its SQL collection types. JSON array positions are zero-based, while DuckDB LIST and ARRAY positions are one-based. An index that selects the first element from JSON will therefore not select the first element from a LIST. Check the type before applying an index; the distinction is documented in DuckDB’s JSON overview.
Choose an engine based on where the data lives
For local files, DuckDB’s table functions provide a direct file-to-SQL workflow. PostgreSQL and BigQuery are relevant alternatives when the records already belong in those systems; neither is the same workflow as querying a local file directly with DuckDB.
| Engine | Best fit in this context | Documented approach |
|---|---|---|
| DuckDB | Local JSON and NDJSON files | Read files with JSON table functions, then query the resulting rows with SQL. See the loading reference. |
| PostgreSQL 17 | JSON available to a PostgreSQL query | JSON_TABLE uses a JSON path row pattern and a COLUMNS clause to project JSON values into relational columns. See the PostgreSQL 17 JSON functions documentation. |
| BigQuery | Data loaded into Google Cloud’s managed warehouse | Supports a native JSON type and loading NDJSON with the NEWLINE_DELIMITED_JSON source format. See Google Cloud’s JSON data documentation. |
BigQuery’s current documentation states a 500-level nesting limit for its JSON type and lists restrictions such as not being able to partition or cluster on JSON columns; check the Google Cloud documentation for current service details. For JSON extraction there, prefer the documented JSON_QUERY and JSON_VALUE functions; the BigQuery JSON functions reference marks some older JSON_EXTRACT* functions as deprecated.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
