DuckDB - Query JSON and CSV Files With SQL, No Database Required

I wrote Pandas — merge two or more files a while back for exactly this kind of job, and it works, but it means writing a script: import pandas, read the files, merge, maybe export again. Most of the time what I actually want is one query, run once, answered once. awk handles the simple version of that — group-by-and-count, filtering — but the moment I need an actual JOIN or a window function, I'm back to writing Python for a question that's genuinely one line of SQL.

Demo​

order_id,region,amount?
1001,EMEA,120.50
1002,APAC,89.00
1003,EMEA,45.20
1004,AMER,300.00
1005,APAC,60.75

[
  { "name": "Alice Martin", "birth_date": "1990-04-12", "active": true },
  { "name": "Bruno Dupont", "birth_date": "1985-11-02", "active": false },
  { "name": "Chloé Bernard", "birth_date": "1998-07-23", "active": true }
]

[
  { "region": "EMEA", "manager": "Alice Martin" },
  { "region": "APAC", "manager": "Chloé Bernard" },
  { "region": "AMER", "manager": "Bruno Dupont" }
]

$ duckdb -c "SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM 'orders.csv' GROUP BY region ORDER BY total DESC"

┌─────────┬────────┬────────┐
│ region  │ orders │ total  │
│ varchar │ int64  │ double │
├─────────┼────────┼────────┤
│ AMER    │      1 │  300.0 │
│ EMEA    │      2 │  165.7 │
│ APAC    │      2 │ 149.75 │
└─────────┴────────┴────────┘

$ duckdb -c "SELECT name FROM read_json_auto('users.json') WHERE active = true"

┌───────────────┐
│     name      │
│    varchar    │
├───────────────┤
│ Alice Martin  │
│ Chloé Bernard │
└───────────────┘

$ duckdb -c "SELECT order_id, region, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region FROM 'orders.csv' ORDER BY region, rank_in_region"

┌──────────┬─────────┬────────┬────────────────┐
│ order_id │ region  │ amount │ rank_in_region │
│  int64   │ varchar │ double │     int64      │
├──────────┼─────────┼────────┼────────────────┤
│     1004 │ AMER    │  300.0 │              1 │
│     1002 │ APAC    │   89.0 │              1 │
│     1005 │ APAC    │  60.75 │              2 │
│     1001 │ EMEA    │  120.5 │              1 │
│     1003 │ EMEA    │   45.2 │              2 │
└──────────┴─────────┴────────┴────────────────┘

$ duckdb -c "SELECT r.manager, o.region, COUNT(*) AS orders, SUM(o.amount) AS total FROM 'orders.csv' o JOIN 'regions.json' r USING (region) GROUP BY ALL ORDER BY total DESC"

┌───────────────┬─────────┬────────┬────────┐
│    manager    │ region  │ orders │ total  │
│    varchar    │ varchar │ int64  │ double │
├───────────────┼─────────┼────────┼────────┤
│ Bruno Dupont  │ AMER    │      1 │  300.0 │
│ Alice Martin  │ EMEA    │      2 │  165.7 │
│ Chloé Bernard │ APAC    │      2 │ 149.75 │
└───────────────┴─────────┴────────┴────────┘
▋

Four genuinely different query shapes, none of which required writing a script: a GROUP BY/SUM on the CSV, a WHERE filter through read_json_auto on the JSON, a RANK() OVER (PARTITION BY ...) window function, and a JOIN between the CSV and a JSON file, in the same query — the kind of question awk simply doesn't have a clean answer for.

What Makes DuckDB Different​

DuckDB is a real SQL engine — the syntax, the query planner, the works — except it runs in-process, needs no server, and reads CSV/JSON/Parquet files directly as if they were tables:

SELECT * FROM 'orders.csv';
SELECT * FROM read_json_auto('users.json');

No CREATE TABLE, no import step, no schema to define — it infers column types by sampling the file, the same idea as jq's automatic parsing but with full SQL on top instead of a filter language.

Installation​

Try It in Two Minutes​

Click the button below to get a script that creates /tmp/duckdb-json with the Dockerfile and the three demo files:

There's no apt or pip package for the DuckDB CLI itself (only for the Python bindings), so the Dockerfile downloads the official release binary.

Make duckdb Available Everywhere​

/tmp/duckdb-json was only for the demo: it vanishes at the next reboot. What has to stay on your PC is the Dockerfile, so the image can be rebuilt any time (new machine, docker image prune, a new DuckDB version). Give it a permanent home in ~/tools/duckdb, and build the image from there:

mkdir -p ~/tools/duckdb
cp /tmp/duckdb-json/Dockerfile ~/tools/duckdb/
docker build -t duckdb-cli ~/tools/duckdb

What turns that image into a command is one function, and it gets its own file in a folder your shell loads at startup, the same organisation as in my modular ZSH workflow: your rc file stays short, and removing the tool is deleting one file.

The code is the same for zsh and bash; only the folder and the loader differ.

