這是某群前些天某人的一個(gè)提問以及我的回答。

這個(gè)話題讓我想到2016年，客戶扔了個(gè)10G的json文件過來，BA想提取里面的信息，卻發(fā)現(xiàn)Windows下沒有合適的工具打開它，問我咋辦，我當(dāng)時(shí)是用grep處理的。后來知道了jq^[1]，

jq 是一個(gè)輕量級(jí)且靈活的命令行JSON處理器，它被設(shè)計(jì)來處理JSON數(shù)據(jù)，就像sed用于處理文本數(shù)據(jù)一樣。你可以使用jq來切片、過濾、映射和轉(zhuǎn)換結(jié)構(gòu)化數(shù)據(jù)，其語法簡(jiǎn)潔，易于上手。

jq是一個(gè)強(qiáng)大的工具，特別適合需要處理和轉(zhuǎn)換JSON數(shù)據(jù)的開發(fā)者和系統(tǒng)管理員使用。它的設(shè)計(jì)哲學(xué)和功能集使得它成為處理JSON數(shù)據(jù)的不二之選。

其實(shí)這個(gè)不二之選，自從有了DuckDB，就不成立了。相比較jq，還是DuckDB更香。

其實(shí)我之前就向大家介紹了DuckDB的文章，探索DuckDB：使用SQL和DuckDB分析JSON數(shù)據(jù)。

今天看到一篇文章，和我的想法不謀而合。DuckDB as the New jq^[2] 

以下是全文，

最近，我對(duì)DuckDB^[3]項(xiàng)目（一個(gè)面向數(shù)據(jù)應(yīng)用的SQLite^[4]）產(chǎn)生了興趣。它最令人驚嘆的特性之一是包含了許多數(shù)據(jù)導(dǎo)入器，而且不需要額外的依賴。這意味著它可以直接將JSON作為數(shù)據(jù)庫(kù)表讀取和解析，同時(shí)還支持許多其他格式。

我日常工作中大量使用JSON，經(jīng)常使用 jq^[5]來探索文檔。我喜歡jq，但我發(fā)現(xiàn)它難以使用。它的語法非常強(qiáng)大，但我每次想要做超出僅僅選擇字段的操作時(shí)，都必須研究文檔。

一旦我了解到DuckDB可以直接將JSON文件讀取到內(nèi)存中，我就意識(shí)到我可以用它來做很多我目前使用jq做的事情。與復(fù)雜且自定義的jq語法相比，我對(duì)SQL非常熟悉，并且?guī)缀趺刻於荚谑褂谩?/p>

這里有一個(gè)例子：

首先，我們獲取一些示例JSON來玩。我使用GitHub API獲取了golang組織的倉(cāng)庫(kù)信息：

% curl 'https://api.github.com/orgs/golang/repos' > repos.json

現(xiàn)在，作為一個(gè)示例問題來回答，讓我們獲取一些關(guān)于使用的開源許可證類型的統(tǒng)計(jì)信息。

JSON結(jié)構(gòu)如下所示：

[
  {
    "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",
      ...
    },
    ...
  },
  ...
]

這可能不是最好的方法，但在我搜索并閱讀了一些關(guān)于如何在jq中做到這一點(diǎn)的文檔后，我拼湊出了以下內(nèi)容：

 % 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 │
└──────────────┴───────┘

對(duì)我來說，這個(gè)SQL要簡(jiǎn)單得多，我能夠不查看任何文檔就寫出來。唯一的難點(diǎn)是使用->>操作符查詢嵌套的JSON。這種語法與PostgreSQL JSON Functions^[6]相同，所以我對(duì)此很熟悉。

如果我們確實(shí)需要以JSON格式輸出，DuckDB提供了一個(gè)標(biāo)志來實(shí)現(xiàn)這一點(diǎn)：

% 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只是導(dǎo)入DuckDB數(shù)據(jù)的多種方式之一。這種方法同樣適用于CSV、parquet、Excel文件等。

我可以選擇創(chuàng)建表并本地持久化，但通常我只是在查詢數(shù)據(jù)，不需要持久化。

想要了解更多關(guān)于DuckDB出色的JSON支持，請(qǐng)閱讀這篇博客文章：一次一個(gè)向量地撕裂深層嵌套的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] 一次一個(gè)向量地撕裂深層嵌套的JSON: https://duckdb.org/2023/03/03/json.html