- Nippondanji氏に怒られても仕方ない、配列型とJSON型の使い方 (in PostgreSQL)
http://kwatch.houkagoteatime.net/blog/2015/12/10/pg-array-and-json-types/
というようなデータ構造を生成したいのだがPostgreSQLでは実現が難しい、という内容でした。
その時は、「ふむふむ」と聞いていたのですが、最近、JSONの機能に興味を持っていたこともあり、「どうにか望むデータ構造をどうにか実現できないだろうか？」ということが気になっていました。その後、いくつか調査してみた結果、わりと簡単に実現できることが分かりましたので、本エントリではその方法を紹介します。
実現したいことは、
- 複数のテーブルをJOINして1:Nの構造を生成すること
- Nの部分にはJSONを使うこと
- JSONのキー名を任意に設定できること
■テーブルとデータを作成する
まず、テーブルとデータを準備します。
create table product (
"id" integer primary key,
"name" text not null
);
insert into product values (101, 'T-Shirt'),
(102, 'Sweater');
create table coloring (
"product_id" integer not null,
"color" text not null,
"code" text not null
);
insert into coloring values (101, 'White', 'W'),
(101, 'Black', 'B'),
(102, 'White', 'WH'),
(102, 'Ivory', 'IV'),
(102, 'Gray', 'GR');
この状態でテーブルのデータを見ると、以下のような状態になっています。
testdb=> select * from product;
id | name
-----+---------
101 | T-Shirt
102 | Sweater
(2 rows)
testdb=> select * from coloring;
product_id | color | code
------------+-------+------
101 | White | W
101 | Black | B
102 | White | WH
102 | Ivory | IV
102 | Gray | GR
(5 rows)
この状態から目指す出力を一発で取得できるクエリを書いてみます。■テーブルをJOINする
まず、productとcoloringをJOINし、一つのリレーションにまとめます。
select p.id, p.name, c.color from product p left join coloring c on p.id = c.product_id;上記クエリを実行すると以下のような出力が得られます。
testdb=> select p.id, p.name, c.color testdb-> from product p left join coloring c testdb-> on p.id = c.product_id; id | name | color -----+---------+------- 101 | T-Shirt | White 101 | T-Shirt | Black 102 | Sweater | White 102 | Sweater | Ivory 102 | Sweater | Gray (5 rows)この状態から、望む形式でデータを取り出すにはどうすればいいかを見ていきます。
■json_build_object() 関数
PostgreSQL 9.4から導入された json_build_object() 関数を使うと、複数のカラムを一つのJSONオブジェクトにまとめることができるようになります。
- 9.15. JSON関数と演算子 https://www.postgresql.jp/document/9.4/html/functions-json.html
Kuwata氏の資料にある通り、行コンストラクタを使ってJSONに変換すると、JSONオブジェクトのキーが自動的に f1, f2, ... となってしまうのですが、この json_build_object() では奇数番目の引数にキー名、偶数番目の引数に値を設定することによって、任意のキー名と値を持つJSONオブジェクトを生成することができます。
testdb=> select json_build_object('mykey1', 'foo', 'mykey2', 'bar')::jsonb;
json_build_object
------------------------------------
{"mykey1": "foo", "mykey2": "bar"}
(1 row)
testdb=>
■複数のカラムを json_build_object() 関数で1つのJSONオブジェクトに変換する
というわけで、json_build_object() 関数を使って先ほどのクエリの color カラムと code カラムをまとめてJSON化し、キー名に "color" と "code" と設定してみます。
testdb=> select p.id, p.name, json_build_object('color', c.color, 'code', c.code) colors
from product p left join coloring c
on p.id = c.product_id;
id | name | colors
-----+---------+------------------------------------
101 | T-Shirt | {"color" : "White", "code" : "W"}
101 | T-Shirt | {"color" : "Black", "code" : "B"}
102 | Sweater | {"color" : "White", "code" : "WH"}
102 | Sweater | {"color" : "Ivory", "code" : "IV"}
102 | Sweater | {"color" : "Gray", "code" : "GR"}
(5 rows)
■複数レコードのJSONオブジェクトを json_agg() 関数で集約する
ここまで来れば、あとは json_agg() 関数を使って、複数レコードのJSONを集約させるだけです。
testdb=> select p.id, p.name, json_agg(json_build_object('color', c.color, 'code', c.code)) colors
from product p left join coloring c
on p.id = c.product_id group by 1,2;
id | name | colors
-----+---------+-------------------------------------------------------------------------------------------------------------
101 | T-Shirt | [{"color" : "White", "code" : "W"}, {"color" : "Black", "code" : "B"}]
102 | Sweater | [{"color" : "White", "code" : "WH"}, {"color" : "Ivory", "code" : "IV"}, {"color" : "Gray", "code" : "GR"}]
(2 rows)
というわけで、1件の id, name に対して、複数の colors を保持するデータ構造を実現することができました。
■まとめ
今回、「どうやったらできるのだろうか？」という疑問から出発しましたが、ちょうど json_build_object() という関数が 9.4 から提供されたことによって、目的のデータ構造を比較的容易に作ることができることが分かりました。
JSONは、PostgreSQLに導入されてある程度時間が経っていますが、アプリケーション側から便利に使う機能などはまだまだ拡充中です。 ぜひ、最新バージョンのPostgreSQLを試してみていただき、「今、何がどこまでできるのか？」に挑戦してみていただければと思います。
そして、「もっとこういう機能が欲しい」というニーズがある場合には、積極的に共有していただければと思います。もしかすると、誰かが将来のPostgreSQLに機能追加してくれるかもしれません。
では。
0 件のコメント:
コメントを投稿