# 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:

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

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

```bash
➜ 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:

```bash
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: [https://www.pgrs.net/2024/03/21/duckdb-as-the-new-jq/](https://www.pgrs.net/2024/03/21/duckdb-as-the-new-jq/)

More information about DuckDB JSON extension: [https://duckdb.org/docs/extensions/json.html](https://duckdb.org/docs/extensions/json.html)