DEV Community

VeilAnalytics
VeilAnalytics

Posted on

How to Query Complex JSON and NDJSON Files with SQL (Without Writing Custom Parsers)

#webdev #database #python #javascript

How to Query Complex JSON and NDJSON Files with SQL (Without Writing Custom Parsers)

Nested JSON is the universal language of APIs, event streams, and database dumps.

But when you need to answer a quick analytical question like "What was the average response time for customer_id: 4892 across this 500MB JSON dump?", the options are usually painful:

  1. Write a quick Python script: Write boilerplate dictionary loops, parse dates, handle missing keys, and debug KeyError exceptions.
  2. Import into MongoDB / Postgres: Spin up a local container or instance, configure schemas or JSONB columns, write queries, and tear it down.
  3. Upload to an online JSON viewer / AI tool: Expose internal API payloads, customer IDs, and sensitive tokens to third-party servers.

There is a much cleaner way: query nested JSON directly using SQL in-memory, keeping your data 100% on your machine.

🛠️ Querying JSON Directly with DuckDB SQL

Modern columnar engines like DuckDB have native JSON readers that automatically detect schemas and flatten nested objects on the fly:

-- Read a JSON file directly as a virtual table
SELECT 
  event_type,
  user.id AS user_id,
  user.email AS user_email,
  payload.amount AS transaction_amount,
  timestamp
FROM read_json_auto('api_events.json')
WHERE payload.status = 'completed'
ORDER BY payload.amount DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

You do not need to define tables, specify column data types, or import anything. The engine inspects the file, extracts the structure, and lets you run standard SQL aggregations immediately.

📂 Handling NDJSON / JSON Lines (Massive Event Streams)

For server logs and event tracking, files are usually formatted as Newline Delimited JSON (NDJSON / .jsonl), where each line is a separate JSON object.

NDJSON can scale to gigabytes, making standard Python json.load() crash with Out-Of-Memory (OOM) errors because it tries to parse the entire tree into memory at once.

DuckDB streams NDJSON in vectorized chunks:

-- Aggregate metrics across gigabytes of NDJSON event logs
SELECT 
  strftime('%Y-%m-%d %H:00', timestamp::TIMESTAMP) AS hourly_bucket,
  endpoint,
  COUNT(*) AS request_count,
  AVG(duration_ms) AS avg_latency,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95_latency
FROM read_ndjson_auto('server_events.jsonl')
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;
Enter fullscreen mode Exit fullscreen mode

🔒 The Privacy Imperative: Keeping API Dumps Confidential

JSON exports from production systems frequently contain:

  • PII (emails, IP addresses, names)
  • Authentication tokens or internal session IDs
  • Proprietary customer financial transactions

Uploading these dumps to generic cloud AI services or online JSON formatters is a major security liability.

By running the query engine in-process or in-browser (via WebAssembly), the file is read directly from your local filesystem into sandboxed memory. No network packets containing your data are ever sent across the web.

🚀 Interactive In-Browser Querying: VeilAnalytics

If you prefer a clean interface without writing CLI commands or installing local database engines:

VeilAnalytics provides an in-browser analytics workspace powered by DuckDB-WASM:

  1. Drop your .json or .jsonl file directly into the browser tab.
  2. Ask questions in natural language: "Find the top 10 users by total transaction value in completed orders".
  3. The platform maps your request to an optimized read-only SQL query executed entirely inside your browser sandbox.
  4. Export interactive charts and dashboards as self-contained offline HTML files.

📋 Summary: When to Use SQL on JSON

Scenario Traditional Python In-Memory SQL Engine
Quick aggregations on 1GB+ JSON 50+ lines of loop code + high RAM Single SQL query in < 2 seconds
Nested field extraction record.get('user', {}).get('id') user.id dot notation
Data Privacy Local, but tedious 100% Local & Instant
P95 / Percentiles Calculation Requires numpy / manual sort Native PERCENTILE_CONT

Next time you receive a multi-hundred megabyte JSON export, skip the custom parsing scripts. Query it with SQL locally.

VeilAnalytics — Private in-browser data analytics workspace. Query CSV, JSON, and Parquet with natural language and SQL. Zero data uploads.

Top comments (0)