Turn a Folder of JSON Files Into SQL Tables
JSON is nested and SQL is flat, so the real question is not whether your logs can be read but what shape you get. Query Streams flattens nested objects into ordinary columns with a policy you can see and change — then you query the folder with SQL.
“id”: 90124,
“site”: { “region”: “eu-west” }
}}
Query Streams is a secure, real-time database integration platform that turns a folder of newline-delimited JSON files into live SQL tables you can query from Microsoft Excel, Google Sheets, Airtable and your AI assistant. Learn more at QueryStreams.com and sign up for free to query your first folder of JSON log files in minutes.
Reading JSON is not the hard part. Every tool can read JSON. The hard part is that JSON is a tree and a SQL table is a rectangle, so the moment you want to query JSON files with SQL — or get them into a spreadsheet — somebody has to decide how the tree becomes columns. Most tools make that decision silently, and you discover what they chose when a number is missing.
The JSONL Folder connector makes that decision explicit. It flattens nested objects into ordinary columns under a policy that is written into the connector, visible in the table definition, and adjustable with one setting. The folders this suits are the ones that fill up on their own: webhook captures, API dumps, application logs, and anything a queue or an event bus writes one record at a time.
What one JSON record actually becomes
This is the single most useful thing to look at, so here it is before anything else. On the left is one line from a JSONL file, pretty-printed for legibility. On the right are the columns you get from it at the default flatten depth.
{
"event_id": "evt_8812",
"ts": 1787903642,
"type": "order.paid",
"actor": { "id": 4417, "role": "customer" },
"order": {
"id": 90124,
"total": 149.99,
"site": {
"region": "eu-west",
"bay": { "aisle": "A7", "slot": 12 }
}
},
"tags": [ "priority", "gift" ]
}
Three things are worth noticing. Column names are the path joined with underscores, so order.site.region becomes order_site_region — a name you can type in a spreadsheet without quoting it. The bay object sits one level deeper than the policy allows, so it is kept whole as a JSON column rather than being dropped. And tags is an array, which is never flattened at any depth.
Each column also keeps its original dot path alongside the flattened name. The name is what you write SQL against; the dot path is what you address when you want to rename a column, retype it or exclude it. Nothing about the JSON is guessed at query time — the whole mapping is fixed when the folder is first connected.
order.site.region is a column — two objects were opened to reach a plain value — while order.site.bay would need a third and is pinned as JSON instead.
Choosing a flatten depth
Flatten depth is the setting worth thinking about for a minute before you save, because it decides the shape of the table rather than the way it is displayed. The default is 2, the floor is 1 and the ceiling is 8. Flattening cannot be switched off: 0 is refused when you save, so it is a mistake rather than a shortcut.
One level of object opened, and the lowest the setting goes. Useful when the payload is wide and you only read the top of it, leaving the rest as one JSON column to dip into.
Two levels of object opened. Enough for the envelope-plus-payload shape almost every event log uses, without exploding into hundreds of columns.
Eight levels, and no setting goes higher. Beyond that a record has stopped being tabular data, and anything still nested is kept as JSON rather than refused.
Raise the depth when the values you actually want to filter and total live below the cut — a warehouse identifier three levels down that you need in a GROUP BY is the usual reason. Leave it alone, or lower it, when the payload is deep but you only ever read the top of it: every extra level is more columns to look at, and a wide flattened table is harder to work with in a spreadsheet than a narrow one with a JSON column you dip into occasionally.
Changing the depth later is allowed and safe, but it is a change of definition rather than a display preference: the folder is re-sampled and the table is rebuilt against the new policy. That is deliberate. The alternative — quietly reinterpreting data that is already in the table — would mean the same query returned different columns depending on when it ran.
Depth is the only setting that decides how many columns you get, which is why it is pinned along with the shape. The rest of the card — the table name, the table layout, which files are in scope and the manifest overrides — is set out in the settings reference further down. An option the connector does not recognise is rejected by name rather than ignored, on the grounds that a setting silently doing nothing is indistinguishable from one that is working.
What is never flattened, and why that is the right answer
Some values have no honest column representation, and the policy keeps them as JSON regardless of the depth you set. Nothing is discarded — the value is preserved verbatim and you read into it with SQL when you need to.
| Value in the record | What you get |
|---|---|
| An array, at any depth | One JSON column holding the array exactly as written. Arrays have no fixed length, so there is no honest number of columns to spread them across. |
| An object deeper than the flatten depth you set | One JSON column holding that whole sub-object. |
| A wide, open-ended object read as a map | One JSON column. Objects used as dictionaries have keys that differ per record, so they are data rather than structure. |
| A value with no single type across the file | One JSON column, rather than a cast that would have to choose a winner. |
A JSON column is a normal column: it appears in the table definition, it lands in your spreadsheet, and the SQL engine’s JSON functions work on it. Reaching into one costs a line of SQL, not a re-configuration.
SELECT event_id,
order_site_region,
json_extract_string(order_site_bay, '$.aisle') AS aisle,
json_array_length(tags) AS tag_count
FROM events
WHERE type = 'order.paid'
AND json_extract_string(order_site_bay, '$.aisle') = 'A7';
If you find yourself writing that extraction in every query, that is the signal to raise Flatten depth by one and let order_site_bay_aisle be a real column instead.
The two ways flattening can fail
Flattening a tree into names is not always possible, and when it is not, the connector says so instead of producing a table that is subtly wrong. There are two failure modes worth recognising on sight, because both have a one-line fix.
A flattened path collides with a real column
Underscores are how the path becomes a name, which means two different paths can arrive at the same name. A record carrying both a top-level user_id and a nested user object with an id inside it produces user_id twice: once as itself, once from user.id.
There is no safe way to pin that. Picking one would mean losing the other, and worse, you would not be told which. So the connector refuses and names both paths: paths user_id and user.id both flatten to column user_id. Lower the depth so the nested one stays inside a JSON column, or rename one of them in the connector’s overrides — the dot path is exactly what an override addresses.
The same key is a value in some records and an object in others
This one is endemic to logs that have been through a schema change. Yesterday’s files carry "actor": "system". Today’s carry "actor": {"id": 4417, "role": "customer"}. One folder, one field, two irreconcilable shapes: actor cannot be both a column of its own and the parent of actor_id.
The connector detects that one path is a prefix of another and refuses, naming the field and an example of the conflict. The fix depends on what you want: narrow the file pattern so one connector covers the old shape and another covers the new, or switch the connector to one table per layout so the two eras get a table each.
http.status and db.system. Because the connector addresses columns by dot path, a field literally named http.status is indistinguishable from a status field inside an http object — and the read would return empty values rather than an error. That is exactly the kind of silently-wrong result the connector exists to prevent, so it refuses and names the field. Rename it in the producer, or exclude those files.
Two more disagreements are refused on the same principle. A field that is a number in one file and text in another is named rather than cast, because casting would have to pick a winner. And a file that cannot be read as newline-delimited JSON at all is recorded individually and skipped, so one corrupt log does not stop the other nine hundred from pinning.
| Situation | When it is caught | What happens |
|---|---|---|
| Two paths flatten to one column name | First connection | Refuses and names both paths. |
| A key is a value in one file, an object in another | First connection | Refuses and names the field. |
A field name contains a literal . | First connection, and again per file later | Refuses at first connection; a file that grows one later is parked. |
| A field’s type differs between files | First connection | Refuses, naming both types and both files. |
| A new field appears in one file | Every sync | That file is parked and the new path is named. |
| A file drops a field it used to have | Every sync | That file is parked, rather than reading nulls into your totals. |
| One malformed line in a file | Every sync | The whole file is parked. A file is all-in or all-out, never half. |
Refusals concern the whole folder and happen when you connect it; parking concerns one file and happens on any sync. Both are recorded, and the files_events table names the file and the exact disagreement — the general mechanics of pinning and parking are covered in how File Set connectors work.
JSONL, not JSON: what the folder must contain
The name matters here. This connector reads newline-delimited JSON — one complete JSON object per line, no commas between them and no wrapping brackets. It is the format almost every logger, queue consumer and streaming export already writes, whatever the file extension says.
{"event_id":"evt_8810","ts":1787903601,"type":"order.created"}
{"event_id":"evt_8811","ts":1787903622,"type":"payment.authorised"}
{"event_id":"evt_8812","ts":1787903642,"type":"order.paid"}
A single file containing one large JSON array — the pretty-printed [ { ... }, { ... } ] shape an API returns when you save its response — is not newline-delimited JSON, and this connector will not read it. It is recorded as unreadable when you connect the folder and parked on later syncs, with the reason given rather than left as an empty table. If that is what your folder holds, the practical fix is to have whatever writes the files emit one object per line, which is a one-line change in most exporters.
Encoding is not a question here, unlike a folder of CSV exports. JSON is UTF-8 by definition, so there is no encoding to detect and no setting to get wrong. Bytes that are not valid UTF-8 park the file before the parser is allowed near them. If your folder is delimited text rather than JSON, the sibling guide on combining multiple CSV files into one table covers the encoding detection that job needs.
Why every line is read, not just the first few
When the folder is first connected, the connector samples up to 64 files — and reads each of those files completely rather than peeking at the first few hundred lines. That is a deliberate cost, and the reason is specific to JSON.
A JSONL file has no header. Its shape is whatever its records happen to contain, so a bounded sample would make the column list depend on where in the file an unusual record happens to sit. Connect on Monday and order.discount_code appears at line 40; connect on Tuesday when it first appears at line 4,000 and the column is not there at all. Reading the whole file removes that coin-toss entirely.
The same full read happens on each changed file at sync time, for a sharper reason: a field that appears late in a file would otherwise vanish without trace. The strict typed read the connector uses ignores fields it was not told about, so an extra field past a sample boundary would not fail loudly — it would simply not be there. Reading the whole file means a new field parks the file and gets named. One extra parse per changed file buys that, and only changed files are ever re-read.
When the folder holds several event shapes
Event folders are frequently not one shape but several, because one consumer writes everything it sees to one directory. Rather than forcing agreement, switch the connector from one pinned table to one table per layout. Files are grouped by their exact flattened shape and each group gets its own table, up to 25 distinct layouts.
Two details are particular to JSON here. The flatten depth stays a single policy for the whole connector — one depth, applied to every layout — because the depth is what defines a shape in the first place; grouping by shape would be meaningless if each group could be flattened differently. And the cross-file conflicts described above stop being conflicts: a key that is a value in some files and an object in others simply produces two layouts, which is precisely what you wanted. Files that cannot be pinned on their own, such as one with a dotted field name, are skipped individually instead of stopping the rest.
Table names come from the filenames, with trailing dates and serial numbers trimmed, so a group of orders_2026_08_*.jsonl files lands in a table called orders. Names are fixed the first time they are assigned and are kept across later re-samples, so a table your saved queries already reference will not be renamed underneath them. A file whose shape matches none of the pinned layouts is parked until you touch the connector’s settings, which re-samples the folder and admits the new layout as an additional table.
Getting JSON into Microsoft Excel and Google Sheets
Once the folder is flattened it is an ordinary SQL table, which is what makes JSON to Excel a non-event: you run a saved query from the add-in and the rows land in the sheet. There is no import wizard, no expanding of record columns, and nothing to repeat next month — a refresh picks up every log file written since.
SELECT order_site_region,
COUNT(*) AS paid_orders,
SUM(order_total) AS gross
FROM events
WHERE type = 'order.paid'
AND ts >= 1785542400
GROUP BY order_site_region
ORDER BY gross DESC;
Every row also carries _source_file and a unique _row_id, so a figure that looks wrong can be traced to the exact log file that produced it — grouping by _source_file is usually the quickest way to find the day something changed. And because the folder is a connector like any other, the flattened event table joins to your PostgreSQL orders table or your Stripe payments in a single read-only statement, with nothing loaded into a warehouse first.
- Microsoft Excel and Google Sheets — run the saved query from the add-in and the flattened rows land in the sheet, refreshed on demand.
- Airtable, Smartsheet, Baserow, SeaTable and Anvil — scheduled syncs push log data into the tool your team already uses.
- Nova AI — ask a question about the logs in plain English and get SQL plus a chart.
- Claude, Cursor, ChatGPT and Grok — through the Query Streams MCP server, an AI assistant can query the folder directly.
- REST API — turn a saved query over your event logs into an endpoint for an internal application.
order.total, and totals then add up to the cent.
Every JSONL setting, and when to change it
These are the settings that belong to the JSONL Folder card itself. Everything a folder connector shares with the other formats — root folders, recursion and max depth, exclude patterns, symlinks, the filename parse pattern, folder tokens, the scan interval, hashing, the delete policy, event retention, the scan limits and the four ingest caps — is documented once in the File Set connector guide rather than repeated here.
Table name
Required on this card. A JSONL folder is a single-table connector — every record in every matching file unions into one table — so this is the name you will type in SQL: events, webhooks, whatever the folder actually holds. Letters, digits and underscores, and it may not start with a digit.
Choose it deliberately, because it is the name every saved query, every Nova question and every spreadsheet refresh will use from then on. The one case where it stops being the whole story is one table per layout, described below, where each group of files gets a table named after the files themselves.
Flatten depth
Default: 2. Valid range: 1 to 8. Leaving the box empty rides the agent’s own default rather than pinning a number of your own. What each depth does to your columns is set out earlier in this guide; what follows is the practical side of choosing one.
Zero is refused by name. Flattening cannot be turned off, so if what you want is the agent default, clear the box — do not type 0. Nine or higher is refused too, and refused when you save rather than later at sync time, so you find out while you are still looking at the form instead of in a sync report an hour afterwards. Anything nested deeper than the depth you choose is not lost: it lands as JSON text in a single column.
When to raise it. When the records have genuinely nested structure you need to filter or group on. If the value you want is user.address.city, that is three objects deep, and at depth 2 you will be extracting it out of a JSON column in every single query — set 3 and user_address_city becomes a real column instead.
When to lower it to 1. Wide event payloads where you only care about the top-level fields. Depth 2 against a payload with four hundred keys gives you four hundred columns to scroll past in a spreadsheet; depth 1 gives you the handful you actually read plus one JSON column holding the rest, which is very much easier to work with.
Table layout
Default: one pinned layout, which expects every file in scope to flatten to the same shape. The alternative is one table per layout, which groups the files by their flattened shape and gives each group its own table, up to 25 distinct layouts.
The case for switching is the one described under When the folder holds several event shapes above: a single directory that one consumer writes several kinds of event into. In that mode the cross-file conflicts covered earlier stop being refusals and become separate tables instead, which is usually what you wanted in the first place.
Changing this setting re-samples the folder and rebuilds, by design. Table names already in use are carried across the rebuild, so existing saved queries keep working.
Include patterns
Seeded to **/*.jsonl on this card, and this is the single most likely reason for staring at a connector that found nothing. Newline-delimited JSON has no agreed file extension. Depending on what wrote the files you will find .jsonl, .ndjson, .log or plain .json — and the seeded pattern catches only the first of those. If your files end in anything else, add a line for it before you save. One glob per line, and the lines are additive.
**/*.jsonl **/*.ndjson **/*.json logs/**/*.log
The extension is not what makes a file valid, though, so widen the pattern to match how your tool names things rather than in the hope that a name will change what is inside. The requirement is the format described above — one complete JSON object per line, no commas between records and no wrapping brackets. A .jsonl file holding a pretty-printed array is still refused; a .log file holding one object per line reads perfectly well.
Manifest overrides (JSON)
Behind Show advanced options. This is raw JSON applied at the moment the shape is pinned, so an override becomes part of the table definition rather than something re-applied on every query. Three keys are accepted, all optional, and columns are addressed by their original dot path rather than by the flattened column name.
{
"renames": { "user.id": "nested_user_id" },
"retypes": { "order.total": "DECIMAL(18,4)" },
"exclude": [ "order.site.bay" ]
}
For a JSONL folder this is the practical fix for two situations described earlier in this guide. renames resolves a flattened dot path colliding with a real column: rename one of the two paths and the refusal goes away without having to lower the depth for the whole table. exclude drops a noisy nested branch you are never going to query, which is often a better answer than reducing the depth and losing the fields you do want alongside it. retypes is what the note on money above refers to — force a flattened leaf to an exact decimal and the value is parsed straight from the JSON text at that type.
Emptying the box clears your overrides and returns the table to the shape that was sampled.
Setting it up
You need a Query Streams account and the Network Agent installed on a machine that can see the folder — a log server, a workstation with the share mapped, or your own laptop. JSONL Folder connectors require Network Agent 2.6 or newer.
- Install the agent from the download page if it is not already running.
- In the portal, add a data connector and choose JSONL Folder.
- Enter the folder path as the agent’s machine sees it, with a file pattern such as
events_*.jsonlif the folder holds more than you want in one table. - Leave Flatten depth at 2 for a first look, and check the include pattern matches your file extension. You will know within one query whether you want the depth raised.
- Save. The agent samples the folder, pins the flattening policy and reports the columns it produced — read that list before you write any SQL, because it tells you exactly what your JSON became.
- Open the query builder, pick the connector and query the table, or ask Nova AI to write the SQL for you.
Read-only is enforced rather than promised: a query must be a single statement, and only SELECT, WITH, PRAGMA, DESCRIBE and EXPLAIN are accepted. Your log files are never written, moved or renamed, and the parsed data is cached encrypted on the agent’s own machine.
Frequently Asked Questions
How do I get JSON data into Microsoft Excel? +
What does flattening actually do to a nested object? +
{"order":{"id":1}} becomes a column called order_id; {"order":{"site":{"region":"eu-west"}}} becomes order_site_region. The original dot path is kept alongside the name, and that path is what you address when renaming, retyping or excluding a column.
What is Flatten depth and what should I set it to? +
Can it read a .json file containing one big array? +
How are JSON arrays handled? +
JSON column holding the array exactly as written, which is the only honest answer — an array has no fixed length, so there is no correct number of columns to spread it across. Nothing is lost: you read into it with the engine’s JSON functions, for example json_array_length(tags) for a count or json_extract_string(tags, '$[0]') for the first element.
Why was my folder refused for “colliding column names”? +
user_id alongside a nested user.id, both of which want to be called user_id. Pinning one would silently lose the other, so the connector refuses and names both paths. Lower Flatten depth so the nested one stays inside a JSON column, or rename one path in the connector’s manifest overrides.
What if a field is a plain value in some records and an object in others? +
actor cannot be both a column and the parent of actor_id. It usually means the folder spans a schema change. Either narrow the file pattern so one connector covers each era, or switch to one table per layout and let the two shapes have a table each — in that mode the conflict dissolves, because different shapes are simply different tables.
My log fields have dots in their names, like http.status. Will that work? +
http.status cannot be distinguished from a status field inside an http object, and reading it would return empty values rather than an error. Rather than risk that, the connector refuses and names the field. Either flatten the naming in whatever writes the logs, or exclude those files with the file pattern.
A new field appeared in today’s log file. What happens? +
Do amounts in JSON stay exact? +
Are my JSON files uploaded or modified? +
SELECT, WITH, PRAGMA, DESCRIBE or EXPLAIN statement — anything that could write is rejected before it runs.
Get Started
Query your JSON log folder with SQL
Point the agent at a folder of newline-delimited JSON and get a flat SQL table with nested objects opened into columns — queryable from Microsoft Excel, Google Sheets, Airtable or your AI assistant. Your files never leave your network.
Related guides: How to query a folder of files with SQL | Combine multiple CSV files into one table | Download the Query Streams Agent | All File Set Connector guides
Category: File Set Connectors
Tags: json to excel, query json files with sql, jsonl, newline delimited json, json log files, flatten json, jsonl folder connector, nested json to columns
Meta Description: Turn a folder of JSONL files into SQL tables. Nested objects flatten into columns, no uploads.
English 
Spanish 
German 
French 
Portuguese 
Japanese 
Korean 
Chinese 
Italian 
Dutch 
Indonesian 
Polish