SQLite JSONを完全なインデックス速度で活用する方法: 生成カラムを使った高速クエリ

 (dbpro.app)
11 ポイント 投稿者 GN⁺ 2025-12-13 | 2件のコメント | WhatsAppで共有
  • SQLiteのJSON機能を活用し、元のJSONドキュメントをそのまま保存しつつ、必要なフィールドを 仮想生成カラム（virtual generated columns） として抽出してインデックス化する方法を紹介
  • json_extract 関数を使ってJSON内部のデータをカラムのように扱い、B-treeインデックスの速度でクエリを実行可能
  • 新しいクエリパターンが必要になるたびに、データ移行なしで カラムとインデックスを追加して拡張可能
  • この方法により、スキーマレスデータの柔軟性とリレーショナルデータベースの性能を同時に確保
  • SQLiteを使う開発者にとって、簡潔な構造と高い性能を提供する実用的なパターンとして強調

SQLiteとJSON機能の組み合わせ

  • SQLiteはJSON関数と演算子をサポートしており、これによってJSONデータを直接保存・操作できる
    • JSONドキュメントを1つのカラムにそのまま保存し、必要な情報だけを仮想カラムとして抽出
    • この方法は、スキーマ定義なしでデータを柔軟に扱える形態
  • DB Proチームはここ数か月、SQLiteを重点的に使用しながら、こうした機能を実務で検証してきた
    • SQLiteは適切に構成すれば、本番環境でも安定して利用可能

仮想生成カラム（Generated Columns）

  • json_extract を使ってJSON内部の特定の値を 仮想生成カラム として定義
    • このカラムは実データを保存せず、クエリ時点で計算されて即座に利用できる
    • 別途バックフィル（backfill）作業やデータ複製は不要
  • たとえばJSONデータから特定のフィールドを抽出し、カラムのように扱う構造を実装できる

インデックス追加と性能向上

  • 仮想カラムにインデックスを追加すると、JSONデータも通常のカラムと同様にB-treeインデックスの速度で検索できる
    • インデックスが追加された仮想カラムは、リレーショナルデータベースのカラムと同等の性能を提供
  • このアプローチにより、JSONデータのサイズが大きくても高速検索が可能になる

新しいクエリパターンの追加

  • 後から新しいフィールドでの検索が必要になった場合は、単に新しい仮想カラムとインデックスを追加すればよい
    • 例: user_id フィールドを抽出してインデックスを作成
    • 既存のデータ行を修正したり移行したりする必要はない
  • これにより、データ構造を変更せずに即時のクエリ拡張性を確保できる

パターンの利点と意義

  • このパターンは、スキーマレスJSON保存の柔軟性とリレーショナルDBのインデックス性能を組み合わせる
    • 初期設計段階でインデックス戦略をあらかじめ決めておく必要がない
    • 必要な時点でカラムとインデックスを追加して最適化できる
  • SQLiteを活用する開発者にとって、シンプルでありながら強力なデータ処理方式として提示されている
  • DB Proは今後もSQLiteのさまざまな機能を扱う追加記事を予告している

関連記事

