这是某群前些天某人的一个提问以及我的回答。

这个话题让我想到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