Opening and Exploring JSON Files as Tables

You just downloaded an API response. Maybe it's a list of users from Stripe, events from Mixpanel, or records from your company's internal API. The file is JSON - an array of objects, some with nested fields, some with arrays inside arrays. You open it in a text editor and see 4,000 lines of curly braces.

What you actually want is a table. Rows and columns. Something you can scan visually, sort, filter, and make sense of without parsing nested structures in your head. That's what this guide covers.

Loading JSON and JSONL files

Open ExploreMyData and drag your JSON file onto the page (or click to browse). The tool handles two formats:

• JSON: A single array of objects, like [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
• JSONL (JSON Lines): One JSON object per line, no wrapping array. Common in log files and streaming exports.

DuckDB reads both formats natively. Each top-level key in the objects becomes a column, and each object becomes a row. If your file has 500 objects with keys "id", "name", "email", and "address", you get a table with 500 rows and 4 columns.

The nesting problem

The table loads, but some columns look wrong. Instead of plain values, you see things like {"street": "123 Main", "city": "Portland", "state": "OR"} in the address column. That's because JSON objects can nest, and DuckDB preserves nested structures as STRUCT or JSON types.

This is technically correct but not useful when you want to filter by city or sort by state. You need to flatten the nested fields into their own columns.

Flattening nested objects with JSON Extract

Use the JSON Extract operation in dictionary mode. Select the nested column (e.g., "address") and choose the keys you want to extract. Each key becomes a new column.

Type each key you want into the "Keys to be extracted" list, one row per key, and set each one's type to text or number. Extracting "street", "city" and "state" from the address column gives you three new columns named for the keys alone: street, city and state. There's no address_ prefix. If you're pulling a "name" key out of two different nested columns, that collides, so extract one, rename it with Rename Columns, then extract the other.

There's a Keep source column in the grid checkbox above the key list. Leave it off and the original nested column is dropped once its keys are extracted, which is usually what you want. Turn it on while you're still working out which keys exist, so you can read the raw JSON alongside the extracted values.

Dictionary mode also has an Extract as switch with a Rows option. Instead of one column per key, that gives you two columns, json_key and json_value, and one row per key per original row. Three keys on 500 rows becomes 1,500 rows. It's the right shape when the keys vary from record to record and you want to count which ones actually appear.

Under the hood, each key becomes one DuckDB ->> extraction, the operator that pulls a JSON value out as text:

SELECT id, name, email, address->>'street' AS street, address->>'city' AS city, address->>'state' AS state FROM pipeline_output

Setting a key's type to number wraps it in a TRY_CAST(... AS DOUBLE), so a key that isn't a clean number lands as NULL rather than failing the query. You don't need to write any of this, but it's worth recognising: ->> always returns text, which is why the type dropdown exists at all.

Expanding arrays

Some JSON fields contain arrays. A user might have a "tags" field like ["premium", "enterprise", "early-adopter"], or an "orders" field that's a list of order objects.

Switch JSON Extract to Array / List mode for these columns. The Extract as switch then decides the shape of the result, and the two options are genuinely different, not cosmetic.

Columns is the default. It takes the array positionally and writes each index into its own column, named for the source column plus the index: tags_0, tags_1, and so on. It defaults to five columns, and you set the count yourself. The row count doesn't change, which is the appeal: rows stay one-per-record. The limit is 20 columns, and asking for more raises an error telling you to use rows instead. Choose a count that covers the longest array you care about, because anything past it is silently dropped, and arrays shorter than the count leave NULLs in the trailing columns.

Rows unnests instead: one row per array element, with all the other columns duplicated. A user with three tags becomes three rows. This is the JSON equivalent of a SQL UNNEST(), and it's the right choice when the array is a real one-to-many relationship rather than a fixed-length tuple. It's what you want for counting how many users carry each tag, or for looking at order-level detail.

Be careful with large arrays in rows mode. If each row has 50 elements and you have 1,000 rows, unnesting gives you 50,000. Usually fine, occasionally a surprise.

The Keep source column in the grid checkbox applies here too. In rows mode it's often worth leaving on for the first pass, so you can see the original array next to the exploded values and confirm nothing was lost.

Spotting which columns still need work

There's no structure overview screen; you read the grid. The header badges are the shortcut, and there are only three of them: T for text, # for number, D for date. Nested types don't get their own badge. A STRUCT column and an array column both show T, because anything DuckDB reports that isn't a recognised number or date falls through to text.

So the badge won't tell you a column is nested. The cell contents will. Scan the first few rows and any value that starts with a brace or a square bracket is a column that needs JSON Extract. Braces mean dictionary mode; brackets mean array mode.

Clicking one of those headers opens the categorical explorer, and it does something quietly useful: it lists the distinct serialised values with counts. On a nested column that's a fast way to see how much the structure varies. Two hundred rows sharing one repeated shape is a very different problem from two hundred distinct shapes. The italic (null) row in that same list gives you the count of records where the key was missing from the source JSON entirely.

Trimming to the columns you need

API responses tend to include everything. A Stripe customer object has dozens of fields, but you probably only care about five or six. After loading and flattening, use Select Columns to keep only what you need.

Pick the columns you want, in the order you want them. Everything else is dropped from the view. This makes the table manageable and focused on the data you actually need to analyze.

The pipeline preserves the full data - Select Columns just filters the output. If you realize later you need another field, delete the Select Columns step and the original columns reappear.

Common JSON exploration workflows

Here are a few patterns that come up regularly:

• API response audit: Load the JSON, scan the cells for braces and brackets, extract the nested fields, then Select Columns to focus on what matters. Good for finding out what an API actually returns, as opposed to what the docs claim.
• Log file analysis: Load a JSONL log file, filter by timestamp range or log level, group by error type. Each log entry becomes a row.
• Config comparison: Load two JSON config files as tables, join on the key name, compare values side by side.
• Nested data flattening: Extract all nested objects, drop the original nested columns, export as CSV. Useful when downstream tools can't handle JSON nesting.

JSON or CSV: when each makes sense

JSON is better at representing complex, hierarchical data. CSV is better for flat, tabular data. When you load JSON into ExploreMyData, you're essentially converting from one to the other - flattening a hierarchy into a table.

This works well when the data is mostly tabular with a few nested fields. It works less well when the structure is deeply nested or highly variable (different objects have completely different keys). For those cases, you might need to extract specific paths rather than trying to flatten everything.

The point is to get from "I have a JSON file" to "I can see and explore this data" as fast as possible. Load, flatten what needs flattening, trim to the columns you need, and start asking questions.

Open a JSON file now →