SQLiteでJSONを保存・クエリする：例とベストプラクティス

JSON（JavaScript Object Notation）は、データを保存・交換するためのフォーマットです。軽量で、テキストベースで、人間が読みやすく、機械が解析・生成しやすい形式です。SQLiteでは、JSONをいくつかの異なる方法で保存・クエリできます。

JSON1拡張機能を使用する

SQLiteでJSONを保存する方法の1つは、JSONのエンコードとデコードを行う関数を提供するJSON1拡張機能を使用することです。JSON1拡張機能を使用するには、まず次のコマンドを実行して有効にする必要があります：

SELECT load_extension('/path/to/json1/extension');

拡張機能が有効になったら、次のSQL文を使用して、JSON型の列を持つテーブルを作成できます：

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

JSONデータの挿入

このテーブルにJSON値を挿入するには、JSON1拡張機能が提供するjson_encode関数を次のように使用できます：

INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}));

JSONデータのクエリ

このテーブルをクエリするには、JSONパスを指定してJSON列から値を抽出できるjson_extract関数を使用できます。たとえば、id 1のユーザーのメールアドレスを取得するには、次のクエリを使用できます：

SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;

BLOBにJSONを保存する

SQLiteでJSONを保存・クエリするもう1つの方法は、BLOB型を使用することです。この場合、次のようにBLOB列を持つテーブルを作成できます：

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

BLOB列へのJSONの挿入

このテーブルにJSON値を挿入するには、json_encode関数を使用して値を文字列としてエンコードし、次にhex関数を使用してBLOBに変換します：

INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));

BLOB列内のJSONのクエリ

このテーブルをクエリするには、JSON1拡張機能が提供するJSON_EXTRACT関数を使用できますが、unhex関数を使用してBLOB値を文字列に変換する必要があります：

SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;

まとめ

結論として、SQLiteはJSON値を保存・クエリする2つの方法を提供しています：JSON1拡張機能を使用する方法とBLOB型を使用する方法です。どちらのアプローチにも独自の利点と欠点があり、どちらを選択するかは特定のニーズによって異なります。

JSONデータをSQL INSERT文に変換する必要がある場合は、無料のJSON to SQLコンバーターツールをご覧ください。