SQLite JSON at full index speed using generated columns

Japanese Translation:

SQLiteは、仮想生成列と組み込みの

json_extract

GENERATED ALWAYS AS (json_extract(...)) VIRTUAL

JSONパスにインデックスを作成できることを発見しました。これにより、単一のテキストフィールドにデータを保持しながら即時検索速度が得られます。この手法はスキーマレスなJSONストアを高速クエリエンジンへ変換し、従来一般的だったコストの高いマイグレーションやETLステップを排除します。実際に libSQL や Turso などのライブラリが SQLite を使用しており、DB Pro はローカルでそれを活用しています。Jay はこの試験中に発見した他の SQLite 機能を扱う短いブログシリーズを計画しており、追加インデックステクニックや実際の事例も含む予定です。軽量データベースに依存する開発者・企業にとって、このパターンを採用すれば JSON の柔軟性を保ちつつリレーショナル性能が得られ、スキーマ変更が簡素化されクエリ速度も向上します。

DB ProではSQLiteを心から愛しています。
本当に好きでない人はほとんど見つけられません。確かに制約はあります――「弱点」ではなく制限です。しかし、適切にデプロイして注意深くチューニングすれば、SQLiteを本番環境で問題なく使うことができます。

近年、SQLite は再び注目を集めています。libSQL や Turso へのフォークはもちろん、PocketBase のような人気バックエンドフレームワークの根幹にも使われており、再び勢いを取り戻しています。

私たちも好きです。実際に DB Pro 自体のローカルデータベースとして SQLite を使用しており、当社のユースケースには他に優れた代替手段がないと感じています。

過去三か月間、SQLite を積極的に利用してきました。その結果、以前は知らなかった多くのことを学びました。
そこで、私たちが発見した SQLite のクールで興味深い機能やニュアンスを紹介する短いブログシリーズを書こうと思います。これがその第一弾です。

你知道 SQLite に JSON 関数と演算子があることをご存知でしたか？

最近まで知らなかったのですが、Hacker News のコメントで気づきました。
*“bambax” が言っていた内容を理解するまで何度も読み返し、自分でも試してみる必要がありました。私たちのブログにはブラウザ内に埋め込まれた SQLite コンポーネントがありますので、実際に動く例を作ってみました（主に自分用ですが）。

1. JSON ドキュメントをそのまま保存する

JSON 列を持つシンプルなテーブルを作成します。

CREATE TABLE posts (
    id   INTEGER PRIMARY KEY,
    data JSON NOT NULL
);

JSON ドキュメントは到着したままの形で自然に格納されます。スキーマ変更は不要です。

2. 仮想生成列を追加する

ここがマジックの始まりです。仮想生成列を加えましょう。生成列は値を必要なときに計算し、実際にはデータを保存しません。

ALTER TABLE posts ADD COLUMN title TEXT GENERATED ALWAYS AS (json_extract(data, '$.title')) VIRTUAL;

書き込みは発生せず、バックフィリングもありません。クエリ時にオン・ザ・フライで計算されます。

3. 完全なパフォーマンスを得るためにインデックスを作る

次に、これらの仮想列を高速化するインデックスを作ります。

CREATE INDEX idx_posts_title ON posts(title);

JSON が通常のリレーショナルカラムと同じようにフルインデックスで扱えるようになります。

4. 最高速でクエリできる

例として:

SELECT * FROM posts WHERE title = 'Hello World';

このクエリは非常に高速です。

5. 後から別のパターンが必要になったら？

JSON の構造が変わっても、既存行を触れずに新しい生成列とインデックスを追加できます。例えば

user_id

で検索したい場合は：

ALTER TABLE posts ADD COLUMN user_id TEXT GENERATED ALWAYS AS (json_extract(data, '$.user.id')) VIRTUAL;
CREATE INDEX idx_posts_user_id ON posts(user_id);

これだけで、マイグレーションやスキーマの書き換えなしに最適化できます。

このパターンが強力な理由

この手法は SQLite で JSON を扱う考え方を一変させました。スキーマレスデータの柔軟性と、リレーショナルデータベースの性能・操作性を兼ね備えつつ、早期に制約を課したり、選択肢を限定したりすることなく利用できます。

感謝します、bambax さん！

SQLite の隠れたスーパーパワーはまだまだあります。これが最初の一例です。

読んでいただきありがとうございます！
— Jay