SQLite の JSONB と ->> 演算子で JSON を速く扱う

SQLite に JSON を入れて使うとき、TEXT に生の JSON 文字列を格納していると、クエリのたびにパースが走る。SQLite 3.45.0（2024-01-15） で入った JSONB を使うと、パース済みのバイナリ表現でそのまま保存・処理でき、json_extract などの呼び出しが速くなる。ここでは sqlite3 CLI で実際に動かしながら勘所を押さえる。

まずバージョンを確認

JSONB は 3.45 以降。-> / ->> 演算子自体は 3.38 から使える。

sqlite3 --version
# 3.45.0 以上であることを確認する

テキスト JSON と JSONB の違い

json() はテキスト JSON を正規化して返し、jsonb() は同じ内容をバイナリ表現（BLOB）で返す。テーブルには JSONB で持たせておくと、読み出しのたびのパースを避けられる。

sqlite3 :memory: <<'SQL'
-- 生テキストのJSONと、JSONB(BLOB)の型を比べる
SELECT typeof(json('{"a":1}'))  AS as_text;   -- text
SELECT typeof(jsonb('{"a":1}')) AS as_jsonb;  -- blob
SQL

-> と ->> の使い分け

• -> は JSON を返す（値が文字列なら引用符つきの JSON テキスト）
• ->> は SQL の値を返す（文字列なら引用符なしの TEXT、数値なら数値）

比較や WHERE、表示にそのまま使うなら ->> が扱いやすい。

sqlite3 :memory: <<'SQL'
CREATE TABLE events(id INTEGER PRIMARY KEY, body BLOB);
-- 値は jsonb() でバイナリとして格納する
INSERT INTO events(body) VALUES
 (jsonb('{"user":"alice","action":"login","score":42}')),
 (jsonb('{"user":"bob","action":"logout","score":7}'));

-- ->> は SQL の値（引用符なし）を返すので WHERE や表示にそのまま使える
SELECT id,
       body ->> 'user'   AS user,
       body ->> 'action' AS action,
       body ->> 'score'  AS score
FROM events
WHERE body ->> 'action' = 'login';
SQL

出力は次のようになる。

1|alice|login|42

-> と ->> の差はこう確認できる。

sqlite3 :memory: <<'SQL'
SELECT jsonb('{"user":"alice"}') -> 'user'  AS arrow;    -- "alice"（JSON, 引用符あり）
SELECT jsonb('{"user":"alice"}') ->> 'user' AS darrow;   -- alice（TEXT, 引用符なし）
SQL

生成列 + インデックスで検索を速くする

JSON の特定フィールドで頻繁に絞り込むなら、生成列（generated column）に切り出してインデックスを張る。これで WHERE body ->> 'user' = ... 相当の検索がインデックスに乗る。

sqlite3 :memory: <<'SQL'
CREATE TABLE events(
  id   INTEGER PRIMARY KEY,
  body BLOB,
  user TEXT GENERATED ALWAYS AS (body ->> 'user') VIRTUAL
);
CREATE INDEX idx_events_user ON events(user);

INSERT INTO events(body) VALUES
 (jsonb('{"user":"alice","action":"login"}')),
 (jsonb('{"user":"bob","action":"logout"}'));

-- インデックスが使われることを確認
EXPLAIN QUERY PLAN
SELECT id FROM events WHERE user = 'alice';
SQL

SEARCH events USING INDEX idx_events_user (user=?) と出れば、生成列インデックスで検索できている。VIRTUAL はディスクに列を保存せず参照時に計算する指定で、インデックス自体は作られるため検索は速いままだ。

更新は jsonb_set で

部分更新も JSONB のまま行える。

sqlite3 :memory: <<'SQL'
CREATE TABLE t(id INTEGER PRIMARY KEY, body BLOB);
INSERT INTO t(body) VALUES (jsonb('{"user":"alice","score":42}'));

-- score を 100 に更新（結果も JSONB のまま保持）
UPDATE t SET body = jsonb_set(body, '$.score', 100) WHERE id = 1;

-- 確認のためテキストに戻して表示
SELECT json(body) FROM t;   -- {"user":"alice","score":100}
SQL

まとめ

• 格納は jsonb() でバイナリ表現にしておくと、読み出しのたびのパースを避けられる
• 取り出し・比較・表示には ->>（SQL値）、JSON のまま扱いたいときは ->
• よく絞り込むフィールドは生成列 + インデックスにして検索を速くする
• 部分更新は jsonb_set で JSONB のまま行える

アプリ側にJSONのまま渡したいAPIレスポンスを、SQLite内で軽く絞り込み・整形してから返す、といった用途にちょうど噛み合う。まずは手元の sqlite3 で上のスニペットをそのまま流して挙動を確かめてほしい。