🔔 本の下書きプレビューをシェアできるようになりました
Zenn
🦆

JSONの検索にDuckDBを使おうよ with jq

2025/01/16に公開
1
JSON
jq
DuckDB
tech

JSONをフィルタしたくてjqコマンドを使う度に構文をど忘れし、jqの使い方を検索するなんてことが度々あります。 「CLIでJSONをSQLでクエリできたらな～」 なんて思いませんか。
もしかしたらDuckDBが役に立つかもしれません。

この記事では

  • DuckDBでどのようにJSONを検索できるのか
  • jqとDuckDB、どっちで書くほうが負荷が少なそうか
  • DuckDBだと簡単にできること

をご紹介します。

DuckDBとは

DuckDBはオンライン分析処理（OLAP）に特化したデータベースシステムです。SQLiteのようにアプリケーションの親プロセス内で動作させることができ、携帯性にも優れています。今回の記事でも単純なCLIツールとしてDuckDBを利用しているので、DuckDBをインストールすれば皆さんもすぐ試せます。

インストールは下記リンクからどうぞ。（公式ドキュメント）
https://duckdb.org/docs/installation/index?version=stable&environment=cli&platform=win&download_method=direct&architecture=x86_64

先にまとめ

  • NDJSONや配列形式のJSON（ex.[{"key": "value"},{"key": "value"}]）はDuckDBだけでも簡単に検索できそう
  • JSONをDBのテーブルのように扱い、オブジェクト同士をJOINできるのは便利
  • 様々なフォーマットのJSONをDuckDBだけでクエリするのは辛い（できなくはない）
  • jqを極めていない人は簡単なクエリを書いたjq + DuckDBを組み合わせて使うのがオススメ

DuckDBでどのようにJSONを検索できるのか

DuckDBでは外側が配列形式になっているJSONやNDJSONは簡単に検索可能です。この点は他の記事でも触れられており、分析目的で使ううえでは非常に便利なポイントです。

配列形式のJSONをクエリする

echo '[
  {"name": "taro","age": 12},
  {"name": "hanako","age": 10}
]' | duckdb -markdown -c "SELECT * FROM read_json('/dev/stdin') WHERE name = 'taro'"
name age
taro 12

という具合にクエリできます。RDB上のテーブルを扱うようにJSONをクエリできるので、SQLを良く書く人は便利と感じるかもしれません。

もちろん上記コマンドの-markdownを-jsonに変えることで出力をJSON形式にもできます。

NDJSONもクエリできる

ND(Newline Delimited)JSONもクエリできます。構造化ログの検索をするときはDuckDBが活躍しそうです。

echo '{"name": "taro","age": 12}
{"name": "hanako","age": 10}' \
| duckdb -markdown -c "
  SELECT *
  FROM read_json('/dev/stdin')
  WHERE name = 'taro'"

出力は同じ

また標準入力からJSONを読み込む以外にも、FROM句でread_json('https://example.com/data.json')のように書くことでURLから直接JSONを読み込むこともできます。

https://zenn.dev/shiguredo/articles/duckdb-jsonlines-log

jqとDuckDB、どっちで書くほうが負荷が少なそうか

DuckDB単体ではjqで書く場合と比べてどうしても記述量が増えてしまうケースも存在します。
ここでは

  • jqで書いた場合
  • DuckDBで書いた場合
  • DuckDBとjqを合わせて書いた場合
    のそれぞれを示します。

条件を満たすkey-valueのペアをオブジェクトから取得するパターン

jqでは以下のように書きます。

# age > 14のkeyとvalueを抽出したい
echo '{
  "taro": {"age": 13},
  "jiro": {"age": 14},
  "hanako": {"age": 15}
}' | jq -c '. | to_entries | map(select(.value.age > 14)) | from_entries'
# 出力
{"hanako":{"age":15}}

DuckDBで同等の結果を得るには以下のよう記述します。

