SQLite3学習 JSON拡張（json_tree オブジェクト配列→行）
よくあるJSON形式を解析する。
成果物
オブジェクト配列
[ {"id":1,"name":"yamada"}, {"id":2,"name":"suzuki"}, {"id":3,"name":"tanaka"} ]
よくCSVなどの表データをJSONにするとき、上記のような形になる。
- キー名はすべて同じ
- 値は必ず数値かテキストであり、決して配列やオブジェクトにならない
こういうの、なんて呼ぶの？ ここでは適当に「オブジェクト配列」と呼ぶ。
今回はこれを表(行)に展開してみる。
配列を行に展開する
ターミナルで以下コマンドを実行する。
sqlite3
対話モードにて以下コマンドをコピペし実行。
create table users(id int primary key, json text); insert into users values(111, '[{"id": 1, "name": "yamada"},{"id": 2, "name": "suzuki"}]'); insert into users values(222, '[{"id": 3, "name": "tanaka"}]'); insert into users values(333, '[]'); .headers on .mode column select * from users, json_each(users.json);
id json key value type atom id parent fullkey path
---------- --------------------------------------------------------- ---------- ------------------------ ---------- ---------- ---------- ---------- ---------- ----------
111 [{"id": 1, "name": "yamada"},{"id": 2, "name": "suzuki"}] 0 {"id":1,"name":"yamada"} object 1 $[0] $
111 [{"id": 1, "name": "yamada"},{"id": 2, "name": "suzuki"}] 1 {"id":2,"name":"suzuki"} object 6 $[1] $
222 [{"id": 3, "name": "tanaka"}] 0 {"id":3,"name":"tanaka"} object 1 $[0] $
json_each()で配列の要素を行に展開した。
必要な列のみ取得する
select id, value from (select * from users, json_each(users.json));
id value
---------- ------------------------
111 {"id":1,"name":"yamada"}
111 {"id":2,"name":"suzuki"}
222 {"id":3,"name":"tanaka"}
users.idとusers.json（オブジェクト配列を要素ごとに行にした）を取得できた。
列名を変える
select id as users_id, value as object from (select * from users, json_each(users.json));
インデントを入れると以下。
select id as users_id, value as object from ( select * from users, json_each(users.json) );
users_id object
---------- ------------------------
111 {"id":1,"name":"yamada"}
111 {"id":2,"name":"suzuki"}
222 {"id":3,"name":"tanaka"}
オブジェクトをキー単位で行に分ける
select * from ( select id as users_id, value as object from ( select * from users, json_each(users.json) ) ), json_tree(object);
users_id object key value type atom id parent fullkey path
---------- ------------------------ ---------- ------------------------ ---------- ---------- ---------- ---------- ---------- ----------
111 {"id":1,"name":"yamada"} {"id":1,"name":"yamada"} object 0 $ $
111 {"id":1,"name":"yamada"} id 1 integer 1 2 0 $.id $
111 {"id":1,"name":"yamada"} name yamada text yamada 4 0 $.name $
111 {"id":2,"name":"suzuki"} {"id":2,"name":"suzuki"} object 0 $ $
111 {"id":2,"name":"suzuki"} id 2 integer 2 2 0 $.id $
111 {"id":2,"name":"suzuki"} name suzuki text suzuki 4 0 $.name $
222 {"id":3,"name":"tanaka"} {"id":3,"name":"tanaka"} object 0 $ $
222 {"id":3,"name":"tanaka"} id 3 integer 3 2 0 $.id $
222 {"id":3,"name":"tanaka"} name tanaka text tanaka 4 0 $.name $
オブジェクトの要素のみ取り出す（親・子の要素は排除する）
select * from ( select id as users_id, value as object from ( select * from users, json_each(users.json) ) ), json_tree(object) where atom!='';
users_id object key value type atom id parent fullkey path
---------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
111 {"id":1,"name":"yamada"} id 1 integer 1 2 0 $.id $
111 {"id":1,"name":"yamada"} name yamada text yamada 4 0 $.name $
111 {"id":2,"name":"suzuki"} id 2 integer 2 2 0 $.id $
111 {"id":2,"name":"suzuki"} name suzuki text suzuki 4 0 $.name $
222 {"id":3,"name":"tanaka"} id 3 integer 3 2 0 $.id $
222 {"id":3,"name":"tanaka"} name tanaka text tanaka 4 0 $.name $
これだと、keyがどのオブジェクトに属するものか判別できない。objects_idが欲しい。配列内でのインデックスがあればuser_idと組合せることで特定できる。
配列インデックスをobjects_idとして取得する
配列インデックスは最初のjson_each()で取れる表のうちkey列で得られる。
select * from ( select id as users_id, key as objects_id, value as object from ( select * from users, json_each(users.json) ) ), json_tree(object) where atom!='';
users_id objects_id object key value type atom id parent fullkey path
---------- ---------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
111 0 {"id":1,"name":"yamada"} id 1 integer 1 2 0 $.id $
111 0 {"id":1,"name":"yamada"} name yamada text yamada 4 0 $.name $
111 1 {"id":2,"name":"suzuki"} id 2 integer 2 2 0 $.id $
111 1 {"id":2,"name":"suzuki"} name suzuki text suzuki 4 0 $.name $
222 0 {"id":3,"name":"tanaka"} id 3 integer 3 2 0 $.id $
222 0 {"id":3,"name":"tanaka"} name tanaka text tanaka 4 0 $.name $
users_idとobjects_idを複合キーとすることで、そのnameがどこに属するものか特定できる。たとえば以下。1個目のkey=nameなレコードは、users.id=111の行にある。そのうちusers.json列のオブジェクト配列[]内におけるインデックス0の位置にある無名オブジェクトが持つnameキーである。
| 取得行 | users_id | objects_id | value |
|---|---|---|---|
1個目name |
111 |
0 |
yamada |
2個目name |
111 |
1 |
suzuki |
3個目name |
222 |
0 |
tanaka |
有用な列のみ取得する
select users_id, objects_id, key, value, type from ( select id as users_id, key as objects_id, value as object from ( select * from users, json_each(users.json) ) ), json_tree(object) where atom!='';
users_id objects_id key value type ---------- ---------- ---------- ---------- ---------- 111 0 id 1 integer 111 0 name yamada text 111 1 id 2 integer 111 1 name suzuki text 222 0 id 3 integer 222 0 name tanaka text
これで、指定した行の、指定したインデックスにあるオブジェクトのうち、指定したキーの値を取得できるテーブルが得られた！
あとは条件を与えて絞り込むだけ。users.idを得て一致させたり、users.json[]のインデックスを指定したり、user.json[0].nameのようにキーを指定する。それをSQLで書けばいい。副問合せ地獄になるが。
name=suzukiの行とオブジェクト位置を取得する
select * from ( select users_id, objects_id, key, value, type from ( select id as users_id, key as objects_id, value as object from ( select * from users, json_each(users.json) ) ), json_tree(object) where atom!='' ) where key='name' and value='suzuki';
users_id objects_id key value type ---------- ---------- ---------- ---------- ---------- 111 1 name suzuki text
でもこれ、suzukiさんは他の行にも存在するかもしれない。それを行にある他の列で絞り込みたいこともあるだろう。
行の条件で絞りこみたい場合
テーブルにclass列を追加する。
create table users(id int primary key, class text, json text); insert into users values(111, 'A', '[{"id": 1, "name": "yamada"},{"id": 2, "name": "suzuki"}]'); insert into users values(222, 'A', '[{"id": 3, "name": "tanaka"}]'); insert into users values(333, 'A', '[]'); insert into users values(444, 'B', '[{"id": 1, "name": "suzuki"}]'); .headers on .mode column
これで直前に提示したselect文を実行すると以下。
users_id objects_id key value type ---------- ---------- ---------- ---------- ---------- 111 1 name suzuki text 444 0 name suzuki text
別の行にあるsuzukiがヒットした。これをjson列の値でなく、行がもつ他の列で絞り込みたい。今回はclass列で絞りたい。
行の条件で絞込
select * from ( select users_id, objects_id, key, value, type from ( select id as users_id, key as objects_id, value as object from ( select * from users, json_each(users.json) where users.class='A' ) ), json_tree(object) where atom!='' ) where key='name' and value='suzuki';
最初の副問合せのところにwhere句を追加した。
users_id objects_id key value type ---------- ---------- ---------- ---------- ---------- 111 1 name suzuki text
JSONのキーで絞り、さらに行がもつ他の列でも絞り込めた。
これだけ複雑な条件を指示できれば、何でも取得できるだろう。
所感
SQLite3でもJSONを取得できることがわかった。ただし副問合せ地獄になる。JSONを扱うためのもっと簡単な方法はないのか？
JsonQuery
XMLからデータを取得するクエリ構文にXPath,XQueryというのがあった。これをマネてJSONからデータを取得するクエリ構文にJson-Pathというのがあった。しかしそれはイマイチであったため流行らず。
- https://codeday.me/jp/qa/20181212/20153.html
- http://oboe2uran.hatenablog.com/entry/2018/05/06/131634
それぞれのプログラミング言語で実装されたJSONパーサを使うのが現状らしい。
CSSセレクタ
CSSセレクタがいかに優秀であるかがわかる。だが、ループ処理はできない。たとえばJQueryではCSSセレクタで配列を取得したあと、JavaScriptでfor文などを使って繰り返し処理をする。CSSセレクタというクエリ構文だけではループ処理できない。
SQLite3 JSON拡張関数
それにひきかえ、SQLite3はSQL文だけで完結できる。他のプログラミング言語は不要。すごい！ ただし副問合せ地獄になる……。
そもそも、json_extract(json, 'JSON-PATH')のように、第2引数にJsonPathっぽいテキストを書いている。だからSQL文だけだとは言えない。でも実行エンジンはSQLite3だけでできるから、他の言語のSDK不要というのは嬉しい。
有用だけど流行らなそう
SQLite3のJSON関数も、きっと流行らないだろう。なにせ超面倒だから。可読性も悪い。せめて副問合せをモジュール化できたらいいのだが……。
ただ、バージョン3.29.0ではコンパイルオプションをつけずともデフォルトで有効化されている。自分でコンパイルすればすぐ使えるだろう。でも、そのコンパイルが面倒。かといってOSのパッケージマネージャにあるのは古くてJSON拡張機能が効かない。うん、やっぱ流行らないわ。
情報源
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習をはじめよう
- SQLite3学習 SQLiteについて
- SQLite3学習 SQLiteの適切な用途
- SQLite3学習 SQLiteの特徴
- SQLite3学習 SQLiteのクセ
- SQLite3学習 データ型とアフィニティ
- SQLite3学習 演算子の一覧
- SQLite3学習 よくある質問
- SQLite3学習 SQLiteダウンロード＆コンパイル
- SQLite3学習 Tclで操作する
- SQLite3学習 ビルドオプション動作確認（SQLITE_ALLOW_URI_AUTHORITY）
- SQLite3学習 面白そうなコンパイルオプション
- SQLite3学習 SQLiteの拡張について
- SQLite3学習 JSON拡張
- SQLite3学習 JSON拡張（json_extract）
- SQLite3学習 JSON拡張（json_each）
- SQLite3学習 JSON拡張（json_tree オブジェクト→行）
- SQLite3学習 JSON拡張（json_tree オブジェクトツリー→行）
