JSON, YAML, CSV and SQLite files: which recorded command-line tool for which file
For assistants asked to read, filter, edit or aggregate data and config files, this feature separates four recorded tools by the kind of file they fit (jq, yq, DuckDB, sqlite3) and lists the pitfalls headlesson observed in their lesson tests, separately from cautions taken from documentation. Test results are dated operator observations on the stated version with small self-made inputs; descriptions of what a tool can do come from its documentation. None of it measures speed or behaviour on large real data.
The kind of file decides the tool
- JSON only, to filter or extract values: jq (
app:jq). Its lesson passed 5 steps with jq-1.7.1-apple on macOS on 2026-10-11. - YAML (or converting between YAML, JSON and other formats), including editing a config file: yq (mikefarah) (
app:yq), the Go tool by mikefarah. Its lesson passed 7 steps with 4.54.1 on 2026-10-11. yq (mikefarah) (app:yq) uses jq-like expressions and can also read JSON, so for JSON alone either works; jq (app:jq) does not read YAML. - CSV, Parquet or JSON files to query with SQL where they lie: DuckDB (
app:duckdb). Its lesson passed 6 steps with 1.5.6 on 2026-10-11, reading one self-made CSV and writing a Parquet file; a supplementary check in the same test read that Parquet file back. Reading JSON files is documented and was not tested. - Data already in a SQLite database file: SQLite CLI (sqlite3) (
app:sqlite) (sqlite3). Its lesson passed 6 steps with the OS-provided 3.51.0 on 2026-10-11. Choose between DuckDB (app:duckdb) and SQLite CLI (sqlite3) (app:sqlite) by where the data already is (data files versus a SQLite database file); their lessons used the same five region and amount rows, but they are separate lessons, not a controlled comparison, and speed was not measured.
Pitfalls observed in the tests, and cautions from the documentation
- jq (
app:jq): --arg always binds a string, so a number needs --argjson, and after arithmetic a large integer lost precision (12345678901234567890 + 0 printed 12345678901234567000), both seen in the test. From the documentation: jq does not edit the input file. - yq (mikefarah) (
app:yq): in the test, redirecting output to the input file truncated it while exiting 0, a missing key printed null and exited 0 unless -e was given, and converting to JSON dropped comments. From the documentation: two unrelated programs are called yq (mikefarah's Go tool and a Python wrapper around jq), so check yq --version first. - DuckDB (
app:duckdb): in the test, sum() over integers returned HUGEINT, which -json printed as a quoted string ("total":"8"), and column types were inferred from the CSV, so check them with DESCRIBE. From the documentation: the CLI reads ~/.duckdbrc unless -init /dev/null is passed. headlesson's own caution, not tested: check where COPY ... TO writes before running it; the test only created a new file, and overwriting an existing one was not tried. - SQLite CLI (sqlite3) (
app:sqlite): in the test, a mistyped database path created a new empty file and exited 0, -json printed nothing (not []) for zero rows, and a write under -readonly exited 8.
A safe sequence
With jq (app:jq), yq (mikefarah) (app:yq), DuckDB (app:duckdb) or SQLite CLI (sqlite3) (app:sqlite): confirm which program and version is on PATH; inspect before changing anything (read the key with yq (mikefarah) (app:yq), DESCRIBE the file with DuckDB (app:duckdb), open the database with -readonly and look at .schema with SQLite CLI (sqlite3) (app:sqlite)); write results to a new file and show the difference before replacing the user's file; treat empty output, null and the exit status as signals rather than trusting exit 0 alone. Each lesson (headlesson_get with sections ["lesson","gotchas"]) gives the exact commands and versions.
Records cited
Each line gives the record, its public page if there is one, and where its name comes from.
- jq —
app:jq(no public page yet; available over MCP); source: https://jqlang.org/ (checked 2026-10-11, primary, official) - yq (mikefarah) —
app:yq(no public page yet; available over MCP); source: https://github.com/mikefarah/yq/blob/master/README.md (checked 2026-10-11, primary, official) - DuckDB —
app:duckdb; source: https://duckdb.org/ (checked 2026-10-11, primary, official) - SQLite CLI (sqlite3) —
app:sqlite(no public page yet; available over MCP); source: https://sqlite.org/cli.html (checked 2026-10-11, primary, official)
Article source
headlesson editorial judgement (checked 2026-10-13, inferred, editorial).
Retrieve over MCP
headlesson_get id="feature:data-files" on https://mcp.headlesson.com/mcp