How to Analyze JSON Files Locally with SQL

SQL for Files is a good fit for JSON arrays and newline-delimited JSON where each object represents one row. That makes logs, API exports, product catalogs, and event files easier to inspect with SQL.

• JSON array files such as [{...}, {...}].
• NDJSON files where each line is one JSON object.
• Nested arrays or structs that you can inspect and flatten with SQL patterns.

1. Open the editor and add your JSON or NDJSON file.
2. Review the created table in the Database sidebar.
3. Preview the first rows to confirm field names and inferred types.
4. Run focused queries before writing larger transformations.

When your JSON records have consistent top-level fields, you can query them like columns in a regular table.

SELECT event_type, user_id, created_at
FROM events
WHERE event_type = 'purchase'
ORDER BY created_at DESC
LIMIT 100;

Aggregation works well for event counts, API status codes, product categories, and user activity summaries.

SELECT
  event_type,
  COUNT(*) AS events
FROM events
GROUP BY event_type
ORDER BY events DESC;

Nested JSON often needs one extra step: unnest lists into rows or expand structs into columns. DuckDB includes SQL features for these patterns, and the Learn SQL track includes lessons on UNNEST for nested data.

SELECT
  order_id,
  item.name AS item_name,
  item.quantity
FROM orders,
UNNEST(items) AS item;

JSON exports can contain logs, customer IDs, analytics events, or sensitive operational data. SQL for Files keeps JSON processing in the browser, so you can inspect files without sending them to a hosted database or converter.