How to Query a Huge JSON File Without Writing a Script

You have a JSON file and a question about it. Which accounts are inactive? What’s the ID of the record that failed? How many events are there of each type? The file is 80 MB — too big to scroll, too big to eyeball — and your instinct is to open an editor, write data = json.load(open("data.json")), and start looping. For a question you’ll ask exactly once, that’s a lot of ceremony to reach one number. And if the file is big enough, json.load will sit there consuming memory and may fall over before it answers.

Here’s the reframe that saves the afternoon: getting a value out of JSON is a query, not a program. JSON has query languages, the same way a database does, and they run from a single command line. This post covers three that between them cover most of what comes up — jq, JSONPath, and DuckDB — when to reach for each, and what to do when the file genuinely won’t fit in memory.

The file we’ll use

Call it events.json: one object at the top, with an events array inside it. The printed sample has three events; picture the array holding two hundred thousand.

{
  "generatedAt": "2026-07-17T09:00:00Z",
  "events": [
    { "id": "e_1001", "type": "login",  "userId": 42, "ok": true,  "ms": 128 },
    { "id": "e_1002", "type": "upload", "userId": 42, "ok": false, "ms": 940 },
    { "id": "e_1003", "type": "login",  "userId": 7,  "ok": true,  "ms": 96  }
  ]
}

The questions we’ll answer against it are the ones that actually come up: read one value near the top, pull a field out of every item, keep only the items that match a condition, and count or average across the whole array.

jq: the default answer

jq is a small command-line program that parses JSON and runs a filter over it — a single binary you can grab with brew install jq, apt install jq, or from jqlang.org. A filter is an expression that describes the shape of the answer, and the simplest one is a path. Reading a single top-level value looks like the path you’d write in code:

jq '.generatedAt' events.json
# "2026-07-17T09:00:00Z"

The quotes are jq telling you the result is a JSON string. When you want the bare text — to pass it to another command — add -r for raw output:

jq -r '.generatedAt' events.json
# 2026-07-17T09:00:00Z

.events[] iterates the array, streaming each element into whatever comes after the pipe. So “every event’s type” is a path with an iteration in the middle:

jq -r '.events[].type' events.json
# login
# upload
# login

Filtering is select, which passes an item through only when its condition holds. The IDs of the events that failed:

jq -r '.events[] | select(.ok == false) | .id' events.json
# e_1002

Read that left to right: take each event, keep the ones where ok is false, then emit its id. Counting is wrapping a result in [ ] to collect it back into an array and taking its length:

jq '[.events[] | select(.type == "login")] | length' events.json
# 2

Four small pieces — a path, [] to iterate, select to filter, length to count — already answer most of the questions a big file provokes. When you just want a quick tally of a category, jq plus two classic Unix tools beats any of this:

jq -r '.events[].type' events.json | sort | uniq -c | sort -rn
#   2 login
#   1 upload

Reading a file that’s bigger than memory

There’s a limit hiding behind everything above. By default jq — like json.load — reads the entire document and builds the whole tree in memory before it evaluates anything. For an 80 MB file that’s fine. For one that’s larger than your RAM, it isn’t, and no clever filter changes that: the parser has to hold the value before the filter can run.

Two things get you past it. The first is the shape of the file. If the export is NDJSON — one JSON object per line, no wrapping array — then memory use tracks the size of a single record rather than the whole file, so it stays flat no matter how many records pile up, because tools read each line, process it, and discard it:

# events.ndjson: one object per line
jq -r 'select(.ok == false) | .id' events.ndjson

Its footprint is bounded by the largest single record, not the file size, so it holds steady as the file grows. If you control how the data is exported, emitting NDJSON instead of one giant array is one of the most useful changes you can make; from then on, processing it incrementally is straightforward.

If you’re stuck with one enormous array you didn’t create, you have to read from disk instead of building the whole tree. jq’s own --stream mode does exactly that: it walks the file emitting [path, value] events, and the fromstream(1 | truncate_stream(...)) idiom reassembles the top-level array one element at a time, so memory stays bounded:

# big.json is one giant top-level array — stream its elements instead of loading it
jq -rn --stream 'fromstream(1 | truncate_stream(inputs)) | select(.ok == false) | .id' big.json

Two limits are worth naming before you reach for it: that form only works when the top level is an array, and it’s built for pulling values out rather than combining them. It’s low-level and easy to get wrong, so once the job grows past extracting a field — real aggregation especially — DuckDB (below) or a streaming parser such as Python’s ijson is easier to maintain. Reach for any of these only when the file truly won’t fit; for anything that does, plain jq is simpler.

JSONPath: a path you can paste anywhere

jq has its own syntax, and it’s worth learning, but it only exists where jq is installed. JSONPath is a smaller idea — a path expression, nothing more — implemented in many languages and in a lot of tools, editors, and API clients. If you’ve ever written $.store.book[0].title, that’s JSONPath. It maps cleanly onto the same questions:

