SQLite3 で JSON 拡張 コトハジメ

sqlite3_json.md

SQLite3 で JSON 拡張 コトハジメ

注意

• SQL 素人が書いてます

テーブルとデータ

CREATE TABLE authn_log (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

    json JSON NOT NULL,

    created_unixtime INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    created_at TIMESTAMP NOT NULL DEFAULT (DATETIME('now'))
);
INSERT INTO authn_log(json) VALUES('{"github_id": 1, "abc": 10}');
INSERT INTO authn_log(json) VALUES('{"github_id": 1, "abc": 10}');
INSERT INTO authn_log(json) VALUES('{"github_id": 2, "abc": 1}');
INSERT INTO authn_log(json) VALUES('{"github_id": 3, "abc": 20}');
INSERT INTO authn_log(json) VALUES('{"github_id": 3, "abc": 20}');

実行例

json の github_id の値で group by して abc の値を合計する

SELECT json_extract(json, '$.github_id'), sum(json_extract(json, '$.abc')) FROM authn_log GROUP BY json_extract(json, '$.github_id');

sqlite> SELECT json_extract(json, '$.github_id'), sum(json_extract(json, '$.abc')) FROM authn_log GROUP BY json_extract(json, '$.github_id');
1|20
2|1
3|40

資料

• The JSON1 Extension
  • とりあえずこれをじっくり読み込むのがいい

以下は雰囲気を掴むために読むくらいで良い。

• Sqlite 3.9 の Json1を試した - Qiita
• SQLite3のJSON機能が便利だった - Qiita
• SQLite3のJSON機能が便利だった2 - Qiita
• SQLite3のJSON機能が便利だった3 - Qiita