JSONの配列内にある要素を列挙する。
成果物
配列を行にする
ターミナルで以下コマンドを実行する。
sqlite3
対話モードにて以下コマンドをコピペし実行。
select value from json_each('["A","B","C"]');
実行結果は以下。
A B C
以下コマンドでSQLite3対話モード終了。
.exit
json_each
テーブル
json_each()
関数はjson_each
という名のテーブルを返す。詳細はURL参照。value
列で値を取得できる。
.headers on .mode column select * from json_each('["A","B","C"]');
key value type atom id parent fullkey path ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 A text A 1 $[0] $ 1 B text B 2 $[1] $ 2 C text C 3 $[2] $
取得する
全行の配列要素を
create table users(id int primary key, json text); insert into users values(1, '["A", "B"]'); insert into users values(2, '["C"]'); insert into users values(3, '[]'); select json_each.value from users, json_each(users.json);
A B C
全行のオブジェクトキーが指す配列要素を
配列がオブジェクトの値であるとき。
create table users(id int primary key, json text); insert into users values(1, '{"has": ["A", "B"]}'); insert into users values(2, '{"has": ["C"]}'); insert into users values(3, '{"has": []}'); select json_each.value from users, json_each(json_extract(users.json, '$.has'));
A B C
全行のオブジェクトキーが指す多次元配列の要素を
create table users(id int primary key, json text); insert into users values(1, '{"has": [[1, 2], ["A", "B"]]}'); insert into users values(2, '{"has": [[3], ["C"]]}'); insert into users values(3, '{"has": []}'); select json_each.value from users, json_each(json_extract(users.json, '$.has[1]'));
A B C
has[0]
には数値、has[1]
には文字の配列がある。そのうち文字配列を取得する。さらに全行の文字配列を行として抽出する。
行を配列にする
上記をjson_group_array()
するだけ。
select json_group_array(value) from (select json_each.value from users, json_each(json_extract(users.json, '$.has[1]')));
["A","B","C"]
行にする
- 「配列を行にする」と同じ
select value from json_each('["A","B","C"]');
select value from json_each((select ...));
全行のオブジェクトキーが指す多次元配列の要素を重複なくソート
create table users(id int primary key, json text); insert into users values(1, '{"has": [[1, 2], ["A", "B"]]}'); insert into users values(2, '{"has": [[3], ["A", "C"]]}'); insert into users values(3, '{"has": []}'); select distinct json_each.value from users, json_each(json_extract(users.json, '$.has[1]')) order by json_each.value desc;
C B A
全行をマージするなら「重複なく」するように使うことはよくあるだろう。
全行のオブジェクトキーが指す多次元配列の要素を重複なくソートしカウントする
create table users(id int primary key, json text); insert into users values(1, '{"has": [[1, 2], ["A", "B"]]}'); insert into users values(2, '{"has": [[3], ["B", "C"]]}'); insert into users values(3, '{"has": []}'); .headers on .mode column select json_each.value as value, count(json_each.value) as count from users, json_each(json_extract(users.json, '$.has[1]')) group by json_each.value order by count desc, value asc;
value count ---------- ---------- B 2 A 1 C 1
重複のカウントも集計ではよくやる。
情報源
対象環境
- 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)