The trade-off is real and worth stating plainly. JSONPath selects — it points at values and hands them back. It doesn’t transform, reshape, or aggregate. There’s no standard JSONPath for “average ms grouped by type,” because grouping and averaging aren’t selection. So the rule of thumb is: reach for JSONPath when you want the value at a location, especially when the expression has to travel into code or a config where a jq dependency isn’t welcome, and reach for jq the moment the answer requires building something new out of what you selected.

One caveat about portability: JSONPath got a formal IETF standard in 2024, RFC 9535, but many libraries predate it or add their own extensions — and the filter syntax (?(@.ok == false)) is exactly where they diverge, over whitespace, quoting, and function support. The form shown here is the common one used by libraries like jsonpath-plus (which is what the evaluator linked below runs); check your target implementation’s docs before assuming an expression ports unchanged.

Group-by, averages, and joins: SQL over JSON

The moment the question turns into “how many of each,” “what’s the average,” or “which of these appears in that other file,” you’ve left selection behind and you’re doing analytics. jq can do it — grouping and averaging ms by type is one expression:

jq '.events
    | group_by(.type)
    | map({ type: .[0].type, count: length, avgMs: (map(.ms) | add / length) })' events.json

That works, and for a quick one-off it’s fine. But once the aggregation gets involved — several group keys, a join against another file, a sort on the aggregate — SQL is the language actually designed for the job, and DuckDB runs it over JSON straight from the command line (another single binary: brew install duckdb, or from duckdb.org). Point it at the file and unnest the array inside SQL:

duckdb -c "
  SELECT e.type, count(*) AS n, round(avg(e.ms)) AS avg_ms
  FROM (SELECT unnest(events) AS e FROM read_json_auto('events.json'))
  GROUP BY e.type
  ORDER BY n DESC"
# ┌────────┬───┬────────┐
# │  type  │ n │ avg_ms │
# ├────────┼───┼────────┤
# │ login  │ 2 │  112.0 │
# │ upload │ 1 │  940.0 │
# └────────┴───┴────────┘

read_json_auto samples the file and infers the columns and their types for you, so there’s nothing to declare up front; unnest then expands the events list into one row per event, and e.type reaches into each struct. Notice there’s no jq '.events' > tmp.json step first. That matters: pre-extracting with jq would itself load the whole file into memory and defeat the point on a file too big for it, so DuckDB reads the original straight from disk instead. (If the file already fits in memory, a jq pre-step is fine — it just isn’t the move for the huge case.) DuckDB is far more forgiving of size than a jq-into-memory pipeline, though a single monolithic top-level object still has to be parsed; the friendliest huge shape is NDJSON or a top-level array it can scan row by row. Beyond that, anything you’d write against a database table — WHERE, GROUP BY, JOIN two JSON files together, ORDER BY a computed column — works here, on the JSON file directly, with no schema to declare.

The trap: grepping for a value

There’s a tempting shortcut that quietly fails: grep '"userId"' events.json. It looks like it works on the small sample and betrays you on the real one. grep matches lines of text, and JSON isn’t organized by line — a value can sit on a different line from its key, an object can span twenty lines or none, and a minified file is a single line, so grep returns either the whole thing or nothing. It also can’t tell a key from a string value that happens to contain the same characters. The moment structure matters — and “get me the value at this key” is entirely about structure — you need something that reads JSON as structure rather than text, which is exactly what jq, JSONPath, and DuckDB do.

Iterating on an expression that fights back

The filters above are short because the questions were clean. Real ones get gnarly — a select with three conditions, a nested path you’re not sure about, a bracket you keep misplacing — and editing a long expression by rerunning it against an 80 MB file every time you change it is slow and blind. This is the one place a browser tool earns its keep. Paste a representative slice of the data into the JSONPath & jq Evaluator and both engines run as you type, with a live match count that tells you instantly whether your filter caught three items or thirty thousand — the quickest way to zero in on an expression. It holds the input in browser memory, so it’s for a representative sample rather than the whole gigabyte; once the expression is right, you run it over the full file with jq on disk.

When it won’t parse

All of this assumes the file is valid JSON. Exports often aren’t, quite — a truncated download, a trailing comma, or the NDJSON-versus-array mismatch, where a tool emits one object per line and you feed it to something expecting a single array (or the reverse). The parser fails before any query can run, so check syntax first: jq . file.json reads the whole document and, if it can’t, reports the line and column of the first error, which is usually all you need to spot the stray character or the wrong shape. Fix that, then query.

When you should write the script after all

Queries win for read-only questions. A script earns its place when the work stops being a question and becomes a process: joining several files with logic a single JOIN can’t express, looking each record up against an external API, transforming and writing the result back out, or anything you’ll run on a schedule and have to maintain. The line is roughly: if you’d delete the code the moment it prints the answer, it should have been a query; if you’ll run it again next week, write the script.

Which method, when

The reflex to write a script comes from treating JSON as a programming problem. Most of the time it’s a query problem — which items match, what’s the value at this key, how many of each — and treating it that way turns a ten-minute detour into one line you’ll delete the moment it answers.