DuckDB as the New jq

#1

Post by pgr0ss » Thu, Mar 21, 2024, 6:19 PM UTC

DuckDB as the New jq
pgrs.net

Re: DuckDB as the New jq

#2

Post by sshine » Thu, Mar 21, 2024, 8:58 PM UTC

Very cool!

I am also a big fan of jq.

And I think using DuckDB and SQL probably makes a lot of sense in a lot of cases.

But I think the examples are very geared towards being better solved in SQL.

The ideal jq examples are combinations of filter (select), map (map) and concat (.[]).

For example, finding the right download link:

  $ curl -s https://api.github.com/repos/go-gitea/gitea/releases/latest \
    | jq -r '.assets[]
             | .browser_download_url
             | select(endswith("linux-amd64"))'
  https://github.com/go-gitea/gitea/releases/download/v1.15.7/gitea-1.15.7-linux-amd64

Or extracting the KUBE_CONFIG of a DigitalOcean Kubernetes cluster from Terraform state:

  $ jq -r '.resources[]
          | select(.type == "digitalocean_kubernetes_cluster")
          | .instances[].attributes.kube_config[].raw_config' \ 
      terraform.tfstate
  apiVersion: v1
  kind: Config
  clusters:
  - cluster:
      certificate-authority-data: ...
      server: https://...k8s.ondigitalocean.com
  ...

Re: DuckDB as the New jq

#3

Post by hprotagonist » Thu, Mar 21, 2024, 9:17 PM UTC

i've been using simonw's sqlite-utils (https://sqlite-utils.datasette.io/en/stable/) for this sort of thing; given structured json or jsonl, you can throw data at an in-memory sqlite database and query away: https://sqlite-utils.datasette.io/en/stable/cli.html#queryin...

Re: DuckDB as the New jq

#4

Post by NortySpock » Thu, Mar 21, 2024, 9:25 PM UTC

In a similar vein, I have found Benthos to be an incredible swiss-army-knife for transforming data and shoving it either into (or out of) a message bus, webhook, or a database.

https://www.benthos.dev/

Re: DuckDB as the New jq

#5

Post by pgr0ss » Thu, Mar 21, 2024, 9:32 PM UTC

sshine wrote: post #2

Very cool! I am also a big fan of jq. And I think using DuckDB and SQL probably makes a lot of sense in a lot of cases. But I think the examples are very geared towards being better solved in SQL. The ideal jq examples are combinations of filter (select), map (map) and concat (.[]). For example, finding the right download link: $ curl -s https://api.github.com/repos/go-gitea/gitea/releases/latest \ | jq -r '.assets[]…

I think that's a fair point. Unnesting arrays in SQL can be annoying. Here is your first example with duckdb:

  duckdb -c \
    "select * from ( \
      select unnest(assets)->>'browser_download_url' as url \
      from read_json('https://api.github.com/repos/go-gitea/gitea/releases/latest') \
    ) \
    where url like '%linux-amd64'"

Re: DuckDB as the New jq

#6

Post by pletnes » Thu, Mar 21, 2024, 9:34 PM UTC

Worth noting that both jq and duckdb can be used from python and from the command line. Both are very useful data tools!

Re: DuckDB as the New jq

#7

Post by haradion » Thu, Mar 21, 2024, 9:41 PM UTC

I've found Nushell (https://www.nushell.sh/) to be really handy for ad-hoc data manipulation (and a decent enough general-purpose shell).

Re: DuckDB as the New jq

#8

Post by HellsMaddy » Thu, Mar 21, 2024, 9:44 PM UTC

Jq tip: Instead of `sort_by(.count) | reverse`, you can do `sort_by(-.count)`

Re: DuckDB as the New jq

#9

Post by jeffbee » Thu, Mar 21, 2024, 9:57 PM UTC

I tried this and it just seems to add bondage and discipline that I don't need on top of what is, in practice, an extremely chaotic format.

Example: trying to pick one field out of 20000 large JSON files that represent local property records.

% duckdb -json -c "select apn.apnNumber from read_json('*')" Invalid Input Error: JSON transform error in file "052136400500", in record/value 1: Could not convert string 'fb1b1e68-89ee-11ea-bc55-0242ad1302303' to INT128

Well, I didn't want that converted. I just want to ignore it. This has been my experience overall. DuckDB is great if there is a logical schema, not as good as jq when the corpus is just data soup.

Re: DuckDB as the New jq

#10

Post by hu3 » Thu, Mar 21, 2024, 10:01 PM UTC

Related, clickhouse local cli command is a speed demon to parse and query JSON and other formats such as CSV:

- "The world’s fastest tool for querying JSON files" https://clickhouse.com/blog/worlds-fastest-json-querying-too...

- "Show HN: ClickHouse-local – a small tool for serverless data analytics" https://news.ycombinator.com/item?id=34265206