Querying CSV and JSON

Querying CSV and JSON Files Directly

read_csv and read_json (or just a file name) sniff the dialect, JSON layout and column types. This query joins the 37 MB event log (Order Events as JSON Lines) with the CSV orders to measure delivery time per channel:

472-csv-json.sql: JSON Lines events joined with a CSV fileSQL

.timer on
WITH ev AS (
  SELECT order_id, min(ts) FILTER (type = 'order_placed') AS placed,
         min(ts) FILTER (type = 'order_delivered') AS delivered
  FROM read_json('data/order_events.jsonl') GROUP BY order_id)
SELECT o.channel, count(ev.delivered) AS delivered,
       round(avg(epoch(ev.delivered - ev.placed)) / 86400, 2) AS avg_days
FROM ev JOIN read_csv('csv-out/orders.csv') AS o USING (order_id)
GROUP BY ALL ORDER BY avg_days;

Output

┌─────────┬───────────┬──────────┐
│ channel │ delivered │ avg_days │
│ varchar │   int64   │  double  │
├─────────┼───────────┼──────────┤
│ android │     32501 │      6.2 │
│ ios     │     41718 │     6.21 │
│ web     │     18790 │     6.24 │
└─────────┴───────────┴──────────┘
Run Time (s): real 0.946 user 1.114682 sys 0.394686

Parsing 390,737 JSON objects and 100,000 CSV rows took under a second, with no schema written down. Text formats cannot skip anything, though: every query parses every byte. If you query a text file more than a few times, convert it to Parquet 129 (Exporting to Parquet) or load it into a table.

MENU