キー・バリュー型・ドキュメント型・グラフ型DB
キー値・文書・グラフというモデルの違いを、保存する値と必要な問合せから説明します。
説明
利用者の情報と「誰が誰をフォローしているか」という関係を扱います。IDから一人を探す検索、文書の中の配列を調べる検索、関係を何段かたどる検索では、使いやすいデータの形が異なります。
SQLite上に、キーとJSON文書を組にした表、配列を展開した関係表を用意します。同じ3人のデータから直接のフォロー先と2段先を求め、プロフィールの埋込みとID参照で名前の更新箇所がどう変わるかを確かめます。
キー値・文書・グラフというモデルの違いを、保存する値と必要な問合せから説明します。
1キーにJSON文書を対応させる
キーはprofile:1のような文字列、値は一人分のJSONです。followsはフォロー先のID配列です。あおいは2と3、はるは3をフォローし、みおの配列は空です。
CREATE TABLE profiles(key TEXT PRIMARY KEY,doc TEXT NOT NULL CHECK(json_valid(doc))値が正しいJSONか検査する) STRICT;
INSERT INTO profiles VALUES('profile:1','{"id":1,"name":"あおい","follows":[2,3]}'),('profile:2','{"id":2,"name":"はる","follows":[3]}'),('profile:3','{"id":3,"name":"みお","follows":[]}');
SELECT doc FROM profiles WHERE key='profile:1'キーが一致する文書を取り出す;キーを指定すると、一人分の文書が取り出せます。キーだけで探す利用方法では、値の内部構造に立ち入らず、文書全体を一つの値として扱えます。
2文書内の値を検索
名前や配列の要素数を取り出すにはJSONの内部を調べます。SQLiteではJSONパスを指定できます。
SELECT key,json_extractパスにある値を取得する(doc,'$.name') AS name,json_array_length配列の要素数を取得する(doc,'$.follows') AS following FROM profiles ORDER BY key;あおい2人、はる1人、みお0人です。$.nameは文書直下のname、$.followsは文書直下の配列を指定します。
3配列を行に展開
文書内のフォロー先を一つずつ検索できるよう、json_eachで配列の各要素を行にします。
SELECT p.key,j.key AS position,j.value AS target FROM profiles AS p,json_each配列の要素を1行ずつ返す(p.doc,'$.follows') AS j ORDER BY p.key,j.key;profile:1から2と3、profile:2から3の計3行です。空配列には要素がないためprofile:3の行は出ません。positionは配列内の0始まりの位置です。
4文書を条件で探す
3をフォローしている人を探します。配列の中に値3が存在することを条件にします。
SELECT json_extract(p.doc,'$.name') AS follower FROM profiles AS p WHERE EXISTS条件を満たす要素が一つでもあるか調べる(SELECT 1 FROM json_each(p.doc,'$.follows') AS j WHERE j.value=3) ORDER BY p.key;あおいとはるの2人です。キーの一致検索と異なり、各文書のfollows配列に目的の値が含まれるかを調べています。
5関係を辺として保存
グラフでは利用者を頂点、フォローを向きのある辺として表します。文書の配列からsourceとtargetの組を作ります。
CREATE TABLE people(id INTEGER PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO people SELECT json_extract(doc,'$.id'),json_extract(doc,'$.name') FROM profiles;
CREATE TABLE edges(source INTEGER REFERENCES people(id),target INTEGER REFERENCES people(id),PRIMARY KEY(source,target)同じ始点と終点の組を重複させない) STRICT;
INSERT INTO edges配列の要素を辺として格納する SELECT json_extract(p.doc,'$.id'),j.value FROM profiles AS p,json_each(p.doc,'$.follows') AS j;
SELECT * FROM edges ORDER BY source,target;辺は1→2、1→3、2→3の3本です。IDの外部キーで存在する利用者を参照し、複合主キーで同じ辺の重複を防ぎます。
6直接フォローしている人と、その人のフォロー先
source=1の辺で直接の相手を調べた後、1本目の終点から出る2本目の辺を結合します。
SELECT e.target,p.name FROM edges AS e JOIN people AS p ON p.id=e.target WHERE e.source=1 ORDER BY e.target;
SELECT a.source,a.target AS via,b.target FROM edges AS a JOIN edges AS b ON b.source=a.target1本目の終点に2本目の始点をつなぐ WHERE a.source=1 ORDER BY via,b.target;直接の相手は2のはると3のみおです。2段の経路は1→2→3なので、3は直接でも2段先でも到達できます。「経路の本数」と「相手の人数」は一致するとは限りません。
7到達先の重複をまとめる
再帰CTEで到達可能なIDを集めます。UNIONで同じIDを重複させず、出発点1を最後に除きます。
WITH RECURSIVE reached(id) AS (SELECT 1 UNION同じIDを再び追加しない SELECT e.target FROM edges AS e JOIN reached AS r ON e.source=r.id到達済みの頂点から次の辺をたどる) SELECT r.id,p.name FROM reached AS r JOIN people AS p ON p.id=r.id WHERE r.id<>1 ORDER BY r.id;異なる経路で届く3も一度だけ出ます。このCTEが保存するのはIDだけです。経路や深さを結果列に加えると行の同一性が変わるため、同じ停止方法をそのまま使えるとは限りません。
8名前を埋め込んだコピー
表示用に、あおいのフォロー先の名前をJSONへ埋め込みます。その後、people表でみおの名前をみおりへ変えます。
CREATE TABLE cards(owner INTEGER PRIMARY KEY,doc TEXT NOT NULL CHECK(json_valid(doc))) STRICT;
INSERT INTO cards VALUES(1,'{"following":[{"id":2,"name":"はる"},{"id":3,"name":"みお"}]}');
UPDATE people参照先の名前を変更する SET name='みおり' WHERE id=3;
SELECT doc FROM cards WHERE owner=1;
SELECT p.id,p.name FROM edges AS e JOIN peopleIDを使って現在の名前を取得する AS p ON p.id=e.target WHERE e.source=1 ORDER BY p.id;埋込み文書には古い名前のみお、ID参照の検索には新しい名前のみおりが出ます。埋込みは関連情報をまとめて読めますが、コピーした名前の更新も管理する必要があります。
9コピーした箇所を更新
同じ人物の名前が入っている元のプロフィールと表示用カードを更新します。辺はIDを保持しているので、名前変更では書き換えません。
UPDATE profiles SET doc=json_set指定パスの値を変更したJSONを返す(doc,'$.name','みおり') WHERE key='profile:3';
UPDATE cards SET doc=json_set(doc,'$.following[1].name2番目のフォロー先の名前を指定する','みおり') WHERE owner=1;
SELECT json_extract(doc,'$.name') AS name FROM profiles WHERE key='profile:3';
SELECT doc FROM cards WHERE owner=1;
SELECT * FROM edges ORDER BY source,target;名前を持つ二つの文書を更新し、3本の辺はそのままです。この固定例では配列の2番目を指定しました。配列の順序が変わる設計なら、更新前に人物IDに対応する要素を探す処理も必要です。
まとめ
キー値ではキーから値を取り出し、文書では内部の属性や配列を検索します。
グラフでは頂点間の辺を保存し、辺の結合や再帰で到達先を求めます。
関連情報を埋め込むとまとめて読めますが、コピーした値の更新が必要です。ID参照では参照先の現在値を取得できます。
操作履歴と解説を振り返れます。
SQLite 3.54.0
1
CREATE TABLE profiles(key TEXT PRIMARY KEY,doc TEXT NOT NULL CHECK(json_valid(doc))) STRICT;
INSERT INTO profiles VALUES('profile:1','{"id":1,"name":"あおい","follows":[2,3]}'),('profile:2','{"id":2,"name":"はる","follows":[3]}'),('profile:3','{"id":3,"name":"みお","follows":[]}');
SELECT doc FROM profiles WHERE key='profile:1';╭──────────────────────────────────────────╮
│ doc │
╞══════════════════════════════════════════╡
│ {"id":1,"name":"あおい","follows":[2,3]} │
╰──────────────────────────────────────────╯2
SELECT key,json_extract(doc,'$.name') AS name,json_array_length(doc,'$.follows') AS following FROM profiles ORDER BY key;╭───────────┬────────┬───────────╮ │ key │ name │ following │ ╞═══════════╪════════╪═══════════╡ │ profile:1 │ あおい │ 2 │ │ profile:2 │ はる │ 1 │ │ profile:3 │ みお │ 0 │ ╰───────────┴────────┴───────────╯
3
SELECT p.key,j.key AS position,j.value AS target FROM profiles AS p,json_each(p.doc,'$.follows') AS j ORDER BY p.key,j.key;╭───────────┬──────────┬────────╮ │ key │ position │ target │ ╞═══════════╪══════════╪════════╡ │ profile:1 │ 0 │ 2 │ │ profile:1 │ 1 │ 3 │ │ profile:2 │ 0 │ 3 │ ╰───────────┴──────────┴────────╯
4
SELECT json_extract(p.doc,'$.name') AS follower FROM profiles AS p WHERE EXISTS(SELECT 1 FROM json_each(p.doc,'$.follows') AS j WHERE j.value=3) ORDER BY p.key;╭──────────╮ │ follower │ ╞══════════╡ │ あおい │ │ はる │ ╰──────────╯
5
CREATE TABLE people(id INTEGER PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO people SELECT json_extract(doc,'$.id'),json_extract(doc,'$.name') FROM profiles;
CREATE TABLE edges(source INTEGER REFERENCES people(id),target INTEGER REFERENCES people(id),PRIMARY KEY(source,target)) STRICT;
INSERT INTO edges SELECT json_extract(p.doc,'$.id'),j.value FROM profiles AS p,json_each(p.doc,'$.follows') AS j;
SELECT * FROM edges ORDER BY source,target;╭────────┬────────╮ │ source │ target │ ╞════════╪════════╡ │ 1 │ 2 │ │ 1 │ 3 │ │ 2 │ 3 │ ╰────────┴────────╯
6
SELECT e.target,p.name FROM edges AS e JOIN people AS p ON p.id=e.target WHERE e.source=1 ORDER BY e.target;
SELECT a.source,a.target AS via,b.target FROM edges AS a JOIN edges AS b ON b.source=a.target WHERE a.source=1 ORDER BY via,b.target;╭────────┬──────╮ │ target │ name │ ╞════════╪══════╡ │ 2 │ はる │ │ 3 │ みお │ ╰────────┴──────╯ ╭────────┬─────┬────────╮ │ source │ via │ target │ ╞════════╪═════╪════════╡ │ 1 │ 2 │ 3 │ ╰────────┴─────┴────────╯
7
WITH RECURSIVE reached(id) AS (SELECT 1 UNION SELECT e.target FROM edges AS e JOIN reached AS r ON e.source=r.id) SELECT r.id,p.name FROM reached AS r JOIN people AS p ON p.id=r.id WHERE r.id<>1 ORDER BY r.id;╭────┬──────╮ │ id │ name │ ╞════╪══════╡ │ 2 │ はる │ │ 3 │ みお │ ╰────┴──────╯
8
CREATE TABLE cards(owner INTEGER PRIMARY KEY,doc TEXT NOT NULL CHECK(json_valid(doc))) STRICT;
INSERT INTO cards VALUES(1,'{"following":[{"id":2,"name":"はる"},{"id":3,"name":"みお"}]}');
UPDATE people SET name='みおり' WHERE id=3;
SELECT doc FROM cards WHERE owner=1;
SELECT p.id,p.name FROM edges AS e JOIN people AS p ON p.id=e.target WHERE e.source=1 ORDER BY p.id;╭───────────────────────────────────────────────────────────────╮
│ doc │
╞═══════════════════════════════════════════════════════════════╡
│ {"following":[{"id":2,"name":"はる"},{"id":3,"name":"みお"}]} │
╰───────────────────────────────────────────────────────────────╯
╭────┬────────╮
│ id │ name │
╞════╪════════╡
│ 2 │ はる │
│ 3 │ みおり │
╰────┴────────╯9
UPDATE profiles SET doc=json_set(doc,'$.name','みおり') WHERE key='profile:3';
UPDATE cards SET doc=json_set(doc,'$.following[1].name','みおり') WHERE owner=1;
SELECT json_extract(doc,'$.name') AS name FROM profiles WHERE key='profile:3';
SELECT doc FROM cards WHERE owner=1;
SELECT * FROM edges ORDER BY source,target;╭────────╮
│ name │
╞════════╡
│ みおり │
╰────────╯
╭─────────────────────────────────────────────────────────────────╮
│ doc │
╞═════════════════════════════════════════════════════════════════╡
│ {"following":[{"id":2,"name":"はる"},{"id":3,"name":"みおり"}]} │
╰─────────────────────────────────────────────────────────────────╯
╭────────┬────────╮
│ source │ target │
╞════════╪════════╡
│ 1 │ 2 │
│ 1 │ 3 │
│ 2 │ 3 │
╰────────┴────────╯演習完了