TE
科技回声
首页24小时热榜最新最佳问答展示工作
GitHubTwitter
首页

科技回声

基于 Next.js 构建的科技新闻平台,提供全球科技新闻和讨论内容。

GitHubTwitter

首页

首页最新最佳问答展示工作

资源链接

HackerNews API原版 HackerNewsNext.js

© 2025 科技回声. 版权所有。

DuckDB as the New jq

364 点作者 pgr0ss大约 1 年前

23 条评论

xg15大约 1 年前
The most effective combination I&#x27;ve found so far is jq + basic shell tools.<p>I still think jq&#x27;s syntax and data model is unbelievably elegant and powerful once you get the hang of it - but its &quot;standard library&quot; 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.<p>Luckily, what jq can do <i>really well</i> 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 &quot;pure&quot; jq.<p>So I think my solution to the OP&#x27;s task - get all distinct OSS licenses from the project list and count usages for each one - would be:<p>curl ... | jq &#x27;.[].license.key&#x27; | sort | uniq -c<p>That&#x27;s it.
评论 #39785906 未加载
评论 #39786378 未加载
评论 #39807119 未加载
评论 #39799502 未加载
评论 #39788747 未加载
评论 #39786364 未加载
评论 #39790539 未加载
评论 #39786140 未加载
ndr大约 1 年前
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.<p><pre><code> cat repos.json | bb -e &#x27; (-&gt;&gt; (-&gt; *in* slurp (json&#x2F;parse-string true)) (group-by #(-&gt; % :license :key)) (map #(-&gt; {:license (key %) :count (-&gt; % val count)})) json&#x2F;generate-string println)&#x27; </code></pre> [0] <a href="https:&#x2F;&#x2F;babashka.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;babashka.org&#x2F;</a>
hu3大约 1 年前
Related, clickhouse local cli command is a speed demon to parse and query JSON and other formats such as CSV:<p>- &quot;The world’s fastest tool for querying JSON files&quot; <a href="https:&#x2F;&#x2F;clickhouse.com&#x2F;blog&#x2F;worlds-fastest-json-querying-tool-clickhouse-local" rel="nofollow">https:&#x2F;&#x2F;clickhouse.com&#x2F;blog&#x2F;worlds-fastest-json-querying-too...</a><p>- &quot;Show HN: ClickHouse-local – a small tool for serverless data analytics&quot; <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34265206">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34265206</a>
评论 #39785472 未加载
sshine大约 1 年前
Very cool!<p>I am also a big fan of jq.<p>And I think using DuckDB and SQL probably makes a lot of sense in a lot of cases.<p>But I think the examples are very geared towards being better solved in SQL.<p>The ideal jq examples are combinations of filter (select), map (map) and concat (.[]).<p>For example, finding the right download link:<p><pre><code> $ curl -s https:&#x2F;&#x2F;api.github.com&#x2F;repos&#x2F;go-gitea&#x2F;gitea&#x2F;releases&#x2F;latest \ | jq -r &#x27;.assets[] | .browser_download_url | select(endswith(&quot;linux-amd64&quot;))&#x27; https:&#x2F;&#x2F;github.com&#x2F;go-gitea&#x2F;gitea&#x2F;releases&#x2F;download&#x2F;v1.15.7&#x2F;gitea-1.15.7-linux-amd64 </code></pre> Or extracting the KUBE_CONFIG of a DigitalOcean Kubernetes cluster from Terraform state:<p><pre><code> $ jq -r &#x27;.resources[] | select(.type == &quot;digitalocean_kubernetes_cluster&quot;) | .instances[].attributes.kube_config[].raw_config&#x27; \ terraform.tfstate apiVersion: v1 kind: Config clusters: - cluster: certificate-authority-data: ... server: https:&#x2F;&#x2F;...k8s.ondigitalocean.com ...</code></pre>
评论 #39784665 未加载
jeffbee大约 1 年前
I tried this and it just seems to add bondage and discipline that I don&#x27;t need on top of what is, in practice, an extremely chaotic format.<p>Example: trying to pick one field out of 20000 large JSON files that represent local property records.<p>% duckdb -json -c &quot;select apn.apnNumber from read_json(&#x27;*&#x27;)&quot; Invalid Input Error: JSON transform error in file &quot;052136400500&quot;, in record&#x2F;value 1: Could not convert string &#x27;fb1b1e68-89ee-11ea-bc55-0242ad1302303&#x27; to INT128<p>Well, I didn&#x27;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.
mritchie712大约 1 年前
You can also query (public) Google Sheets [0]<p><pre><code> SELECT * FROM read_csv_auto(&#x27;https:&#x2F;&#x2F;docs.google.com&#x2F;spreadsheets&#x2F;export? format=csv&amp;id=1GuEPkwjdICgJ31Ji3iUoarirZNDbPxQj_kf7fd4h4Ro&#x27;, normalize_names=True); </code></pre> 0 - <a href="https:&#x2F;&#x2F;x.com&#x2F;thisritchie&#x2F;status&#x2F;1767922982046015840?s=20" rel="nofollow">https:&#x2F;&#x2F;x.com&#x2F;thisritchie&#x2F;status&#x2F;1767922982046015840?s=20</a>
NortySpock大约 1 年前
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.<p><a href="https:&#x2F;&#x2F;www.benthos.dev&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.benthos.dev&#x2F;</a>
评论 #39786034 未加载
评论 #39785107 未加载
haradion大约 1 年前
I&#x27;ve found Nushell (<a href="https:&#x2F;&#x2F;www.nushell.sh&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.nushell.sh&#x2F;</a>) to be really handy for ad-hoc data manipulation (and a decent enough general-purpose shell).
评论 #39787608 未加载
nf3大约 1 年前
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&#x27;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.
JeremyNT大约 1 年前
I have a lot of trouble understanding the benefits of this versus just working with json with a programming language. It seems like you&#x27;re adding another layer of abstraction versus just dealing with a normal hashmap-like data structure in your language of choice.<p>If you want to work with it interactively, you could use a notebook or REPL.
评论 #39788859 未加载
评论 #39786260 未加载
评论 #39786362 未加载
hprotagonist大约 1 年前
i&#x27;ve been using simonw&#x27;s sqlite-utils (<a href="https:&#x2F;&#x2F;sqlite-utils.datasette.io&#x2F;en&#x2F;stable&#x2F;" rel="nofollow">https:&#x2F;&#x2F;sqlite-utils.datasette.io&#x2F;en&#x2F;stable&#x2F;</a>) for this sort of thing; given structured json or jsonl, you can throw data at an in-memory sqlite database and query away: <a href="https:&#x2F;&#x2F;sqlite-utils.datasette.io&#x2F;en&#x2F;stable&#x2F;cli.html#querying-data-directly-using-an-in-memory-database" rel="nofollow">https:&#x2F;&#x2F;sqlite-utils.datasette.io&#x2F;en&#x2F;stable&#x2F;cli.html#queryin...</a>
评论 #39796418 未加载
pletnes大约 1 年前
Worth noting that both jq and duckdb can be used from python and from the command line. Both are very useful data tools!
ec109685大约 1 年前
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.
Sammi大约 1 年前
I work primarily in projects that use js and I mostly don&#x27;t see the point in working with json in other tools than js.<p>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.<p>When I want to munge some json I use js... because that is what js in innately good at and it&#x27;s what I already know. A little js script that does stdin&#x2F;file read and then JSON.parse, and then map and filter some stuff, and at the end JSON.stringify to stdout&#x2F;file does the job 100% of the time in my experience.<p>And I can use a debugger or put in console logs when I want to debug. I don&#x27;t know how to debug jq or sql, so when I&#x27;m stuck I end up going for js which I can debug.<p>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&#x27;t see the limitation of existing js or sql.
评论 #39790120 未加载
snthpy大约 1 年前
Hi,<p>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:<p>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.<p>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.<p>It doesn&#x27;t have much in the way of json functions out-of-the-box but in PRQL it&#x27;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.<p>Anyway, you can take a look at the repo here: <a href="https:&#x2F;&#x2F;github.com&#x2F;PRQL&#x2F;prql-query">https:&#x2F;&#x2F;github.com&#x2F;PRQL&#x2F;prql-query</a><p>If interested, you can get in touch with me via Github or the PRQL Discord. I&#x27;m @snth on both.
HellsMaddy大约 1 年前
Jq tip: Instead of `sort_by(.count) | reverse`, you can do `sort_by(-.count)`
评论 #39785429 未加载
mutant大约 1 年前
<a href="https:&#x2F;&#x2F;github.com&#x2F;mikefarah&#x2F;yq">https:&#x2F;&#x2F;github.com&#x2F;mikefarah&#x2F;yq</a><p>Yq handles almost every format, and IMO easier to use.
schindlabua大约 1 年前
Shoutout to jqp, an interactive jq explorer.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;noahgorstein&#x2F;jqp">https:&#x2F;&#x2F;github.com&#x2F;noahgorstein&#x2F;jqp</a>
评论 #39797099 未加载
rpigab大约 1 年前
I love jq and yq, but sometimes I don&#x27;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.<p>Something like this, I have a version of this in a shell alias:<p><pre><code> python3 -c &quot;import json,sys;d=json.load(sys.stdin);print(doStuff(d[&#x27;path&#x27;][&#x27;etc&#x27;]))&quot; </code></pre> Pretty print is done with json.dumps.
phmx大约 1 年前
There is also a way to import a table from the STDIN (see also <a href="https:&#x2F;&#x2F;duckdb.org&#x2F;docs&#x2F;data&#x2F;json&#x2F;overview" rel="nofollow">https:&#x2F;&#x2F;duckdb.org&#x2F;docs&#x2F;data&#x2F;json&#x2F;overview</a>)<p>cat my.json | duckdb -c &quot;CREATE TABLE mytbl AS SELECT * FROM read_json_auto(&#x27;&#x2F;dev&#x2F;stdin&#x27;); SELECT ... FROM mytbl&quot;
dudus大约 1 年前
DuckDB parses JSON using yyjson internally .<p><a href="https:&#x2F;&#x2F;github.com&#x2F;ibireme&#x2F;yyjson">https:&#x2F;&#x2F;github.com&#x2F;ibireme&#x2F;yyjson</a>
jonfw大约 1 年前
My current team produces a CLI binary that is available on every build system and everybody&#x27;s dev machines<p>Whenever we&#x27;re writing automation, if the code is nontrivial, or if it starts to include dependencies, we move the code into the CLI tool.<p>The reason we like this is that we don&#x27;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.
hermitcrab大约 1 年前
if you want a very visual way to transform JSON&#x2F;XML&#x2F;CSV&#x2F;Excel etc in a pipeline it might also be worth looking at Easy Data Transform.