# DuckDB - Query JSON and CSV Files With SQL, No Database Required > Run real SQL — joins, window functions, GROUP BY — directly against JSON and CSV files, no import step, no server, no schema to define. Dockerized, with a duckdb shell function that works from any folder. The tool I reach for once awk or jq stop being enough. ---  **TL;DR** [DuckDB](https://duckdb.org/) is an embedded analytical database that queries CSV and JSON files directly with SQL — `SELECT * FROM 'file.csv'` just works, no import, no server, no schema to define first. This article sets it up as a Docker image with a `duckdb` shell function loaded by your zsh or bash, the same pattern as [Markitdown](/blog/markitdown) and [Docling](/blog/docling): one command, works from any folder. It's the tool that picks up exactly where `awk` and `jq` stop being enough — the moment a question needs a join, a window function, or a GROUP BY across two different file formats at once. I wrote [Pandas — merge two or more files](/blog/python-pandas-merge) 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 **orders.csv** ```csv order_id,region,amount 1001,EMEA,120.50 1002,APAC,89.00 1003,EMEA,45.20 1004,AMER,300.00 1005,APAC,60.75 ``` **users.json** ```json [ { "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 } ] ``` **regions.json** ```json [ { "region": "EMEA", "manager": "Alice Martin" }, { "region": "APAC", "manager": "Chloé Bernard" }, { "region": "AMER", "manager": "Bruno Dupont" } ] ``` ```bash $ 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: ```sql 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`](/blog/linux-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: **Project: /tmp/duckdb-json** > **Note:** > > Now, run 'docker build -t duckdb-cli .' to create the Docker image, then > install the duckdb function as explained below. **Dockerfile** ```docker # syntax=docker/dockerfile:1.7-labs FROM python:3.13-slim AS final ENV PYTHONDONTWRITEBYTECODE=1 \ PYTHONUNBUFFERED=1 \ PIP_DISABLE_PIP_VERSION_CHECK=1 ARG UID=1000 ARG GID=1000 ARG DUCKDB_VERSION=2.0.0 # Download the official CLI release directly — there is no apt/pip package # for the interactive shell itself, only for the Python bindings. RUN apt-get update && \ apt-get install -y --no-install-recommends curl ca-certificates && \ curl -fsSL \ "https://github.com/duckdb/duckdb/releases/download/v${DUCKDB_VERSION}/duckdb-cli-linux-amd64.tar.gz" \ | tar -xz -C /usr/local/bin duckdb && \ chmod +x /usr/local/bin/duckdb && \ rm -rf /var/lib/apt/lists/* RUN groupadd -g "${GID}" developer && \ useradd -m -u "${UID}" -g "${GID}" -s /usr/sbin/nologin developer WORKDIR /workspace USER developer ENTRYPOINT ["duckdb"] ``` **orders.csv** ```csv order_id,region,amount 1001,EMEA,120.50 1002,APAC,89.00 1003,EMEA,45.20 1004,AMER,300.00 1005,APAC,60.75 ``` **users.json** ```json [ { "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 } ] ``` **regions.json** ```json [ { "region": "EMEA", "manager": "Alice Martin" }, { "region": "APAC", "manager": "Chloé Bernard" }, { "region": "AMER", "manager": "Bruno Dupont" } ] ``` 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. > **Version pinned on purpose:** > > `DUCKDB_VERSION` is a build arg, pinned to `2.0.0` — the version current as of writing. Check the [releases page](https://github.com/duckdb/duckdb/releases) for a newer one and bump the arg; DuckDB ships new versions often enough that pinning explicitly, rather than tracking `latest`, keeps the build reproducible. ### 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: ```bash 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](/blog/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. **1. Create the file** in `~/.zsh/fns/`: **Project: \~/.zsh/fns** **duckdb.zsh** ```bash # DuckDB CLI through Docker: query the CSV / JSON / Parquet files of the # current folder, from anywhere. Works in zsh and bash: save it as # ~/.zsh/fns/duckdb.zsh or ~/.bash/fns/duckdb.sh (see the article). # # duckdb -> interactive SQL shell # duckdb -c "SELECT ..." -> one query, then exit # cat q.sql | duckdb -> piped SQL works too (no TTY needed) # duckdb --write -c "COPY ..." -> allow writing files in the current folder # # The current folder is mounted read-only by default: a query can never alter # your files by accident. --write (first argument) lifts that, for COPY ... TO # and ATTACH 'file.duckdb'. duckdb() { local image="duckdb-cli" # Permanent home of the Dockerfile, so the image can always be rebuilt. local build_dir="${HOME}/tools/duckdb" # Shown as is in the messages, so the command can be copied for any user. # shellcheck disable=SC2016 local build_dir_shown='${HOME}/tools/duckdb' if ! command -v docker >/dev/null 2>&1; then printf '\e[31mError: docker is not installed or not in your PATH.\e[0m\n' >&2 return 1 fi if ! docker image inspect "${image}" >/dev/null 2>&1; then printf '\e[31mError: the Docker image "%s" does not exist on this machine.\e[0m\n\n' "${image}" >&2 printf 'How to continue:\n' >&2 if [[ -f "${build_dir}/Dockerfile" ]]; then printf ' 1. docker build -t %s "%s"\n' "${image}" "${build_dir_shown}" >&2 printf ' 2. Run your duckdb command again.\n' >&2 else printf ' 1. Get the Dockerfile from https://www.avonture.be/blog/duckdb-json-csv\n' >&2 printf ' and save it as %s/Dockerfile\n' "${build_dir_shown}" >&2 printf ' 2. docker build -t %s "%s"\n' "${image}" "${build_dir_shown}" >&2 printf ' 3. Run your duckdb command again.\n' >&2 fi return 1 fi # Read-only mount unless --write is the first argument. local mount_mode=":ro" if [[ "${1:-}" == "--write" ]]; then mount_mode="" shift fi # -t only when both ends are a terminal, so piping SQL in or out still works. local tty_flags="-i" [[ -t 0 && -t 1 ]] && tty_flags="-it" if [[ -n "${mount_mode}" && $# -eq 0 && -t 0 ]]; then printf '\e[33mRead-only: your files cannot be modified. Start with "duckdb --write" to save files.\e[0m\n' >&2 fi # --read-only locks the container's own filesystem; the mount mode above # is what protects (or not) the files of the current folder. docker run --rm "${tty_flags}" \ -v "${PWD}:/workspace${mount_mode}" \ -w /workspace \ --read-only \ --tmpfs /tmp:size=256m,noexec,nosuid \ --cap-drop ALL \ --security-opt no-new-privileges:true \ -u "$(id -u):$(id -g)" \ "${image}" "$@" # zsh reserves $status, hence rc. local rc=$? if [[ ${rc} -ne 0 && -n "${mount_mode}" ]]; then printf '\e[33mTip: the current folder is read-only. If this failed while writing a file, run it again as: duckdb --write ...\e[0m\n' >&2 fi return "${rc}" } ``` **2. Load the folder from `~/.zshrc`.** Already done if you followed one of my ["Ollama daily use"](/series/ollama-daily-use) articles. Otherwise, add at the end of `~/.zshrc`: ```zsh title="~/.zshrc" for fn_file in ~/.zsh/fns/*.zsh; do source "$fn_file" done ``` **3. Open a new terminal** (or run `exec zsh`). **1. Create the file** in `~/.bash/fns/`: **Project: \~/.bash/fns** **duckdb.sh** ```bash # DuckDB CLI through Docker: query the CSV / JSON / Parquet files of the # current folder, from anywhere. Works in zsh and bash: save it as # ~/.zsh/fns/duckdb.zsh or ~/.bash/fns/duckdb.sh (see the article). # # duckdb -> interactive SQL shell # duckdb -c "SELECT ..." -> one query, then exit # cat q.sql | duckdb -> piped SQL works too (no TTY needed) # duckdb --write -c "COPY ..." -> allow writing files in the current folder # # The current folder is mounted read-only by default: a query can never alter # your files by accident. --write (first argument) lifts that, for COPY ... TO # and ATTACH 'file.duckdb'. duckdb() { local image="duckdb-cli" # Permanent home of the Dockerfile, so the image can always be rebuilt. local build_dir="${HOME}/tools/duckdb" # Shown as is in the messages, so the command can be copied for any user. # shellcheck disable=SC2016 local build_dir_shown='${HOME}/tools/duckdb' if ! command -v docker >/dev/null 2>&1; then printf '\e[31mError: docker is not installed or not in your PATH.\e[0m\n' >&2 return 1 fi if ! docker image inspect "${image}" >/dev/null 2>&1; then printf '\e[31mError: the Docker image "%s" does not exist on this machine.\e[0m\n\n' "${image}" >&2 printf 'How to continue:\n' >&2 if [[ -f "${build_dir}/Dockerfile" ]]; then printf ' 1. docker build -t %s "%s"\n' "${image}" "${build_dir_shown}" >&2 printf ' 2. Run your duckdb command again.\n' >&2 else printf ' 1. Get the Dockerfile from https://www.avonture.be/blog/duckdb-json-csv\n' >&2 printf ' and save it as %s/Dockerfile\n' "${build_dir_shown}" >&2 printf ' 2. docker build -t %s "%s"\n' "${image}" "${build_dir_shown}" >&2 printf ' 3. Run your duckdb command again.\n' >&2 fi return 1 fi # Read-only mount unless --write is the first argument. local mount_mode=":ro" if [[ "${1:-}" == "--write" ]]; then mount_mode="" shift fi # -t only when both ends are a terminal, so piping SQL in or out still works. local tty_flags="-i" [[ -t 0 && -t 1 ]] && tty_flags="-it" if [[ -n "${mount_mode}" && $# -eq 0 && -t 0 ]]; then printf '\e[33mRead-only: your files cannot be modified. Start with "duckdb --write" to save files.\e[0m\n' >&2 fi # --read-only locks the container's own filesystem; the mount mode above # is what protects (or not) the files of the current folder. docker run --rm "${tty_flags}" \ -v "${PWD}:/workspace${mount_mode}" \ -w /workspace \ --read-only \ --tmpfs /tmp:size=256m,noexec,nosuid \ --cap-drop ALL \ --security-opt no-new-privileges:true \ -u "$(id -u):$(id -g)" \ "${image}" "$@" # zsh reserves $status, hence rc. local rc=$? if [[ ${rc} -ne 0 && -n "${mount_mode}" ]]; then printf '\e[33mTip: the current folder is read-only. If this failed while writing a file, run it again as: duckdb --write ...\e[0m\n' >&2 fi return "${rc}" } ``` **2. Load the folder from `~/.bashrc`.** Add at the end of `~/.bashrc` (the `-r` test keeps an empty folder from printing an error): ```bash title="~/.bashrc" for fn_file in ~/.bash/fns/*.sh; do [[ -r "$fn_file" ]] && source "$fn_file" done ``` **3. Open a new terminal** (or run `exec bash`). 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: ```bash 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`: ```text 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. > **It shadows a native duckdb:** > > The function is called `duckdb`, so it takes precedence over a `duckdb` binary installed locally. That's the point here (no local install), but if you install DuckDB natively one day, remove the function or rename it. ## 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](#what-makes-duckdb-different): ```bash $ 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. > **To leave the console, type .quit:** > > Console commands start with a **dot**: type `.quit` (or `.exit`, or press `Ctrl`+`D`) to get back to your shell. A bare `exit` or `quit` does **not** work: DuckDB reads it as the beginning of a SQL statement and silently waits for the rest of it, until a `;` ends it with a syntax error. ### 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: ```bash $ 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 **duckdb quick reference** * **No import step** — `SELECT * FROM 'file.csv'` and `read_json_auto('file.json')` query files directly * **Real SQL** — joins, window functions, `GROUP BY`, exactly like a full database, no server required * **No CLI package** — the Dockerfile downloads the official release binary, version-pinned * **One file in `~/.zsh/fns/` or `~/.bash/fns/`** — `duckdb` works from any folder, and explains how to rebuild when the image is gone * **Interactive or one-shot** — bare `duckdb` for a REPL, `-c "SQL"` for a single query * **Read-only by default** — `duckdb --write` to save files with `COPY ... TO` or `ATTACH` * **Complements `ai-data`** — reach for `awk`/`jq` first, DuckDB once the question needs a join or a window function ## Conclusion Between [`python-pandas-merge`](/blog/python-pandas-merge) for anything that needs real scripting, `jq`/`awk`/[`ai-data`](/blog/ollama-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/`.