SQLite3のJSON機能が便利だった3

過去記事

SQLite3のJSON機能が便利だった
SQLite3のJSON機能が便利だった2

今回の内容

　今回はテーブルの内容を抽出する時に、一部分だけをJSONとして取得する
　実はこれが恐ろしいほどに便利なのだ

　1 テーブルとデータの作成

　内容は太郎君と次郎君の好きな言語を保存したものである
　今回、テーブル内のデータにはJSONを使用しない

　　1.1 SQL文

create table users(users_id primary key,users_name text);
create table langs(langs_id primary key,langs_name text);
create table binds(users_id integer,langs_id integer,primary key(users_id,langs_id),
    FOREIGN KEY(users_id) REFERENCES users(users_id),FOREIGN KEY(langs_id) REFERENCES langs(langs_id));

insert into langs values(1,'C');
insert into langs values(2,'C++');
insert into langs values(3,'Java');
insert into langs values(4,'Perl');
insert into langs values(5,'PHP');

insert into users values(1,'太郎');
insert into users values(2,'次郎');

insert into binds values(1,1);
insert into binds values(1,2);
insert into binds values(1,5);
insert into binds values(2,3);
insert into binds values(2,4);
insert into binds values(2,5)

　2 普通に結合して抽出してみる

　　2.1 SQL文

select * from users natural join langs natural join binds

　　2.2 抽出結果

users_id	users_name	langs_id	langs_name
1	太郎	1	C
1	太郎	2	C++
1	太郎	5	PHP
2	次郎	3	Java
2	次郎	4	Perl
2	次郎	5	PHP

　　2.3 欲しいデータはそれじゃ無い

　このデータを希望の形で抽出するとすれば、一つの太郎君に対して複数の言語という組み合わせのデータが欲しいのだ
　決して増殖した太郎君と次郎君が欲しいわけでは無い

　欲しいのはこの形はこれだ！

名前	好きな言語
太郎	C,C++,PHP
次郎	Java,Perl,PHP

　3 あっさり実現可能だった

　　3.1 SQL文

select users_name,lang_names from users natural join
    (select users_id,json_group_array(langs_name) as lang_names from binds natural join langs group by users_id)

　　3.2 抽出結果

users_name	lang_names
太郎	["C","C++","PHP"]
次郎	["Java","Perl","PHP"]

　　3.3 これはヤバいよ

　グループ化とjson_group_arrayを組み合わせたら、あっさり欲しい情報をJSONの配列にまとめることが出来た
　あまりに便利すぎておしっこをチビリそうである

まとめ

　json_group_arrayやjson_group_objectはこんなに便利な機能なのに、Google先生に聞いても日本語の解説は皆無だった
　英語で見てもろくに情報が載っていない
　ぶっちゃけ「いいね」とかは押さなくて良いので、この内容を目立つところに広めて欲しい
　使う人が使えば、とんでもなく便利なのだ

　現在Node.jsでSPA(SinglePageApplication)用のフレームワークを開発中である
　その中で今回のシリーズ記事の内容を所々で使っている
　おかげでデータの入出力がかなり楽になった

　おそらく他のDBにも同等の機能があるのだろう
　我こそはと思う人は、ぜひそういう記事をまとめ欲しい