2件のコメント

 
GN⁺ 2025-12-13
Hacker Newsのコメント

  • JSONドキュメントをシリアライズされたB-treeとして直接エンコードできる
    こうするとインデックス済みの速度で内部フィールドを直接参照でき、ドキュメント自体がすでにインデックス化されているのでパースが不要になる
    このフォーマットはLite³と呼ばれている。私自身が取り組んでいるプロジェクト
    GitHubリンク

    • 本当にすごい！私は Rkyv が好きだけど、Rustが必要なので小さなプロジェクトには負担が大きかった
      Lite³が lite3_val_bytes でバイナリデータをサポートしている点が特に気に入った
    • Lite³がPostgreSQLのJSONBとどう違うのか気になっていた
      JSONBは配列の長さとオフセットを一緒にエンコードして、圧縮効率と速度のバランスを取れる
      Lite³はin-place更新が可能だが、機密データが残らないよう定期的に“vacuum”が必要になる
      JSONBは再エンコードなしでは更新が難しいが、Lite³は構造をたどるだけで簡単に整理できる
      圧縮性はJSONBのほうが良さそうだが、Lite³の設計はとても巧みなアプローチだと思う
      私もASN.1コンパイラを保守しているので、こうしたシリアライズ形式には関心がある。Lite³のおかげで新しいアイデアを得られた
    • Rust版の実装があると本当にうれしい

  • SQLiteは本当に好きだが、分析用途ではDuckDBをより頻繁に使っている
    DuckDBはSQLiteのように単一ファイルを使いつつ、大規模データセットを非常に高速に処理する
    M2 MacBookで2,000万件のデータを扱ってもとても速い
    たとえば次のクエリでJSONファイルをそのまま読み込める

    SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';

    さらにJSON型カラムをロードして、Postgres風の col->>'$.key' 構文も使える

    • 最初のクエリはファイルシステム上のJSONファイルをその場でインデックス化しているのか気になる
    • DuckDBは可視化ツールpygwalkerと組み合わせると、数百万件のデータを数秒で分析できる
      ただしSQLiteと比較するのはやや不公平だ。SQLiteはシステム構築用、DuckDBは分析専用として使うのが適切
      複数プラットフォームへ配布する際はDuckDBはやや扱いづらい
    • データセットを圧縮せずに保存するのは絶対に避けるべきだ。DuckDBはさまざまな圧縮形式をサポートしている

  • JSON性能のためにGenerated Columnを使うのは一般的な方法だと思っていた
    PostgresでもJSONカラム内のキーを外部キーとして保持するためにこう使ったことがある。少し“呪われた”やり方だがうまく動いた

    • PostgresならJSONB内部のフィールドに直接インデックスを張れるのでは？
      たとえば 
      CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
      参考ブログ
    • でもこういうやり方は、結局key/valueテーブルに分離したほうがよい場合も多い
      JSONスキーマが変わるとパースやマイグレーションが失敗することがある
    • 実際にはそれほど“呪われた”方法でもない。必要な部分には正規化されたリレーショナル構造を使い、それ以外はjsonbで扱えばいい
    • STOREDではなくVIRTUALカラムを使ってもよいのか気になっていたが、この例ではVIRTUALを使っている

  • 最近Claude Codeが提案した最適化の例を通じてこの手法を知った
    SQLiteの新機能を知らずに見落としていた部分だったが、性能向上はかなり大きかった
    教訓として、慣れたツールでも定期的にドキュメントを読み直すのは良いことだと思う

    • マニュアルを読み直すのは意外と気づきが多い

  • 2023年のHNでbambaxのコメントを見てブログ記事を書くことになった
    元コメントへのリンク

  • JSONを直接投影しなくてもインデックスは作れるが、計算列(computed column) はクエリをシンプルにしてくれる
    MS-SQL 2025(v17)以前はJSONサポートが限定的だったため、この方法が必須だった

    • JSONを直接クエリせず計算列だけを使えば、非インデックスクエリを誤って書いてしまうことがない
    • 地元のDBAカンファレンスでこの機能の話を聞いたが、そのときは大きな変化とは感じなかった

  • HNで記事を開いたら自分のコメントが引用されていて、そのコメントが記事の主題になっているとは不思議な体験だった
    “ありがとう、bambax!” という文を見て思わず微笑んだ。SQLiteは本当に素晴らしいツールだ

  • 興味深いが、SQLiteの"Index On Expression"を使ってもいいのでは？
    たとえば CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
    ただしJSON path構文が少しでも違うとインデックスが使われない可能性がある。一方でVirtual Generated Columnなら常にインデックスを保証できる

    • インデックス式とビューを併用すれば、式の一致を保証できる
      例: recordliteプロジェクト
    • JSON path構文が少し違うだけでインデックスが壊れることがある
      たとえば json_extract(data, "$.type") と data -> '$.type' は別物として認識される
      つまり、WHERE句の式が変わるとインデックスが無用になる
    • シンプルで高速な解決策だ。クエリとインデックスが一致していなければならないのは常に同じ
    • 式インデックス機能はSQLite 3.9.0(2015-10-14)から追加された比較的新しい機能だ

  • 開発者には、一貫したスキーマがあるにもかかわらずデータをすべてJSON(B)カラムに入れるのは控えてほしい
    インデックス設定や制約管理が難しくなり、実運用ではオーバーヘッドも大きくなる

    • JSONカラムは、ツリー構造のようにテーブルで表しにくいデータを扱うときに真価を発揮する
      たとえばHaskell+TypeScript環境では、複雑なネスト構造をJSONとしてシリアライズするほうがずっと便利だ
    • JSON(B)は異なる型のデータを1つのコレクションに入れるときに便利だ
      例: 複数の決済プロセッサの結果を1つのテーブルに保存する場合や、クラシファイドサイトで品目ごとに異なる属性を扱う場合
      C#やJS/TSでも型検証ツール（Zod、OpenAPIなど）を併用すれば管理しやすい
    • 単純なJSONなら正規化のほうがよいが、複雑なAPIレスポンスをテーブルに分解するとJOIN地獄になる
      結局は保守性と性能のバランスの問題だ。この記事の要点は、JSONにも簡単にインデックスを張れるということ
    • センサーデータのようにツリー構造全体を一度に扱うケースでは、JSONカラムのほうがずっとシンプルだ
      読み取り性能はインデックスで十分補える
    • 完全な正規化は非効率なことも多い
      たとえば製品の価格設定システムで、顧客ごとの特殊な割引ルールをJSONで表現するとずっと柔軟になる

  • JSONの代わりにXMLを使うなら、これは90〜00年代のドキュメント指向データベース(document store) と同じモデルだ
    挿入・更新時にパースし、クエリ時にはインデックスだけを参照する構造だった
    SQLiteがこうした機能を標準で提供しているのは本当に興味深い
 
iolothebard 2025-12-14

20世紀末に… universal databaseというものがあったのだが…（今は正しく、当時は間違っていた。）