for fn_file in ~/.zsh/fns/*.zsh; do
  source "$fn_file"
done

for fn_file in ~/.bash/fns/*.sh; do
  [[ -r "$fn_file" ]] && source "$fn_file"
done

Check that the shell knows the function: type duckdb must answer duckdb is a shell function. Then go to the demo folder and replay the first query of this article:

cd /tmp/duckdb-json
duckdb -c "SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM 'orders.csv' GROUP BY region ORDER BY total DESC"

From now on, it works the same in any folder holding a CSV or JSON file. The function mounts the current folder as /workspace, so paths in your SQL are relative to where you are. That folder is mounted read-only unless the first argument is --write; --read-only on its side locks the container's own filesystem, so nothing can be written inside the image either; a file outside the current folder isn't visible, cd to it first. With no argument, duckdb opens the interactive SQL shell; with -c "SQL", it runs one query and exits; piped SQL (cat report.sql | duckdb) works too.

If the image is missing, the function doesn't fail with a cryptic Docker error, it tells you how to rebuild it from ~/tools/duckdb:

Error: the Docker image "duckdb-cli" does not exist on this machine.

How to continue:
  1. docker build -t duckdb-cli "${HOME}/tools/duckdb"
  2. Run your duckdb command again.

And if ~/tools/duckdb/Dockerfile is gone too, the message points back to this article to get it again.

Working With duckdb​

The Interactive Console​

With the function in place, typing duckdb with no argument opens an interactive SQL console, the most comfortable place to play with the two statements from What Makes DuckDB Different:

$ duckdb
Read-only: your files cannot be modified. Start with "duckdb --write" to save files.
DuckDB v2.0.0 (Cyanoptera)
Enter ".help" for usage hints.
memory D SELECT * FROM 'orders.csv';
┌──────────┬─────────┬────────┐
│ order_id │ region  │ amount │
│  int64   │ varchar │ double │
├──────────┼─────────┼────────┤
│     1001 │ EMEA    │  120.5 │
│     1002 │ APAC    │   89.0 │
│     1003 │ EMEA    │   45.2 │
│     1004 │ AMER    │  300.0 │
│     1005 │ APAC    │  60.75 │
└──────────┴─────────┴────────┘
memory D SELECT * FROM read_json_auto('users.json');
┌───────────────┬────────────┬─────────┐
│     name      │ birth_date │ active  │
│    varchar    │    date    │ boolean │
├───────────────┼────────────┼─────────┤
│ Alice Martin  │ 1990-04-12 │ true    │
│ Bruno Dupont  │ 1985-11-02 │ false   │
│ Chloé Bernard │ 1998-07-23 │ true    │
└───────────────┴────────────┴─────────┘
memory D .quit

Look at the second table: nobody declared anything, yet birth_date came back as a date and active as a boolean, inferred from the JSON values themselves. The memory D prompt means the session runs on a throw-away in-memory database: a SELECT reads your files and changes nothing. .help lists the console commands.

UPDATE and DELETE: Read-Only by Default, --write When You Mean It​

DuckDB runs UPDATE and DELETE on tables, never on a file read directly: UPDATE 'orders.csv' SET … is refused with Can only update base table, whatever the permissions. Changing a file is therefore always the same three steps: load it into a table, change the table, write it back with COPY ... TO.

That last step is a write, and the duckdb function refuses it by default: it mounts the current folder read-only, so no query can alter your files by accident. Put --write first to allow it:

$ duckdb -c "CREATE TABLE o AS FROM 'orders.csv'; UPDATE o SET amount = round(amount * 1.21, 2) WHERE region = 'EMEA'; DELETE FROM o WHERE region = 'APAC'; COPY o TO 'orders_fixed.csv'"

IO Error: Cannot open file "orders_fixed.csv": Read-only file system
Tip: the current folder is read-only. If this failed while writing a file, run it again as: duckdb --write ...

$ duckdb --write -c "CREATE TABLE o AS FROM 'orders.csv'; UPDATE o SET amount = round(amount * 1.21, 2) WHERE region = 'EMEA'; DELETE FROM o WHERE region = 'APAC'; COPY o TO 'orders_fixed.csv'"

$ cat orders_fixed.csv
order_id,region,amount
1001,EMEA,145.81
1003,EMEA,54.69
1004,AMER,300.0

The original orders.csv is untouched; orders_fixed.csv holds the EMEA amounts with 21% VAT and no APAC rows. Write back to 'orders.csv' instead and the file itself is rewritten. COPY ... TO writes CSV, JSON ('out.json') or Parquet ('out.parquet'), picked from the extension. To keep tables between two sessions instead of a throw-away memory database, attach a database file, which is a write too: duckdb --write, then ATTACH 'shop.duckdb' AS shop;.

Key Takeaways​

Conclusion​

Between python-pandas-merge for anything that needs real scripting, jq/awk/ai-data for quick one-liners, and now DuckDB for everything in between that's genuinely a SQL question, I've stopped reaching for "let me write a quick Python script" nearly as often. A .csv and a .json file, queried and joined together with one line of actual SQL, no server, no import — that's a tool that earns a permanent spot in my ~/.zsh/fns/.