SQLiteのJSON操作入門 | json_extract・json_each徹底解説

SQLiteにJSON型はない。でも、それで困らない

SQLiteには専用のJSON型カラムが存在しません。JSONは普通のTEXTカラムに保存し、組み込みの関数群——まとめてJSON1拡張と呼ばれるもの——がパース・検索・更新を担当します。JSON1は最近のSQLiteには標準で同梱されているので、別途インストールする必要はありません。

イメージとしては、ドキュメントはテキストとして保存し、中身を覗くときは関数を使う、という感じです。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT
);

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

2 行のデータが入って、それぞれ JSON ドキュメントをただのテキストカラムに保持している状態です。次は、このドキュメントの中身に手を伸ばす方法を見ていきましょう。

json_extract と ->> でフィールドを取り出す

json_extract(column, path) は JSON ドキュメントから値を取り出す関数です。パスは $（ルート）から始まり、オブジェクトのキーには .field、配列のインデックスには [i] を使います。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

json_extract(data, '$.name') をあちこちに書いていると、正直うんざりしてきます。そこで SQLite には便利な演算子が2つ用意されています。

• -> は JSON 形式の値を返します（文字列はクォート付きで返ってきます）。
• ->> は SQL の値を返します（テキストや数値で、クォートなし）。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json は "Ada"(JSON のまま)で返ってきますが、name_text は Ada になります。比較や表示に使いたいときは ->>、結果をさらに別の JSON 関数に渡すなら -> を使う、と覚えておきましょう。

JSON フィールドで絞り込む

値を取り出せるようになれば、あとは絞り込みも自由自在です。式は他のカラムと同じように WHERE 句にそのまま書けます。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

これでも動きますが、テーブルがある程度の規模になると遅くなります。条件を評価するために毎回すべての行をパースする必要があるからです。これは後ほどインデックスで解消します。

JSONを組み立てる: json_objectとjson_array

逆方向、つまりクエリの中でJSONを構築することもできます。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...) でオブジェクト、json_array(v1, v2, ...) で配列を組み立てられます。SQL の中で API レスポンスをそのまま組み立てるときに便利で、ネストもそのまま書けます:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

JSONを更新する：json_set, json_insert, json_replace

JSONドキュメントを更新して新しい結果を返す、よく似た3つの関数があります。

• json_set(doc, path, value) — 指定したパスに値をセット。無ければ作成し、既にあれば上書きします。
• json_insert(doc, path, value) — パスが存在しない場合のみ挿入します。
• json_replace(doc, path, value) — パスが既に存在する場合のみ更新します。

これらの関数は元のドキュメントを直接書き換えるわけではなく、新しいドキュメントを返します。そのため、通常は UPDATE 文と組み合わせてテーブルに書き戻します。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

json_set は1回の呼び出しで複数のパス/値ペアを受け取れる点も覚えておくと便利です。キーを削除したい場合は json_remove(doc, path) を使います。

json_each で配列を行に展開する

json_each はテーブル値関数で、JSON配列（やオブジェクト）を渡すと要素ごとに1行ずつ返してくれます。これを使えば、素のSQLでは書きづらい「adminタグを持つユーザーを探す」といった処理も、普通のJOINとして自然に書けるようになります。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

users テーブルの各行は、その tags 配列の要素と結合されます。json_each は key、value、type、fullkey といった便利なカラムを返してくれます。兄弟関数の json_tree はドキュメント全体を再帰的にたどり、ネストされたノードまで残らず展開してくれるので、構造が不明な JSON を検索したいときに重宝します。

JSON フィールドにインデックスを張る

先ほどの WHERE data ->> '$.active' = 1 というクエリは動きはしますが、SQLite は条件を評価するために毎回すべての行をパースする必要があります。よく検索するフィールドについては、式インデックス（expression index）を作っておきましょう。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

インデックス側とクエリ側では、まったく同じ式を使う必要があります。インデックスに json_extract(data, '$.email') を書いて、クエリで data ->> '$.email' を使う、といった書き方ではマッチせず、せっかくのインデックスが使われないまま放置されてしまいます。どちらかの書き方に統一しましょう。

頻繁に検索するフィールドであれば、生成カラム（generated column）にしてしまった方が見通しがよくなります。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

email はクエリを書く側からすれば普通のカラムにしか見えませんが、裏ではJSONと自動的に同期し続けてくれます。

JSONのバリデーション

json_valid(text) は、渡したテキストがJSONとしてパースできれば1、できなければ0を返します。これを CHECK 制約と組み合わせれば、不正なデータを書き込み時点で弾けます。

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

1つ目のINSERTは通るのに、2つ目は制約違反で落ちます。このCHECK制約がないと、壊れたJSONがテーブルにこっそり紛れ込み、数か月後に json_extract を呼んだ瞬間に突然エラーで爆発する、なんて事態になりがちです。

JSONとJSONBの違い

SQLite 3.45以降では、JSONBと呼ばれるバイナリ形式が使えるようになりました。中身は同じJSONですが、あらかじめパース済みのコンパクトなバイナリとして保持されるため、関数を呼ぶたびに毎回パースし直す必要がありません。jsonb_* 系の関数(jsonb_extract、jsonb_set、jsonb_object など)はテキストではなくJSONBを返し、JSONB型のカラムも通常のJSONと同じ演算子で問い合わせできます。

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

ダンプ時に人間が読みやすく、目視で確認しやすい形にしたいなら、プレーンな JSON（テキスト）を使うのが無難です。テーブルが大きく、頻繁にクエリされ、プロファイリングでパースのオーバーヘッドが実際に問題になっているときに初めて JSONB を検討しましょう。デフォルトで切り替えるのはおすすめしません。デバッグ中はプレーン JSON の読みやすさが本当に効いてきます。

SQLite で JSON 保存が向いているケース

JSON カラムが活きるのは、こんなときです。

• 行ごとにデータの形が変わる場合（イベントのペイロード、監査ログ、外部 Webhook など）。
• 外部 API のレスポンスをそのままキャッシュしておきたい場合。
• ほぼクエリされず、フィルタにもまず使わないフィールド。

逆に、向かないのはこんな場面です。

• スキーマ設計をサボるための逃げ道として JSON を使うとき。全行に同じフィールドが並ぶなら、それはカラムにすべきです。
• ある値で頻繁にフィルタや JOIN をかけたいとき。インデックス付きの普通のカラムのほうが、JSON パスでの取り出しよりも常に速いです。
• 外部キーを張りたくなるような関係。JSON にはリレーショナルな整合性がありません。

ベストなのは両者の併用です。クエリや制約に関わるフィールドはスカラーのカラムとして切り出し、その横に可変データの受け皿として JSON カラムを置く、という形ですね。

次は全文検索（FTS5）

JSON は保存側の柔軟性をくれるツールでした。次のページでは SQLite の全文検索エンジン FTS5 を取り上げます。LIKE ではとても届かない、ランキングやハイライトまで備えた本格的なテキスト検索が手に入ります。