最近の主要なRDB製品ではJSONデータがサポートされています。
Yellowfinからそのようなデータを利用するにはどうすればいいのか、PostgreSQLを例にとって試してみます。
データ準備
テスト用のテーブルを作ってJSON形式のデータを投入します。
CREATE TABLE json_test (id integer, col_1 json, col_2 jsonb);
INSERT INTO json_test VALUES (
1,
'{ "title": "Book the First", "author": { "last_name": "鈴木", "first_name": "一郎" }, "age": 30 }',
'{ "publish": { "publisher": "Yellowfin出版", "pubdate": "2017-01-01"}, "category": "技術" }'
);
INSERT INTO json_test VALUES (
2,
'{ "title": "Book the Second", "author": { "last_name": "田中", "first_name": "太郎" }, "age": 43 }',
'{ "publish": { "publisher": "マグロ社", "pubdate": "2017-03-31"}, "category": "歴史" }'
);
INSERT INTO json_test VALUES (
3,
'{ "title": "Book the Third", "author": { "last_name": "山田", "first_name": "花子" }, "age": 32 }',
'{ "publish": { "publisher": "ツナ書房", "pubdate": "2017-07-22"}, "category": "文学" }'
);
PostgreSQLのJSONデータ型には、テキストのままデータを保持するjson型と、バイナリに変換して保持するjsonb型の2種類があります。処理効率の側面から、ほとんどの場合はjsonb型を用いるのが望ましいようですが、その点についてここでは論じませんので詳しくはググってみてください。
Yellowfinからの動作確認のため、上記ではjson型とjsonb型のそれぞれを作っています。
突っ込んだデータはこんな感じで見えます。
sandbox=# SELECT * FROM json_test;
id | col_1 | col_2
----+-----------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------
1 | { "title": "Book the First", "author": { "last_name": "鈴木", "first_name": "一郎" }, "age": 30 } | {"publish": {"pubdate": "2017-01-01", "publisher": "Yellowfin出版"}, "category": "技術"}
2 | { "title": "Book the Second", "author": { "last_name": "田中", "first_name": "太郎" }, "age": 43 } | {"publish": {"pubdate": "2017-03-31", "publisher": "マグロ社"}, "category": "歴史"}
3 | { "title": "Book the Third", "author": { "last_name": "山田", "first_name": "花子" }, "age": 32 } | {"publish": {"pubdate": "2017-07-22", "publisher": "ツナ書房"}, "category": "文学"}
(3 rows)
ネストされたデータを取得するには以下のようなSQLを投げます。
sandbox=# SELECT id, col_1->'author'->>'last_name' as last_name, col_2->'publish'->>'publisher' as publisher FROM json_test;
id | last_name | publisher
----+-----------+---------------
1 | 鈴木 | Yellowfin出版
2 | 田中 | マグロ社
3 | 山田 | ツナ書房
(3 rows)
Yellowfinからの利用
データソースとビューの作成
いつもの手順で、Yellowfin上にデータソースとビューを作成していきます。
ただ、ビューの「モデル」の時点ではデータの中身も見られるのですが…
「準備」へ進むとJSONデータ型のカラムは「データ型不明」となり、中身が表示されなくなってしまいます。
このままでは扱えないので、データを変換してやる必要があります。
フリーハンドSQL計算フィールドの作成
そこで使用するのがYellowfinの「計算フィールド」です。
計算フィールドは項目間の四則演算やCASE式による条件分岐のために使用することが一般的ですが、直接SQLを記述する「フリーハンドSQL」の機能も備わっています。
例えば、上記データのpublisherを取得する場合なら、以下のように設定します。
col_2->'publish'->>'publisher'
他にも、last_nameとfirst_nameを連結するなら、
concat(col_1->'author'->>'last_name', col_1->'author'->>'first_name')
pubdateをDate型に変換するには、
to_date(col_2->'publish'->>'pubdate', 'YYYY-MM-DD')
ageを数値型にしたければ、
to_number(col_1->>'age', '99')
といった具合に定義するとYellowfinで扱いやすいデータになります。
当然、レポートも普通に作れます。
今回はビューでフリーハンドSQLの計算フィールドを作成しましたが、レポートでフリーハンドSQLの計算フィールドを作成したり、あるいはレポート自体をフリーハンドSQLで作成する方法を取ることも可能です。