やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3学習　JSON拡張（json_each 配列→行）

SQLite3学習　JSON拡張（json_each 配列→行）

SQLite3

　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

　重複のカウントも集計ではよくやる。

情報源

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで