JSONの検索にDuckDBを使おうよ with jq
JSONをフィルタしたくてjqコマンドを使う度に構文をど忘れし、jqの使い方を検索するなんてことが度々あります。 「CLIでJSONをSQLでクエリできたらな~」 なんて思いませんか。
もしかしたらDuckDBが役に立つかもしれません。
この記事では
- DuckDBでどのようにJSONを検索できるのか
- jqとDuckDB、どっちで書くほうが負荷が少なそうか
- DuckDBだと簡単にできること
をご紹介します。
DuckDBとは
DuckDBはオンライン分析処理(OLAP)に特化したデータベースシステムです。SQLiteのようにアプリケーションの親プロセス内で動作させることができ、携帯性にも優れています。今回の記事でも単純なCLIツールとしてDuckDBを利用しているので、DuckDBをインストールすれば皆さんもすぐ試せます。
インストールは下記リンクからどうぞ。(公式ドキュメント)
先にまとめ
- 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を読み込むこともできます。
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のみで行うようになるかもしれません。
Discussion