这是某群前些天某人的一个提问以及我的回答。
这个话题让我想到2016年，客户扔了个10G的json文件过来，BA想提取里面的信息，却发现Windows下没有合适的工具打开它，问我咋办，我当时是用grep处理的。后来知道了jq[1]，
jq 是一个轻量级且灵活的命令行JSON处理器，它被设计来处理JSON数据，就像sed用于处理文本数据一样。你可以使用jq来切片、过滤、映射和转换结构化数据，其语法简洁，易于上手。
jq是一个强大的工具，特别适合需要处理和转换JSON数据的开发者和系统管理员使用。它的设计哲学和功能集使得它成为处理JSON数据的不二之选。
其实这个不二之选，自从有了DuckDB，就不成立了。相比较jq，还是DuckDB更香。
其实我之前就向大家介绍了DuckDB的文章，探索DuckDB：使用SQL和DuckDB分析JSON数据。
今天看到一篇文章，和我的想法不谋而合。DuckDB as the New jq[2]
以下是全文，
最近，我对DuckDB[3]项目（一个面向数据应用的SQLite[4]）产生了兴趣。它最令人惊叹的特性之一是包含了许多数据导入器，而且不需要额外的依赖。这意味着它可以直接将JSON作为数据库表读取和解析，同时还支持许多其他格式。
我日常工作中大量使用JSON，经常使用 jq[5]来探索文档。我喜欢jq
，但我发现它难以使用。它的语法非常强大，但我每次想要做超出仅仅选择字段的操作时，都必须研究文档。
一旦我了解到DuckDB可以直接将JSON文件读取到内存中，我就意识到我可以用它来做很多我目前使用jq
做的事情。与复杂且自定义的jq
语法相比，我对SQL非常熟悉，并且几乎每天都在使用。
这里有一个例子：
首先，我们获取一些示例JSON来玩。我使用GitHub API获取了golang组织的仓库信息：
% curl 'https://api.github.com/orgs/golang/repos' > repos.json
现在，作为一个示例问题来回答，让我们获取一些关于使用的开源许可证类型的统计信息。
JSON结构如下所示：
[
{
"id": 1914329,
"name": "gddo",
"license": {
"key": "bsd-3-clause",
"name": "BSD 3-Clause \"New\" or \"Revised\" License",
...
},
...
},
{
"id": 11440704,
"name": "glog",
"license": {
"key": "apache-2.0",
"name": "Apache License 2.0",
...
},
...
},
...
]
这可能不是最好的方法，但在我搜索并阅读了一些关于如何在jq
中做到这一点的文档后，我拼凑出了以下内容：
% cat repos.json | jq 'group_by(.license.key)
| map({license: .[0].license.key, count: length})
| sort_by(.count)
| reverse'
[
{
"license": "bsd-3-clause",
"count": 23
},
{
"license": "apache-2.0",
"count": 5
},
{
"license": null,
"count": 2
}
]
在DuckDB中使用SQL是这样的：
% duckdb -c "
select license->>'key' as license, count(*) as count
from 'repos.json'
group by 1
order by count desc"
┌──────────────┬───────┐
│ license │ count │
│ varchar │ int64 │
├──────────────┼───────┤
│ bsd-3-clause │ 23 │
│ apache-2.0 │ 5 │
│ │ 2 │
└──────────────┴───────┘
对我来说，这个SQL要简单得多，我能够不查看任何文档就写出来。唯一的难点是使用->>
操作符查询嵌套的JSON。这种语法与PostgreSQL JSON Functions[6]相同，所以我对此很熟悉。
如果我们确实需要以JSON格式输出，DuckDB提供了一个标志来实现这一点：
% duckdb -json -c "
select license->>'key' as license, count(*) as count
from 'repos.json'
group by 1
order by count desc"
[{"license":"bsd-3-clause","count":23},{"license":"apache-2.0","count":5},{"license":null,"count":2}]
我们甚至可以在DuckDB完成重活之后，使用jq
来美化输出：
% duckdb -json -c "
select license->>'key' as license, count(*) as count
from 'repos.json'
group by 1
order by count desc" | jq
[
{
"license": "bsd-3-clause",
"count": 23
},
{
"license": "apache-2.0",
"count": 5
},
{
"license": null,
"count": 2
}
]
JSON只是导入DuckDB数据的多种方式之一。这种方法同样适用于CSV、parquet、Excel文件等。
我可以选择创建表并本地持久化，但通常我只是在查询数据，不需要持久化。
想要了解更多关于DuckDB出色的JSON支持，请阅读这篇博客文章：一次一个向量地撕裂深层嵌套的JSON[7]
更新：
我还了解到，DuckDB可以直接从URL读取JSON，而不仅仅是本地文件：
% duckdb -c "
select license->>'key' as license, count(*) as count
from read_json('https://api.github.com/orgs/golang/repos')
group by 1
order by count desc"
引用链接
[1]
jq: https://jqlang.github.io/jq/[2]
DuckDB as the New jq: https://www.pgrs.net/2024/03/21/duckdb-as-the-new-jq/[3]
DuckDB: https://duckdb.org/[4]
SQLite: https://www.sqlite.org/[5]
jq: https://jqlang.github.io/jq/[6]
PostgreSQL JSON Functions: https://www.postgresql.org/docs/current/functions-json.html[7]
一次一个向量地撕裂深层嵌套的JSON: https://duckdb.org/2023/03/03/json.html
