How to Query Nested JSON API Responses with SQL Directly in the Browser
Your Stripe API returns line_items nested 4 levels deep. Segment's tracking plan exports event_properties as objects inside arrays. Pasting this into Excel gives you a useless column of [object Object]. Writing a Python script with json_normalize() takes 20 minutes for a single ad-hoc query. DuckDB's read_json_auto() infers schema from the first 100 rows by default—if row 101 introduces a new nested key (common in Stripe webhooks where event types vary), the column silently becomes NULL. Use sample_size=-1 for full-file inference. This workflow loads raw JSON straight into DuckDB-Wasm, auto-detects the schema, and lets you run SQL with UNNEST() to flatten arrays and CTEs for multi-step logic—entirely inside your browser tab.
Why This Matters
Engineers debugging webhooks and analysts pulling Amplitude data hit the same wall: nested JSON that spreadsheets cannot parse. The standard workaround—spinning up a local Postgres instance, running COPY, and writing complex jsonb queries—wastes 30 minutes of setup for a 30-second question. DuckDB's UNNEST() function explodes arrays into rows while preserving a row_id for re-assembly—equivalent to pandas explode() but 8–12x faster on 100K+ element arrays due to vectorized execution. By executing analytical SQL natively in the browser via Wasm, you eliminate infrastructure overhead completely.
Why Excel & Python Fail Here
PostgreSQL's jsonb operators (->>, #>, jsonb_array_elements) require loading data into a table first via COPY. For a one-off query, this means: install Postgres, createdb, CREATE TABLE, COPY FROM, write query, drop table. Total: 30 minutes for a 30-second question. Python's json_normalize() works but requires importing the file, writing code, handling edge cases, and installing pandas. Neither is viable for a quick 'just answer this question' workflow.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the JSON ↔ CSV Converter to execute this entirely in your browser.
- 1
Drag and drop your JSON file
Upload any JSON file (single object, array of objects, or newline-delimited JSON). The tool auto-detects the structure and infers schema using full-file scanning (sample_size=-1) to avoid silent NULL columns.
- 2
Write SQL with UNNEST for nested arrays
The SQL editor provides autocomplete for detected columns. Use UNNEST(line_items) to explode arrays into rows. Use CTEs (WITH clause) for multi-step logic. All standard DuckDB SQL functions are available.
- 3
Export results as CSV or Parquet
Download query results as CSV for spreadsheet use, or Parquet for downstream Spark/Athena pipelines. Large results (>1M rows) auto-stream to avoid browser memory limits.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
Column shows NULL for rows after the first 100 | DuckDB's read_json_auto() default sample_size=100 misses keys introduced later | This tool uses sample_size=-1 (full-file inference) by default. No NULL surprises. |
UNNEST returns empty result set | Array column is stored as VARCHAR (stringified JSON) instead of native LIST type | Cast first: SELECT * FROM UNNEST(CAST(json_column AS JSON[])). The tool auto-detects and handles this. |
Frequently Asked Questions
What's the maximum JSON file size?
Can I JOIN two JSON files?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly