DuckDB で入れ子になっている JSON を UNNEST する話

この記事は BABY JOB Advent Calendar 2024 の 13 日目の記事です。

この記事は以下の記事の前編に相当します。
DuckDB で Web API を叩いて JSON をパースする話
こちらも併せて見ていただけると幸いです。

前座

今日は12月13日の金曜日です。

そう、13日の金曜日。。。

だから、ジェイソン。。。 JSON に関する話をします！ウォー！

とはいえ、JSON で一本記事を書くのは難しかったので、DuckDB で JSON を触ってみた話（前編）をします。^[1]

使用する DuckDB は現在の最新版 1.1.3 をベースにしています。

本題

課題となっていたこと

DuckDB で以下のような入れ子があるような JSON への対処方法について考えます。

blog.json（ブログ記事とコメント情報）

{
  "blog_post": {
    "id": 1,
    "title": "Understanding JSON",
    "author":  "Jane Smith",
    "comments": [
      {
        "id": 1,
        "user": "Alice Johnson",
        "comment": "Great article!",
        "timestamp": "2024-12-01T12:34:56Z"
      },
      {
        "id": 2,
        "user": "Bob Lee",
        "comment": "I learned a lot.",
        "timestamp": "2024-12-02T15:20:30Z"
      }
    ]
  }
}

この blog.json を特に考えず、DuckDB で以下のような SQL でインポートすると以下のようになります。

SELECT 
    blog_post.id as id,
    blog_post.title as title,
    blog_post.author as author,
    blog_post.comments as comments,
FROM 
    read_json_auto('blog.json', ignore_errors=true);

実行結果

ご覧の通り、JSON の中身で comments のところが入れ子になっているため、項目ごとにカラムは分けられず、1つのカラムになってしまいます。

対策としての unnest 関数

その場合、unnest 関数を使うことで、入れ子構造を外し、各項目を参照できます。

具体的には以下のような SQL で実現します。

SELECT
    id,
    title,
    author,
    comment.*,
FROM (
    SELECT 
        blog_post.id as id,
        blog_post.title as title,
        blog_post.author as author,
        unnest(blog_post.comments) as comment,
    FROM 
        read_json_auto('blog.json', ignore_errors=true)
);

実行結果

上記の通り、comments の中の配列を UNNEST して、それぞれ別のレコードにして見ることが可能です。

まとめ

今回、紹介した unnest は、DuckDB 特有ではなく、PostgreSQL にもあります。^[2]

DuckDB には、色々と機能が備わっており、この関数以外にも特徴的な機能があります。
また、面白く、有用なものがあれば、ご紹介していきたいです。

脚注

1. 前座は後編とまったくも同じ。

2. MySQL には、相当するものはなさそう。