Short Note: use DuckDB as (pipe) jq replacement

The usage of the jq can be substituted sometimes by DuckDB with a bit cleaner syntax.

For example, to get properties Id and State.Status from a list of active containers in docker one might do this:

➜ docker inspect f42 | jq '.[] | "\(.Id) \(.State.Status)"' -r
f42d97a8670846a737a972329cf44e5b092954ef7e30681f73d70963ad971e43 running

but it might be easier to write a modern SQL JSON:

➜ docker inspect f42 | mjq "select Id, State->>'Status' from JQ"
│                                Id                                │ (State ->> 'Status') │
│                             varchar                              │       varchar        │
│ f42d97a8670846a737a972329cf44e5b092954ef7e30681f73d70963ad971e43 │ running              │

How does mjq work? It's this function basically:

mjq() {
  local TFILE="/tmp/mjq-$((RANDOM % 100)).json"
  cat >  ${TFILE}
  local SQL="${1//JQ/read_json('${TFILE}')}"
  duckdb -c "${SQL}"

I used mjq because that's a prefix for my tools. You can build any SQL you want as arg, and JQ string will be replaced with the read_json output of the file provided as the input.

Further ideas:

  • the file was used since then further debugging can also be done without re-doing the upstream bash command. YMMV

More information about usage of DuckDB and the motivation for this script:

More information about DuckDB JSON extension: