DuckDBで一般的なjsonを扱う

事の​発端

私は​毎週、​月・水・金​曜日に​記事を​書く​vim駅伝と​いう​企画に​参加しており、​2023年3月から​2024年12月まで​毎月​1本以上の​記事を​書いています。
その​記事の​一覧は​こちらの​JSONファイルで​管理しているのですが、​ふと​年毎の​Vim駅伝の​参加人数を​調べたくなりました。
https://github.com/vim-jp/ekiden/blob/main/src/content.json

そんな​ことを​考えていると、​DuckDBを​使うと​簡単に​JSONを​扱える​ことを​思い出したので、​試してみました。

※ DockDBの​インストール方​法はこちらを​参照してください。

やりかた

DuckDBでは​標準で​JSONを​扱う​ことのできる​関数が​用意されています。
ですら、​まずは​このような​SQLを​書いてみました。

12
SELECT *
FROM read_json('src/content.json');

すると、​以下のような​出力が​得られました。

12345678910111213
> duckdb
v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D SELECT *
  FROM read_json('src/content.json');
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                articles                                                                                 │
│                                 struct(title varchar, date date, runner varchar, url varchar, githubuser varchar, issuenumber bigint)[]                                 │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ [{'title': Vim 駅伝、始動します, 'date': 2023-03-01, 'runner': thinca, 'url': https://thinca.hatenablog.com/entry/vim-ekiden-is-launched, 'githubUser': thinca, 'issu…  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

DuckDBを​何もしらない​私と​しては、​これだけで​配列が​展開された​形で​出力される​ものと​思っていたので、​びっくりしました。
調べてみた​ところ、​DuckDBでは​1行毎に​JSON Lines形式の​ログファイルなどを​読みこみ、​解析する​ことができるようです。​その​ため、​配列が​展開された​形で​出力される​わけではないようです。

そこで、​再度しらべてみた​ところ、unnest1 2と​いう​関数を​使う​ことで、​JSONを​フラットに​する​ことができるようです。

ですから、​正解の​SQLは​以下のようになります。

12345
SELECT a.*
FROM(
	SELECT unnest(articles) as a
	FROM read_json('src/content.json')
);

これで、​以下のような​出力が​得られました。

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950
┌──────────────────────┬────────────┬────────────────┬─────────────────────────────────────────────────────────────────────────────────────┬────────────────┬─────────────┐
│        title         │    date    │     runner     │                                         url                                         │   githubUser   │ issueNumber │
│       varchar        │    date    │    varchar     │                                       varchar                                       │    varchar     │    int64    │
├──────────────────────┼────────────┼────────────────┼─────────────────────────────────────────────────────────────────────────────────────┼────────────────┼─────────────┤
│ Vim 駅伝、始動します │ 2023-03-01 │ thinca         │ https://thinca.hatenablog.com/entry/vim-ekiden-is-launched                          │ thinca         │          11 │
│ Insertモードでも気…  │ 2023-03-03 │ atusy          │ https://blog.atusy.net/2023/03/03/horizontal-arrows-on-insert/                      │ atusy          │          12 │
│ bunsetsu.vimとbuns…  │ 2023-03-06 │ ryoppippi      │ https://github.com/ryoppippi/bunsetsu-wb.nvim/blob/main/README.md                   │ ryoppippi      │          15 │
│ Project V 「思考の…  │ 2023-03-08 │ tani           │ https://gist.github.com/tani/04c52c12ab4254528c1ba7ad509946ad                       │ tani           │          16 │
│ heirline.nvimでNeo…  │ 2023-03-10 │ kyoh86         │ https://zenn.dev/kyoh86/articles/681ab90a44302c                                     │ kyoh86         │          17 │
│ Vim,Neovimのフォン…  │ 2023-03-13 │ staticWagomU   │ https://wagomu.me/blog/2023-03-12-vim-ekiden                                        │ staticWagomU   │          18 │
│ Vim Input Method E…  │ 2023-03-15 │ kuuote         │ https://zenn.dev/vim_jp/articles/20230315_skkeleton                                 │ kuuote         │          20 │
│ lualine.nvimでステ…  │ 2023-03-17 │ Liquid_system  │ https://liquid-system.github.io/lualine.nvim%E3%81%A7%E3%82%B9%E3%83%86%E3%83%BC%…  │ Liquid-system  │          22 │
│ Vim9 script の始め方 │ 2023-03-20 │ thinca         │ https://thinca.hatenablog.com/entry/execute-vim9-script                             │ thinca         │          32 │
│ 錆びついたinit.vim…  │ 2023-03-22 │ matoruru       │ https://qiita.com/matoruru/items/b5ad6e0f1ef6c804378d                               │ matoruru       │          33 │
│ neovimターミナルモ…  │ 2023-03-24 │ fuzmare        │ https://zenn.dev/fuzmare/articles/vim-term-escape                                   │ fuzmare        │          35 │
│ Vimを使わないVimme…  │ 2023-03-27 │ tadashi-aikawa │ https://minerva.mamansoft.net/%F0%9F%93%98Articles/%F0%9F%93%98Vim%E3%82%92%E4%BD…  │ tadashi-aikawa │          41 │
│ Vimのテキストオブ…   │ 2023-03-29 │ ArcCosine      │ https://looxu.blogspot.com/2023/03/vim-surroundvim.html                             │ ArcCosine      │          43 │
│ Vim 駅伝開始1ヶ月…   │ 2023-03-31 │ monaqa         │ https://zenn.dev/vim_jp/articles/2023-03-31-vim-ekiden-looking-back-the-first-month │ monaqa         │          44 │
│ Vim + npm install …  │ 2023-04-03 │ IK             │ https://qiita.com/get_me_power/items/c20447b9cab4eeaef4e4                           │ get-me-power   │          45 │
│ ミニマリストに憧れ…  │ 2023-04-05 │ eihigh         │ https://eihigh.hatenablog.com/entry/2023/04/05/000000                               │ eihigh         │          51 │
│ vusted & nvim-kit…   │ 2023-04-07 │ uga-rosa       │ https://zenn.dev/uga_rosa/articles/6a69ecb4dd26c1                                   │ uga-rosa       │          53 │
│ denops-docker.vim …  │ 2023-04-10 │ ゴリラ         │ https://zenn.dev/vim_jp/articles/2023-03-29-vim-docker-vim-bug-fix                  │ skanehira      │          55 │
│ dial.nvim の新機能…  │ 2023-04-12 │ monaqa         │ https://zenn.dev/vim_jp/articles/2023-04-12-vim-dial-additive-increment             │ monaqa         │          57 │
│ HHKBとViｍとの親和…  │ 2023-04-14 │ ArcCosine      │ https://looxu.hateblo.jp/entry/an-hhkb-and-vim-wannabe-talks                        │ ArcCosine      │          59 │
│          ·           │     ·      │   ·            │                            ·                                                        │   ·            │           · │
│          ·           │     ·      │   ·            │                            ·                                                        │   ·            │           · │
│          ·           │     ·      │   ·            │                            ·                                                        │   ·            │           · │
│ Meguro.vim #26 を…   │ 2024-12-02 │ thinca         │ https://thinca.hatenablog.com/entry/2024/12/megurovim-26                            │ thinca         │         647 │
│ lazyvimの遅延読み…   │ 2024-12-04 │ tositada       │ https://zenn.dev/vim_jp/articles/609c75cea1208a                                     │ tositada17     │         697 │
│ Neovimでlazy load…   │ 2024-12-06 │ kyoh86         │ https://zenn.dev/vim_jp/articles/9a5258067a3132                                     │ kyoh86         │         728 │
│ Neovim でコマンド…   │ 2024-12-09 │ s-show         │ https://kankodori-blog.com/posts/2024-12-09/                                        │ s-show         │         729 │
│ neovimプラグインの…  │ 2024-12-11 │ tositada       │ https://zenn.dev/vim_jp/articles/dbafe015ce9a4e                                     │ tositada17     │         722 │
│ ソフトウェア技術者…  │ 2024-12-13 │ yuys13         │ https://zenn.dev/vim_jp/articles/vimjpradio22-guest-car                             │ yuys13         │         732 │
│ Vim 本体に evalfun…  │ 2024-12-16 │ mikoto2000     │ https://mikoto2000.blogspot.com/2024/12/vim-evalfunc-vim-script.html                │ mikoto2000     │         738 │
│ ちょっと面倒だなぁ…  │ 2024-12-18 │ kamecha        │ https://zenn.dev/trap/articles/5a76bf9af20875                                       │ kamecha        │         741 │
│ VimでTextObjの末尾…  │ 2024-12-20 │ utubo          │ https://zenn.dev/utubo/articles/vim-move-to-head-of-textobj                         │ utubo          │         739 │
│ 2024年 Neovim成長…   │ 2024-12-23 │ tadashi-aikawa │ https://minerva.mamansoft.net/%F0%9F%93%98Articles/%F0%9F%93%982024%E5%B9%B4%20Ne…  │ tadashi-aikawa │         730 │
│ 覚えられない操作は…  │ 2024-12-25 │ staticWagomU   │ https://wagomu.me/blog/2024-12-25-vim-ekiden                                        │ staticWagomU   │         731 │
│ プラグインなしでVi…  │ 2024-12-27 │ PicricAcid     │ https://qiita.com/picric_acid/items/ec987cf244ae86ff6fc4                            │ PicricAcid     │         740 │
│ 思考の速さで日本語…  │ 2024-12-30 │ NI57721        │ https://zenn.dev/vim_jp/articles/neo-azik-for-you                                   │ NI57721        │         749 │
│ NeoVimで書き初め(…   │ 2025-01-01 │ Kleha          │ https://zenn.dev/haru_0205/books/d8b9cd0cf37db9                                     │ Haru-0205      │         773 │
│ NeovimのDiagnostic…  │ 2025-01-03 │ yuys13         │ https://zenn.dev/vim_jp/articles/entering-neovim-diagnosic-floating-window          │ yuys13         │         784 │
│ telescope.nvim で…   │ 2025-01-06 │ delphinus      │                                                                                     │ delphinus      │         793 │
│ lazyvimに2ヶ月入っ…  │ 2025-01-08 │ tositada       │                                                                                     │ tositada17     │         775 │
│ vimiumはいいぞ       │ 2025-01-10 │ 静カニ         │ https://shizukani-cp.github.io/blog/articles/20250110/                              │ shizukani-cp   │         794 │
│ Vim正規表現とマク…   │ 2025-01-13 │ yasunori0418   │ https://blog.yasunori0418.dev/p/combining_vim_regex_and_macros/                     │ yasunori0418   │         804 │
│ Neovim内で翻訳でき…  │ 2025-01-15 │ 苔コッコー     │                                                                                     │ Kokecoco       │         805 │
├──────────────────────┴────────────┴────────────────┴─────────────────────────────────────────────────────────────────────────────────────┴────────────────┴─────────────┤
│ 295 rows (40 shown)                                                                                                                                           6 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

おわりに

これで、​後は​好きなように​集計すれば​良いですね。

後半の​記事に​続く。

https://wagomu.me/blog/2025-01-02_2

  1. https://duckdb.org/docs/sql/query_syntax/unnest.html ↩

  2. https://speakerdeck.com/ktz/duckdbza-shao-jie-1-dot-1dui-ying-ban-at-duckdbzuo-tan-hui?slide=19 ↩