echo '{
  "taro": {"age": 13},
  "jiro": {"age": 14},
  "hanako": {"age": 15}
}' | duckdb -markdown -c "
WITH expanded AS (
  SELECT unnest(map_entries(json)) as row
  FROM read_json('/dev/stdin', map_inference_threshold = 2)
)
SELECT row.key, row.value FROM expanded WHERE row.value.age > 14"
key value
hanako {'age': 15}

・・・結構大変ですね。unnestやmap_entriesといった関数を覚える手間と記述量が増えています。

jqとDuckDBを組み合わせて使う

というわけでMapのようなデータの持ち方をしているJSONを扱う場合はDuckDBとjqを合わせて使うといいです。

# 先ほどの実行内容と同じ出力がされる
echo '{
  "taro": {"age": 13},
  "jiro": {"age": 14},
  "hanako": {"age": 15}
}' | jq -c 'to_entries' | \
duckdb -markdown -c "
  SELECT key, value
  FROM read_json('/dev/stdin')
  WHERE value.age > 14"

このようにコマンドを組み合わせるとだいぶ記述が簡略化されるのでオススメです。

配列に特定の値を含むオブジェクトのみ抽出するパターン

jqでは以下のように書きます。

echo '{
  "posts": [
    { "title": "Post 1", "tags": ["english", "french"] },
    { "title": "Post 2", "tags": ["english"] },
    { "title": "Post 3", "tags": ["french"] }
  ]
}' | jq -c '.posts | map(select(.tags[] | startswith("f")))'
# 出力
[{"title":"Post 1","tags":["english","french"]},{"title":"Post 3","tags":["french"]}]

DuckDBの場合はこちら。

echo '{
  "posts": [
    { "title": "Post 1", "tags": ["english", "french"] },
    { "title": "Post 2", "tags": ["english"] },
    { "title": "Post 3", "tags": ["french"] }
  ]
}' | \
duckdb -markdown -c "
WITH post_table AS (
  SELECT unnest(posts, recursive:=true)
  FROM read_json('/dev/stdin')
)
SELECT *
FROM post_table
WHERE len(list_filter(post_table.tags, x -> x like 'f%')) >= 1;"
title tags
Post 1 [english, french]
Post 3 [french]

やはり少し記述量が増えてしまいます。ここでも簡単なjqを間に挟んでやるとunnestを使ったwith句分の記述を削減できます。

# 先ほどの実行内容と同じ出力がされる
echo '{
  "posts": [
    { "title": "Post 1", "tags": ["english", "french"] },
    { "title": "Post 2", "tags": ["english"] },
    { "title": "Post 3", "tags": ["french"] }
  ]
}' | jq -c '.posts' | \
duckdb -markdown -c "
SELECT *
FROM read_json('/dev/stdin') as post_table
WHERE len(list_filter(post_table.tags, x -> x like 'f%')) >= 1;"

DuckDBだと簡単にできること

ここまで来るとDuckDBでわざわざJSONを操作する必要無いな・・・と思ってしまいますが、 別々のオブジェクトをJOINするようなケースではDuckDBが真価を発揮します。

# 著者に紐づくpostを取得する
echo '{
  "posts": [
    { "title": "Post 1", "author_id": 1 },
    { "title": "Post 2", "author_id": 1 },
  ],
  "users": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ],
}' | duckdb -markdown -c "
WITH json AS MATERIALIZED (SELECT * FROM read_json('/dev/stdin')),
posts AS (SELECT unnest(json.posts, recursive:=true) FROM json),
users AS (SELECT unnest(json.users, recursive:=true) FROM json)
SELECT users.name as user_name, posts.title as post_title
FROM users JOIN posts ON users.id = posts.author_id;"
user_name post_title
Alice Post 2
Alice Post 1

個人的にはどう使い分ける？

最近はChatAIやGithubCopilotなどがあるので、そんなにjqの構文を覚える必要は無いのかなとか思いつつ、なんとなくDuckDBを使ってみたところ

  • unnest
  • map_entries
  • list_filter(x -> x)

関数あたりを覚えればDuckDBだけでJSONの操作が色々できそうなので、ローカルPCでのJSON操作はDuckDBのみで行うようになるかもしれません。

1

Discussion