7 ms·

DuckDB as the New jq

364 points by pgr0ss 2y ago

haradion 2y ago
I've found Nushell (https://www.nushell.sh/ https://www.nushell.sh/) to be really handy for ad-hoc data manipulation (and a decent enough general-purpose shell).
- wraptile 2y ago
  Nushell is really good but the learning curve is massive. I've been on nushell for almost a year now and still struggle to put more complex commands together. The docs are huge but not very good and the community resources are very limited (it's on Dicord smh) unfortunately. So, if anyone wants to get into it you really need to put down few days to understand the whole syntax suite but it's worth it!
sshine 2y ago
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 ...
- pgr0ss 2y ago
  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'"
  - _flux 2y ago
    In case someone else was wondering, one can get a shell-consumable output from that with duckdb -noheader -list
hprotagonist 2y ago
i've been using simonw's sqlite-utils (https://sqlite-utils.datasette.io/en/stable/ 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#querying-data-directly-using-an-in-memory-database https://sqlite-utils.datasette.io/en/stable/cli.html#queryin...
- mutant 2y ago
  Thank you was going to say this as well, sqlite does this the same as duckdb
NortySpock 2y ago
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/ https://www.benthos.dev/
- krembo 2y ago
  How does this defer from filebeat?
  - NortySpock 2y ago
    I don't know which filebeat you are referring to... https://github.com/elastic/beats/tree/master/filebeat https://github.com/elastic/beats/tree/master/filebeat This one? I only looked for a moment, but filebeat appears to be ingestion only. Benthos does input, output, side-effects, stream-stream joins, metrics-on-the-side, tiny-json-wrangling-webooks, and more. I find it to be like plumbers putty, closing over tooling gaps and smoothing rough edges where ordinarily you'd have to write 20 lines of stream processing code and 300 lines of error handling, reporting, and performance hacks.
- esafak 2y ago
  I wish it was not based on YAML. Pipelines are code, not configuration!!
pletnes 2y ago
Worth noting that both jq and duckdb can be used from python and from the command line. Both are very useful data tools!
HellsMaddy 2y ago
Jq tip: Instead of `sort_by(.count) | reverse`, you can do `sort_by(-.count)`
- philsnow 2y ago
  only if you're sure that .count is never null: $ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(-.count)' jq: error (at <stdin>:1): null (null) cannot be negated $ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(.count) | reverse' [{"a":{"count":null}}]
  - mdaniel 2y ago
    this whole thread is like nerd sniping me :-D but I felt compelled to draw attention to jq's coalesce operator because I only recently learned about it and searching for the word "coalesce" in the man page is pfffft (it's official name is "Alternative operator", with alternative being "for false and null") $ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(-(.count//0))' [{"a":{"count":null}}]
jeffbee 2y ago
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.
hu3 2y ago
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-tool-clickhouse-local 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 https://news.ycombinator.com/item?id=34265206
- mightybyte 2y ago
  I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested: clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty" Way easier than opening in Excel and creating a pivot table which was my previous workflow. Here's a list of the different input and output formats that it supports. https://clickhouse.com/docs/en/interfaces/formats https://clickhouse.com/docs/en/interfaces/formats
  - gkbrk 2y ago
    You don't even need to use file() for a lot of things recently. These just work with clickhouse local. Even wildcards work. select * from `foobar.csv` or select * from `monthly-report-*.csv`
    - mightybyte 2y ago
      Ooh very nice, thanks for the tip!
    - wwader 2y ago
      Just had to try: $ function _select_aux () { clickhouse local -q "SELECT $* FORMAT Pretty" } $ alias SELECT='noglob _select_aux' $ SELECT COUNT(*) as count FROM file('repos.json', JSON) ┏━━━━━━━┓ ┃ count ┃ ┡━━━━━━━┩ │ 30 │ └───────┘
      - 2y ago
hermitcrab 2y ago
if you want a very visual way to transform JSON/XML/CSV/Excel etc in a pipeline it might also be worth looking at Easy Data Transform.
mritchie712 2y ago
You can also query (public) Google Sheets [0] SELECT * FROM read_csv_auto('https://docs.google.com/spreadsheets/export? format=csv&id=1GuEPkwjdICgJ31Ji3iUoarirZNDbPxQj_kf7fd4h4Ro', normalize_names=True); 0 - https://x.com/thisritchie/status/1767922982046015840?s=20 https://x.com/thisritchie/status/1767922982046015840?s=20
dudus 2y ago
DuckDB parses JSON using yyjson internally . https://github.com/ibireme/yyjson https://github.com/ibireme/yyjson
ec109685 2y ago
While jq’s syntax can be hard to remember, ChatGTP does an excellent job generating jq from an example json file and a description of how you want it parsed.
xg15 2y ago
The most effective combination I've found so far is jq + basic shell tools. I still think jq's syntax and data model is unbelievably elegant and powerful once you get the hang of it - but its "standard library" is unfortunately sorely lacking in many places and has some awkward design choices in others, which means that a lot of practical everyday tasks - such as aggregations or even just set membership - are a lot more complicated than they ought to be. Luckily, what jq can do really well is bringing data of interest into a line-based text representation, which is ideal for all kinds of standard unix shell tools - so you can just use those to take over the parts of your pipeline that would be hard to do in "pure" jq. So I think my solution to the OP's task - get all distinct OSS licenses from the project list and count usages for each one - would be: curl ... | jq '.[].license.key' | sort | uniq -c That's it.
- pcthrowaway 2y ago
  > I still think jq's syntax and data model is unbelievably elegant and powerful once you get the hang of it - but its "standard library" is unfortunately sorely lacking in many places After a few years of stalled development, jq has been taken over recently by a new team of maintainers and is rapidly working through a lot of longstanding issues (https://github.com/jqlang/jq https://github.com/jqlang/jq), so I'm not sure if this is still the case
  - xg15 2y ago
    Wasn't aware of that, that's great to hear! I think if there is one utility that deserves a great maintainer team then this one. But if we saw some actual improvements in the future, that would be awesome! I have a list of pet peeves that I'd really like to see fixed, so I'm gonna risk a bit of hope.
- jimbokun 2y ago
  The Unix philosophy continues to pass the test of time.
  - pphysch 2y ago
    Yes and no. Many UNIX philosophy proponents are abhorred by powerful binaries like jq and awk.
JeremyNT 2y ago
I have a lot of trouble understanding the benefits of this versus just working with json with a programming language. It seems like you're adding another layer of abstraction versus just dealing with a normal hashmap-like data structure in your language of choice. If you want to work with it interactively, you could use a notebook or REPL.
- edu_guitar 2y ago
  if you are used to the command line and knows some basic syntax, it is less verbose then opening a REPL and reading a file. The fact that you can pipe the json data into it is also a plus, making it easier to check quickly if the response of a curl call has the fields/values you were expecting. Of course, if you are more comfortable doing that from the REPL, you get less value from learning jq. If you are fond of one liners, jq offers a lot of potential.
- bdcravens 2y ago
  Pipelining CLI commands or bash scripts. From a security perspective, it may be preferable to not ship with a runtime.
  - vips7L 2y ago
    bash and jq are both runtimes.
    - bdcravens 2y ago
      Very difficult (or often impractical) to not have a shell at all, and jq is at least limited in scope, and has no dependencies that need to be installed. Far better than a full language with its own standard library and set of dependencies to lock down.
  - jonfw 2y ago
    Use a compiled language like golang if you don't want to ship with a runtime. If you're willing to ship w/ bash then I don't understand the opposition to JS. Either tool puts you in a scenario where somebody who can exec into your env can do whatever they want
ndr 2y ago
If you like lisp, and especially clojure, check out babashka[0]. This my first attempt but I bet you can do something nicer even if you keep forcing yourself to stay into a single pipe command. cat repos.json | bb -e ' (->> (-> *in* slurp (json/parse-string true)) (group-by #(-> % :license :key)) (map #(-> {:license (key %) :count (-> % val count)})) json/generate-string println)' [0] https://babashka.org/ https://babashka.org/
schindlabua 2y ago
Shoutout to jqp, an interactive jq explorer. https://github.com/noahgorstein/jqp https://github.com/noahgorstein/jqp
- parentheses 2y ago
  This is pretty nice!
nf3 2y ago
I run a pretty substantial platform where I implemented structured logging to SQLite databases. Each log event is stored as a JSON object in a row. A separate database is kept for each day. Daily log files are about 35GB, so that's quite a lot of data to go through is you want to look for something specific. Being able to index on specific fields, as well as express searches as SQL queries is a real game changer IMO.
rpigab 2y ago
I love jq and yq, but sometimes I don't want to invest time in learning new syntax and just fallback to some python one liner, that can if necessary become a small python script. Something like this, I have a version of this in a shell alias: python3 -c "import json,sys;d=json.load(sys.stdin);print(doStuff(d['path']['etc']))" Pretty print is done with json.dumps.
phmx 2y ago
There is also a way to import a table from the STDIN (see also https://duckdb.org/docs/data/json/overview https://duckdb.org/docs/data/json/overview) cat my.json | duckdb -c "CREATE TABLE mytbl AS SELECT * FROM read_json_auto('/dev/stdin'); SELECT ... FROM mytbl"
Sammi 2y ago
I work primarily in projects that use js and I mostly don't see the point in working with json in other tools than js. I have tried jq a little bit, but learning jq is learning a new thing, which is healthy, but it also requires time and energy, which is not always available. When I want to munge some json I use js... because that is what js in innately good at and it's what I already know. A little js script that does stdin/file read and then JSON.parse, and then map and filter some stuff, and at the end JSON.stringify to stdout/file does the job 100% of the time in my experience. And I can use a debugger or put in console logs when I want to debug. I don't know how to debug jq or sql, so when I'm stuck I end up going for js which I can debug. Are there js developers who reach for jq when you are already familiar with js? Is it because you are already strong in bash and terminal usage? I think I get why you would want to use sql if you are already experienced in sql. Sql is common and made for data munging. Jq however is a new dsl when I don't see the limitation of existing js or sql.
- wwader 2y ago
  I do quite a lot of adhoc/exploratory programming to query and transform data then jq is very convenient as it works very well with "deep" data structures and the language itself it very composable. To debug in jq you can use the debug function to prints to stderr, ex: "123 | debug | ..." or "{a:123, b:456} | debug({a}) | ... " only prints value of a "{a:123}"
jonfw 2y ago
My current team produces a CLI binary that is available on every build system and everybody's dev machines Whenever we're writing automation, if the code is nontrivial, or if it starts to include dependencies, we move the code into the CLI tool. The reason we like this is that we don't want to have to version control tools like duckdb across every dev machine and every build system that might run this script. We build and version control a single binary and it makes life simple.
snthpy 2y ago
Hi, I very much share your sentiment and I saw a few comments mentioning PRQL so I thought it might be worth bringing up the following: In order to make working with data at the terminal as easy and fun as possible, some time ago I created pq (prql-query) which leverages DuckDB, DataFusion and PRQL. Unfortunately I am currently not in a position to maintain it so the repo is archived but if someone wanted to help out and collaborate we could change that. It doesn't have much in the way of json functions out-of-the-box but in PRQL it's easy to wrap the DuckDB functions for that and with the new PRQL module system it will soon also become possible to share those. If you look through my HN comment history I did provide a JSON example before. Anyway, you can take a look at the repo here: https://github.com/PRQL/prql-query https://github.com/PRQL/prql-query If interested, you can get in touch with me via Github or the PRQL Discord. I'm @snth on both.
mutant 2y ago
https://github.com/mikefarah/yq https://github.com/mikefarah/yq Yq handles almost every format, and IMO easier to use.

364 points by pgr0ss 2y ago

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

wraptile 2y ago
Nushell is really good but the learning curve is massive. I've been on nushell for almost a year now and still struggle to put more complex commands together. The docs are huge but not very good and the community resources are very limited (it's on Dicord smh) unfortunately. So, if anyone wants to get into it you really need to put down few days to understand the whole syntax suite but it's worth it!

sshine 2y ago

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 ...

pgr0ss 2y ago
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'"
- _flux 2y ago
  In case someone else was wondering, one can get a shell-consumable output from that with duckdb -noheader -list

hprotagonist 2y ago

i've been using simonw's sqlite-utils (https://sqlite-utils.datasette.io/en/stable/ 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#querying-data-directly-using-an-in-memory-database https://sqlite-utils.datasette.io/en/stable/cli.html#queryin...

mutant 2y ago
Thank you was going to say this as well, sqlite does this the same as duckdb

NortySpock 2y ago

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/ https://www.benthos.dev/

krembo 2y ago
How does this defer from filebeat?
- NortySpock 2y ago
  I don't know which filebeat you are referring to... https://github.com/elastic/beats/tree/master/filebeat https://github.com/elastic/beats/tree/master/filebeat This one? I only looked for a moment, but filebeat appears to be ingestion only. Benthos does input, output, side-effects, stream-stream joins, metrics-on-the-side, tiny-json-wrangling-webooks, and more. I find it to be like plumbers putty, closing over tooling gaps and smoothing rough edges where ordinarily you'd have to write 20 lines of stream processing code and 300 lines of error handling, reporting, and performance hacks.
esafak 2y ago
I wish it was not based on YAML. Pipelines are code, not configuration!!

pletnes 2y ago

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

HellsMaddy 2y ago

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

philsnow 2y ago
only if you're sure that .count is never null: $ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(-.count)' jq: error (at <stdin>:1): null (null) cannot be negated $ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(.count) | reverse' [{"a":{"count":null}}]
- mdaniel 2y ago
  this whole thread is like nerd sniping me :-D but I felt compelled to draw attention to jq's coalesce operator because I only recently learned about it and searching for the word "coalesce" in the man page is pfffft (it's official name is "Alternative operator", with alternative being "for false and null") $ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(-(.count//0))' [{"a":{"count":null}}]

jeffbee 2y ago

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.

hu3 2y ago

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-tool-clickhouse-local 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 https://news.ycombinator.com/item?id=34265206

mightybyte 2y ago
I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested: clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty" Way easier than opening in Excel and creating a pivot table which was my previous workflow. Here's a list of the different input and output formats that it supports. https://clickhouse.com/docs/en/interfaces/formats https://clickhouse.com/docs/en/interfaces/formats
- gkbrk 2y ago
  You don't even need to use file() for a lot of things recently. These just work with clickhouse local. Even wildcards work. select * from `foobar.csv` or select * from `monthly-report-*.csv`
  - mightybyte 2y ago
    Ooh very nice, thanks for the tip!
  - wwader 2y ago
    Just had to try: $ function _select_aux () { clickhouse local -q "SELECT $* FORMAT Pretty" } $ alias SELECT='noglob _select_aux' $ SELECT COUNT(*) as count FROM file('repos.json', JSON) ┏━━━━━━━┓ ┃ count ┃ ┡━━━━━━━┩ │ 30 │ └───────┘
    - 2y ago

hermitcrab 2y ago

if you want a very visual way to transform JSON/XML/CSV/Excel etc in a pipeline it might also be worth looking at Easy Data Transform.

mritchie712 2y ago

You can also query (public) Google Sheets [0] SELECT * FROM read_csv_auto('https://docs.google.com/spreadsheets/export? format=csv&id=1GuEPkwjdICgJ31Ji3iUoarirZNDbPxQj_kf7fd4h4Ro', normalize_names=True); 0 - https://x.com/thisritchie/status/1767922982046015840?s=20 https://x.com/thisritchie/status/1767922982046015840?s=20

dudus 2y ago

DuckDB parses JSON using yyjson internally . https://github.com/ibireme/yyjson https://github.com/ibireme/yyjson

ec109685 2y ago

While jq’s syntax can be hard to remember, ChatGTP does an excellent job generating jq from an example json file and a description of how you want it parsed.

xg15 2y ago

The most effective combination I've found so far is jq + basic shell tools. I still think jq's syntax and data model is unbelievably elegant and powerful once you get the hang of it - but its "standard library" is unfortunately sorely lacking in many places and has some awkward design choices in others, which means that a lot of practical everyday tasks - such as aggregations or even just set membership - are a lot more complicated than they ought to be. Luckily, what jq can do really well is bringing data of interest into a line-based text representation, which is ideal for all kinds of standard unix shell tools - so you can just use those to take over the parts of your pipeline that would be hard to do in "pure" jq. So I think my solution to the OP's task - get all distinct OSS licenses from the project list and count usages for each one - would be: curl ... | jq '.[].license.key' | sort | uniq -c That's it.

pcthrowaway 2y ago
> I still think jq's syntax and data model is unbelievably elegant and powerful once you get the hang of it - but its "standard library" is unfortunately sorely lacking in many places After a few years of stalled development, jq has been taken over recently by a new team of maintainers and is rapidly working through a lot of longstanding issues (https://github.com/jqlang/jq https://github.com/jqlang/jq), so I'm not sure if this is still the case
- xg15 2y ago
  Wasn't aware of that, that's great to hear! I think if there is one utility that deserves a great maintainer team then this one. But if we saw some actual improvements in the future, that would be awesome! I have a list of pet peeves that I'd really like to see fixed, so I'm gonna risk a bit of hope.
jimbokun 2y ago
The Unix philosophy continues to pass the test of time.
- pphysch 2y ago
  Yes and no. Many UNIX philosophy proponents are abhorred by powerful binaries like jq and awk.

JeremyNT 2y ago

I have a lot of trouble understanding the benefits of this versus just working with json with a programming language. It seems like you're adding another layer of abstraction versus just dealing with a normal hashmap-like data structure in your language of choice. If you want to work with it interactively, you could use a notebook or REPL.

edu_guitar 2y ago
if you are used to the command line and knows some basic syntax, it is less verbose then opening a REPL and reading a file. The fact that you can pipe the json data into it is also a plus, making it easier to check quickly if the response of a curl call has the fields/values you were expecting. Of course, if you are more comfortable doing that from the REPL, you get less value from learning jq. If you are fond of one liners, jq offers a lot of potential.
bdcravens 2y ago
Pipelining CLI commands or bash scripts. From a security perspective, it may be preferable to not ship with a runtime.
- vips7L 2y ago
  bash and jq are both runtimes.
  - bdcravens 2y ago
    Very difficult (or often impractical) to not have a shell at all, and jq is at least limited in scope, and has no dependencies that need to be installed. Far better than a full language with its own standard library and set of dependencies to lock down.
- jonfw 2y ago
  Use a compiled language like golang if you don't want to ship with a runtime. If you're willing to ship w/ bash then I don't understand the opposition to JS. Either tool puts you in a scenario where somebody who can exec into your env can do whatever they want

ndr 2y ago

If you like lisp, and especially clojure, check out babashka[0]. This my first attempt but I bet you can do something nicer even if you keep forcing yourself to stay into a single pipe command. cat repos.json | bb -e ' (->> (-> *in* slurp (json/parse-string true)) (group-by #(-> % :license :key)) (map #(-> {:license (key %) :count (-> % val count)})) json/generate-string println)' [0] https://babashka.org/ https://babashka.org/

schindlabua 2y ago

Shoutout to jqp, an interactive jq explorer. https://github.com/noahgorstein/jqp https://github.com/noahgorstein/jqp

parentheses 2y ago
This is pretty nice!

nf3 2y ago

I run a pretty substantial platform where I implemented structured logging to SQLite databases. Each log event is stored as a JSON object in a row. A separate database is kept for each day. Daily log files are about 35GB, so that's quite a lot of data to go through is you want to look for something specific. Being able to index on specific fields, as well as express searches as SQL queries is a real game changer IMO.

rpigab 2y ago

I love jq and yq, but sometimes I don't want to invest time in learning new syntax and just fallback to some python one liner, that can if necessary become a small python script. Something like this, I have a version of this in a shell alias: python3 -c "import json,sys;d=json.load(sys.stdin);print(doStuff(d['path']['etc']))" Pretty print is done with json.dumps.

phmx 2y ago

There is also a way to import a table from the STDIN (see also https://duckdb.org/docs/data/json/overview https://duckdb.org/docs/data/json/overview) cat my.json | duckdb -c "CREATE TABLE mytbl AS SELECT * FROM read_json_auto('/dev/stdin'); SELECT ... FROM mytbl"

Sammi 2y ago

I work primarily in projects that use js and I mostly don't see the point in working with json in other tools than js. I have tried jq a little bit, but learning jq is learning a new thing, which is healthy, but it also requires time and energy, which is not always available. When I want to munge some json I use js... because that is what js in innately good at and it's what I already know. A little js script that does stdin/file read and then JSON.parse, and then map and filter some stuff, and at the end JSON.stringify to stdout/file does the job 100% of the time in my experience. And I can use a debugger or put in console logs when I want to debug. I don't know how to debug jq or sql, so when I'm stuck I end up going for js which I can debug. Are there js developers who reach for jq when you are already familiar with js? Is it because you are already strong in bash and terminal usage? I think I get why you would want to use sql if you are already experienced in sql. Sql is common and made for data munging. Jq however is a new dsl when I don't see the limitation of existing js or sql.

wwader 2y ago
I do quite a lot of adhoc/exploratory programming to query and transform data then jq is very convenient as it works very well with "deep" data structures and the language itself it very composable. To debug in jq you can use the debug function to prints to stderr, ex: "123 | debug | ..." or "{a:123, b:456} | debug({a}) | ... " only prints value of a "{a:123}"

jonfw 2y ago

My current team produces a CLI binary that is available on every build system and everybody's dev machines Whenever we're writing automation, if the code is nontrivial, or if it starts to include dependencies, we move the code into the CLI tool. The reason we like this is that we don't want to have to version control tools like duckdb across every dev machine and every build system that might run this script. We build and version control a single binary and it makes life simple.

snthpy 2y ago

Hi, I very much share your sentiment and I saw a few comments mentioning PRQL so I thought it might be worth bringing up the following: In order to make working with data at the terminal as easy and fun as possible, some time ago I created pq (prql-query) which leverages DuckDB, DataFusion and PRQL. Unfortunately I am currently not in a position to maintain it so the repo is archived but if someone wanted to help out and collaborate we could change that. It doesn't have much in the way of json functions out-of-the-box but in PRQL it's easy to wrap the DuckDB functions for that and with the new PRQL module system it will soon also become possible to share those. If you look through my HN comment history I did provide a JSON example before. Anyway, you can take a look at the repo here: https://github.com/PRQL/prql-query https://github.com/PRQL/prql-query If interested, you can get in touch with me via Github or the PRQL Discord. I'm @snth on both.

mutant 2y ago

https://github.com/mikefarah/yq https://github.com/mikefarah/yq Yq handles almost every format, and IMO easier to use.