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.
DuckDB as the New jq
11–20 of 74 posts
Re: DuckDB as the New jq
#12In 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/
How does this defer from filebeat?
Re: DuckDB as the New jq
#13You 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);
Re: DuckDB as the New jq
#14Jq tip: Instead of `sort_by(.count) | reverse`, you can do `sort_by(-.count)`
only if you're sure that .count is never null:
$ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(-.count)'
jq: error (at :1): null (null) cannot be negated
$ echo '[{"a": {"count": null}}]' | jq -c 'sort_by(.count) | reverse'
[{"a":{"count":null}}]Re: DuckDB as the New jq
#15DuckDB parses JSON using yyjson internally .
Re: DuckDB as the New jq
#16Related, 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
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"
Here's a list of the different input and output formats that it supports.
Re: DuckDB as the New jq
#17While 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.
Re: DuckDB as the New jq
#18The 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.
Re: DuckDB as the New jq
#19I 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.
Re: DuckDB as the New jq
#20The 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 m…
> 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), so I'm not sure if this is still the case