BLOBに格納したJSON配列をマルチバリュー・ファセットとして使用する

 ファセット検索のマルチバリュー・ファセットのタイプとしてJSON配列を選択できます。しかし、JSONをBLOBとして格納すると文字列ではないため、そのままでは対話モード・レポートやフォームで扱うことができません。

BLOBに格納したJSON配列をマルチバリュー・ファセットとして使用するために作成した、いくつかの処理について紹介します。

実装サンプルとなるアプリケーションを作成するために、クイックSQLの以下のモデルより、表ARTICLESを作成します。

articles
    title
    tags json

レビューおよび実行までを行うと、SQLスクリプトの編集画面が開きます。クイックSQLで列の型としてJSONを指定した際のデフォルトはCLOBなので、これをBLOBに変更します。

その後、実行をクリックして表ARTICLESを作成します。

表ARTICLESが作成されたら、アプリケーションの作成を実行します。

アプリケーション作成ウィザードが起動します。デフォルトで作成されているページを削除し、ファセット検索のページを追加します。

アプリケーションの名前とページ名の双方を記事検索としています。

追加するファセット検索のページのフォームを含めるにチェックを入れます。

表示形式はレポート、表としてARTICLESを選択します。

以上でアプリケーションの作成を実行します。

作成されたアプリケーションを実行すると、以下の画面が開きます。

作成をクリックし、データを作成します。フォームが開くとTagsの指定としてファイルを選択するようになっています。列の型がBLOBなので、ページ・アイテムのタイプがファイル参照になっています。

ページ・デザイナでフォームのページを開き、画面上はカンマ区切りの文字列として複数のタグを入力できるように変更します。

フォームを初期する際にBLOBに保存されているタグのJSON配列を、カンマ区切りの文字列に変更するプロセスを作成します。

レンダリング前のヘッダーの前、初期化フォームArticleの直下に新規にプロセスを作成します。

識別の名前はJSON配列をカンマ区切り文字列にするとします。タイプとしてコードを実行を選びます。編集可能リージョンは- 選択 -のまま変更しません。これは未選択を意味します。タグを保存するページ・アイテムP2_TAGSとして現在は表ARTICLESの列TAGSが割り当てられていますが、この後に割り当てを変更しP2_TAGSにプロセスの処理結果を保存します。そのため、割り当てるべき編集可能リージョンはありません。

ソースのPL/SQLコードとして以下を記述します。変数宣言が不要な場合はbegin/endで囲む必要はありません。

select listagg(t.tag,',') within group (order by t.tag) into :P2_TAGS
from
    articles a
    , json_table(a.tags, '$[*]' 
        columns (tag varchar2(40) path '$')
    ) t
where a.id = :P2_ID;

サーバー側の条件として、タイプにアイテムはNULLではないを選択し、アイテムにP2_IDを指定します。

以上で、データの読み出し時にJSON配列からカンマ区切りの文字列へ変更するプロセスが作成されました。

次にカンマ区切りの文字列をJSON配列に変換して、表ARTICLESに保存するプロセスを作成します。

左ペインでプロセス・ビューを開き、プロセス・フォームArticleの直下に新規にプロセスを作成します。

識別の名前はカンマ区切り文字列をJSON配列で保存とします。ソースのPL/SQLコードとして以下を記述します。

update articles 
set tags = 
(
    select json_arrayagg(column_value returning blob)
    from apex_string.split(:P2_TAGS,',')
)
where id = :P2_ID;

サーバー側の条件として、タイプにリクエストは値に含まれる、値としてCREATE SAVEを指定します。作成または変更の適用のボタンを押した時のみ、処理が実行されます。

ページ・アイテムP2_TAGSの識別のタイプをテキスト・フィールドに変更し、ソースのフォーム・リージョンを- 選択 -に変更（つまり未選択）します。タイプはNULL、セッション・ステートの保持はリクエストごと(メモリーのみ)を指定します。

 以上でタグの入出力については、対応ができました。

アプリケーションを実行し、データを入力してみます。

データを保存すると、レポートにダウンロードのリンクが表示されます。列の型がBLOBの場合のデフォルトになります。

ダウンロードのリンクの代わりに文字列を表示させ、また、マルチバリュー・ファセットも作成します。

リージョンArticlesのソースのSQL問合せを以下に変更します。手抜きですが、JSON配列をそのまま文字列として表示します。ファセットのソースとしてJSON配列を使うため、列TAGSはそのまま残します。文字列として表示する列はTAGS_DISPとして新たに追加します。

select 
    id
    , title
    , tags
    , json_query(tags format json,'$') tags_disp
from articles

列TAGSは非表示列に変更します。

列TAGS_DISPのヘッダーをTags DispからTagsへ変更します。

この時点でレポートを表示させると、ダウンロード・リンクの代わりにJSON配列がそのままの形式で表示されていることが確認できます。

最後にファセットを作成します。

識別の名前をP1_TAGS、タイプとしてチェック・ボックス・グループを選択します。ラベルはTagsとします。LOVのタイプとして個別値、ソースのデータベース列はTAGS、データ型としてVARCHAR2を選択します。実際の型はBLOBなのですが、なぜかVARCHAR2にしないと正常に動作しません。

複数の値のタイプとしてJSON配列を選択し、フィルタの結合にはAND(論理積)を選択しました。

編集フォームが閉じたときにファセットも更新されるよう、動的アクションを追加します。

動的アクション・ビューを開き、動的アクションのレポートの編集 - ダイアログのクローズにTRUEアクションを作成します。

識別のアクションとしてリフレッシュを選択します。影響を受ける要素の選択タイプとしてリージョン、リージョンに検索（ファセットのリージョン）を選択します。

以上でアプリケーションは完成です。

実行すると記事の最初にあるGIF動画のように動作します。

今回作成したアプリケーションを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/multivalue-facet-json-blob.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

PGQLの2層モードで生成されるSQLをAPEXのレポートに組み込む

 Oracle Databaseに作成したプロパティ・グラフはPGQL（プロパティ・グラフ問合せ言語）を使って操作しますが、２層モードで扱う範囲であればPGQLはSQLに変換されて、Oracle Databaseで実行されます。

そこで、SQLclを使ってPGQLから変換されたSQLを取り出し、Oracle APEXのレポートのソースにしてみます。

PGQLやプロパティ・グラフについて勉強をするための準備作業なので、それらについては特に触れません。@ryotayamanakaさんのこちらの記事などを手掛かりにして、勉強を始めるのも良いでしょう。

この記事では、SQLを取り出す準備作業とレポートの生成のために実施した作業を記述します。作業にはAlways FreeのAutonomous Databaseのインスタンスを使用します。

今回の作業を行う上で参考にした記事は以下です。

Executing Property Graph (PGQL) queries in SQLcl

https://blogs.oracle.com/oraclespatial/post/executing-property-graph-pgql-queries-in-sqlcl

それでは、SQLclでPGQLを実行するための準備から始めます。

Oracle APEXのワークスペースとしてAPEXDEVが作成済みとします。

この作業を行うAutonomous Databaseのデータベース・アクションに接続します。

管理のデータベース・ユーザーを開きます。

APEXのワークスペース・スキーマに対して、RESTの有効化とグラフ有効化を実施します。Oracle APEX 22.1以降で作成したワークスペースでは、WKSP_という接頭辞がワークスペース・スキーマに付加されています。

グラフとWebアクセスの両方をONに変更します。また、SQLclで接続する際に使用するパスワードを設定します。REST別名に接頭辞wksp_が付加されている場合は、それを削除しておきます。

APEXのワークスペース・スキーマの名称が変更された理由のひとつは、データを保持しているスキーマ名の露出を避けることであるため、REST別名もワークスペース・スキーマ名と異なる名前にします。

変更の適用をクリックします。

データベース・ユーザーの設定は以上で完了です。

Autonomous Databaseの詳細画面を開きます。

SQLclでの接続に使用するウォレットをダウンロードします。DB接続をクリックします。

ウォレット・タイプとしてインスタンス・ウォレットを選択し、ウォレットのダウンロードを実行します。

パスワードを入力し、ダウンロードを実行します。今回のウォレットの用途では、パスワードが必要になることはありません。

ウォレットがWallet_インスタンス名.zipというファイル名で、手元のPCにダウンロードされます。

最新のSQLclをダウンロードするリンクは以下です。

https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip

また、SQLclの実行に必要なJavaは以下よりダウンロードできます。

https://www.oracle.com/java/technologies/downloads/

SQLclのインストールはZIPファイルを展開してパスを通すだけなので、今回の説明からは割愛します。

SQLcl（Java含む）とウォレットの準備ができたので、SQLclのPGQLプラグインをダウンロードし、利用できるようにします。

Oracle Graph Server and Client Downloadsのページを開きます。

https://www.oracle.com/database/technologies/spatialandgraph/property-graph-features/graph-server-and-client/graph-server-and-client-downloads.html

この中のPGQL Plugin for SQLclを開きます。

Oracle Graph PGQL Plugin for SQLclをダウンロードします。

V1021724-01.zip（バージョンによってファイル名は異なります）がダウンロードされます。このファイルを解凍すると、oracle-graph-sqlcl-plugin-22.3.0.zip（バージョンによってファイル名は異なります）が取り出されます。

このファイルをSQLclのホーム・ディレクトリのlib/ext以下に展開します。

unzip -d <SQLclを展開したディレクトリ>/sqlcl/lib/ext oracle-graph-sqlcl-plugin-22.3.0.zip

以上でSQLclにPGQLプラグインが組み込まれました。

SQLclを起動しAPEXのワークスペース・スキーマに接続します。

sql -cloudconfig Wallet_<インスタンス名>.zip wksp_<ワークスペース名>/<パスワード>@<インスタンス名>_low

以下のDDLを実行し、プロパティ・グラフの元となるデータを保持する表SQLCL_BANK_ACCTSとSQLCL_BANK_TXNSを作成します。

この後にOracle APEXのデータ・ジェネレータを使用してテスト・データを生成するために、表SQLCL_BANK_TXNSに主キー列TXN_IDを追加した上で、各種制約を追加しています。

create table sqlcl_bank_accts (
    acct_id                        number not null constraint sqlcl_bank_accts_acct_id_pk primary key,
    name                           varchar2(64) not null
)
;
create table sqlcl_bank_txns (
    txn_id                         number not null constraint sqlcl_bank_txns_txn_id_pk primary key,
    from_acct_id                   number not null
                                   constraint sqlcl_bank_txns_from_acct_i_fk
                                   references sqlcl_bank_accts on delete cascade,
    to_acct_id                     number not null
                                   constraint sqlcl_bank_txns_to_acct_id_fk
                                   references sqlcl_bank_accts on delete cascade,
    description                    varchar2(128) not null,
    amount                         number(38,2) not null
)
;
create index sqlcl_bank_txns_i1 on sqlcl_bank_txns (from_acct_id);
create index sqlcl_bank_txns_i52 on sqlcl_bank_txns (to_acct_id);

SQL> create table sqlcl_bank_accts (

  2      acct_id                        number not null constraint sqlcl_bank_accts_acct_id_pk primary key,

  3      name                           varchar2(64) not null

  4  )

  5* ;

Table SQLCL_BANK_ACCTSは作成されました。

SQL> create table sqlcl_bank_txns (

  2      txn_id                         number not null constraint sqlcl_bank_txns_txn_id_pk primary key,

  3      from_acct_id                   number not null

  4                                     constraint sqlcl_bank_txns_from_acct_i_fk

  5                                     references sqlcl_bank_accts on delete cascade,

  6      to_acct_id                     number not null

  7                                     constraint sqlcl_bank_txns_to_acct_id_fk

  8                                     references sqlcl_bank_accts on delete cascade,

  9      description                    varchar2(128) not null,

 10      amount                         number(38,2) not null

 11  )

 12* ;

Table SQLCL_BANK_TXNSは作成されました。

SQL> create index sqlcl_bank_txns_i1 on sqlcl_bank_txns (from_acct_id);

Index SQLCL_BANK_TXNS_I1は作成されました。

SQL> create index sqlcl_bank_txns_i52 on sqlcl_bank_txns (to_acct_id);

Index SQLCL_BANK_TXNS_I52は作成されました。

SQL> 

SQLワークショップのユーティリティより、データ・ジェネレータを起動します。

ブループリントの作成をクリックします。

既存の表の使用を選択します。

リストから選択を選び、ブループリント名としてbanktransfersを入力します。

SQLCL_BANK_ACCTSにチェックを入れ、行を1000とします。テスト・データを1000行挿入します。また、SQLCL_BANK_TXNSにチェックを入れ、行を10000とします。テスト・データを10000行挿入します。

ブループリントが作成されます。

参照制約が適切に設定されていると、SQLCL_BANK_ACCTS、SQLCL_BANK_TXNSの順に配置されます。もし逆になっていると、現在のバージョンのデータ・ジェネレータでは順番を変更する方法がありません。そのような場合は表ごとにブループリントを分けて作成し、最初にSQLCL_BANK_ACCTSにテスト・データを挿入してください。

SQLCL_BANK_ACCTSの列ACCT_IDを選択し、データ・ソースを順序、開始を１、増分を１とします。SQLCL_BANK_ACCTSには1000行のデータを挿入する予定なので、ACCT_IDは1から1000の値が設定されます。

列NAMEのデータ・ソースは組込み、組込みとしてperson.last_commna_first（姓、名）を選択します。

SQLCL_BANK_TXNSの列TXN_IDもデータ・ソースは順序、開始は１、増分は１とします。

列FROM_ACCT_IDのデータ・ソースは式、式として以下を入力します。1から1000までの整数を入力します。

ceil(dbms_random.value(1,1000))

列TO_ACCT_IDはFROM_ACCT_IDと同じ設定を行います。

列DESCRIPTIONには任意の文字列が生成されます。最大長が128なのは少し長いので64に変更します。

列AMOUNTのデータ・ソースは組込み、組込みとしてnumber.random（乱数）を選択します。最小値は1000、最大値は10000000、値の精度として0を設定します。一千円から一千万円の間の金額になります。

以上で保存をクリックします。続けてデータの生成をクリックします。

アクションとしてデータベースへの挿入を選択し、テスト・データを直接データベースに挿入します。

データの挿入をクリックします。

エラーが表示されていますが、いくつかのテスト・データの挿入で発生したもので全体が失敗したわけではありません。このまま取消をクリックしてデータ・ジェネレータを終了します。

以上で表SQLCL_BANK_ACCTS、SQLCL_BANK_TXNSにテスト・データが投入されました。

SQLclより投入されたデータを確認します。表SQLCL_BANK_ACCTSには1000行挿入されています。

SQL> select count(*) from sqlcl_bank_accts;

   COUNT(*) 

___________ 

       1000 

SQL> 

表SQLCL_BANK_TXNSには9979行挿入されています。いくつかのテスト・データの挿入に失敗しています。

SQL> select count(*) from sqlcl_bank_txns;

   COUNT(*) 

___________ 

       9979 

SQL> 

テスト・データより、送金元と送金先が同じ行を削除します。

SQL> delete from sqlcl_bank_txns where from_acct_id = to_acct_id;

8行削除されました。

SQL> commit;

コミットが完了しました。

SQL> 

以上でテスト・データの準備も完了しました。

この表を元にプロパティ・グラフSQLCL_BANK_GRAPHを作成します。英語の元記事で実行しているPGQLは以下になります。最初にPGQLのモードに切り替えます。

pgql auto on;

create property graph sqlcl_bank_graph
vertex tables (
    SQLCL_BANK_ACCTS
    Key (ACCT_ID)
    Label ACCOUNT
    Properties (ACCT_ID)
)
edge tables (
    SQLCL_BANK_TXNS
    key(TXN_ID)
    Source key (FROM_ACCT_ID) references SQLCL_BANK_ACCTS
    Destination key (TO_ACCT_ID) references SQLCL_BANK_ACCTS
    Label TRANSFER
    Properties (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT)
);
commit;

SQL> pgql auto on

PGQL Auto enabled for schema=[null], graph=[null], execute=[true], translate=[false]

PGQL> create property graph sqlcl_bank_graph

  2  vertex tables (

  3      SQLCL_BANK_ACCTS

  4      Key (ACCT_ID)

  5      Label ACCOUNT

  6      Properties (ACCT_ID)

  7  )

  8  edge tables (

  9      SQLCL_BANK_TXNS

 10      key(TXN_ID)

 11      Source key (FROM_ACCT_ID) references SQLCL_BANK_ACCTS

 12      Destination key (TO_ACCT_ID) references SQLCL_BANK_ACCTS

 13      Label TRANSFER

 14      Properties (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT)

 15* );

Graph created

PGQL> commit;

コミットが完了しました。

PGQL> 

プロパティ・グラフSQLCL_BANK_GRAPHが作成されました。

元記事ではいくつかのPGQLの実行を行なっていますが、本記事では以下の２行のPGQLについて、APEXのレポートのソースにしてみます。

select v.ACCT_ID, count(*) as NumDeposits match (m)-[e]->(v) group by v.ACCT_ID order by NumDeposits desc limit 5;
select s.ACCT_ID, e.AMOUNT, d.ACCT_ID MATCH (s)-[e]->(d) WHERE s.ACCT_ID=1002 or s.ACCT_ID=1001;

レポートを実装するOracle APEXのアプリケーションを作成します。

アプリケーション作成ウィザードを起動します。

アプリケーションの名前をPGQLレポートとします。

それ以外は変更せず、アプリケーションの作成を実行します。

レポートを作成するアプリケーションの準備ができました。

PGQLを実行し、SQLを取り出します。デフォルトのグラフをsqlcl_bank_graphにして、実際にデータベースで実行されるSQLが表示されるよう、translateモードに変更します。

pgql auto on graph sqlcl_bank_graph translate

PGQL> pgql auto on graph sqlcl_bank_graph translate

PGQL Auto enabled for schema=[null], graph=[SQLCL_BANK_GRAPH], execute=[true], translate=[true]

PGQL> 

最初のPGQLを実行します。赤字が生成されたSQLになります。

PGQL> select v.ACCT_ID, count(*) as NumDeposits match (m)-[e]->(v) group by v.ACCT_ID order by NumDeposits desc limit 5;

SELECT * FROM(SELECT T0$1.T AS "v.ACCT_ID$T",

T0$1.V AS "v.ACCT_ID$V",

T0$1.VN AS "v.ACCT_ID$VN",

T0$1.VT AS "v.ACCT_ID$VT",

7 AS "NumDeposits$T",

to_nchar(COUNT(*),'TM9','NLS_Numeric_Characters=''.,''') AS "NumDeposits$V",

COUNT(*) AS "NumDeposits$VN",

to_timestamp_tz(null) AS "NumDeposits$VT"

FROM (SELECT DISTINCT EID, SVID, DVID,EL FROM "WKSP_APEXDEV".SQLCL_BANK_GRAPHGE$) T0$0,

"WKSP_APEXDEV".SQLCL_BANK_GRAPHVT$ T0$1

WHERE T0$1.K=n'ACCT_ID' AND

T0$0.DVID=T0$1.VID

GROUP BY T0$1.T,

T0$1.V,

T0$1.VN,

T0$1.VT

ORDER BY COUNT(*) DESC NULLS FIRST)

WHERE ROWNUM <= 5;

   v.ACCT_ID    NumDeposits 

____________ ______________ 

156          20             

649          20             

430          20             

644          19             

227          18             

PGQL> 

生成されたSELECT文をソースとしたレポートのページを作成します。

ページの作成をクリックします。

クラシック・レポートを選択します。

ページの名前はPGQL1、データ・ソースのソース・タイプとしてSQL問合せを選択し、SQL SELECT文を入力に、SQLclで生成させたSELECT文を入力します。

以上で、ページの作成をクリックします。

ページが作成されるので、実行します。

PGQLの実行と同じ結果が得られています。表示される列の名称や個数に違いはありますが、これらはAPEX側で調整可能です。

生成されたSQLに表示する行数である５という数値がハード・コードされています。APEXのレポートを使う場合、この値をページ・アイテムに置き換えることができます。

例としてページ・アイテムP2_ROWSを作成し、表示行数を指定できるようにしてみます。ソースとなるSELECT文を以下に変更します。

ページ・アイテムP2_ROWSのタイプを数値フィールドとし、動的アクションを作成します。動的アクションでは、P2_ROWSの値に変更があったときに、クラシック・レポートのリージョンPGQL1をリフレッシュします。

リージョンPGQL1のソースのSQL問合せを変更し、送信するページ・アイテムにP2_ROWSを指定します。

以上の変更を行いページを実行すると、以下のような動作になります。

続いて、２番目のPGQLをレポートにしてみます。

PGQL> select s.ACCT_ID, e.AMOUNT, d.ACCT_ID MATCH (s)-[e]->(d) WHERE s.ACCT_ID=1002 or s.ACCT_ID=1001;

SELECT T0$1.T AS "s.ACCT_ID$T",

T0$1.V AS "s.ACCT_ID$V",

T0$1.VN AS "s.ACCT_ID$VN",

T0$1.VT AS "s.ACCT_ID$VT",

T0$0.T AS "e.AMOUNT$T",

T0$0.V AS "e.AMOUNT$V",

T0$0.VN AS "e.AMOUNT$VN",

T0$0.VT AS "e.AMOUNT$VT",

T0$2.T AS "d.ACCT_ID$T",

T0$2.V AS "d.ACCT_ID$V",

T0$2.VN AS "d.ACCT_ID$VN",

T0$2.VT AS "d.ACCT_ID$VT"

FROM "WKSP_APEXDEV".SQLCL_BANK_GRAPHGE$ T0$0,

"WKSP_APEXDEV".SQLCL_BANK_GRAPHVT$ T0$1,

"WKSP_APEXDEV".SQLCL_BANK_GRAPHVT$ T0$2

WHERE T0$0.K=n'AMOUNT' AND

T0$1.K=n'ACCT_ID' AND

T0$2.K=n'ACCT_ID' AND

T0$0.SVID=T0$1.VID AND

T0$0.DVID=T0$2.VID AND

(T0$1.VN = 1002 OR T0$1.VN = 1001);

行が選択されていません

PGQL> 

s.ACCT_IDに設定している1001、1002のどちらも今回のテスト・データには含まれていないため、選択されるデータはありません。

生成されたSELECT文をレポートのソースとして、先ほどと同様にクラシック・レポートのページを作成します。

今回も同様に、1001、1002とハード・コードされている部分をページ・アイテムP3_ACCT_IDSに置き換えます。

ページ・アイテムP3_ACCT_IDSを作成します。識別のタイプはテキスト・フィールド、ラベルはACCT_IDsとします。ページ・アイテムP3_ACCT_IDSの値に変更があったときに、リージョンPGQL2をリフレッシュする動的アクションを作成します。

リージョンPGQL2のソースのSQL問合せを置き換えます。送信するページ・アイテムとしてP3_ACCT_IDSを指定します。

以上の変更を行いページを実行すると、以下のような動作になります。

今回の作業は以上で完了です。

実際のところ、以下のPGQLのSELECT文は

select v.ACCT_ID, count(*) as NumDeposits match (m)-[e]->(v) group by v.ACCT_ID order by NumDeposits desc limit 5;

以下のSELECT文と同じです。

select to_acct_id, count(*) from sqlcl_bank_txns group by to_acct_id order by 2 desc fetch first 5 rows only;

以下のPGQLのSELECT文は

select s.ACCT_ID, e.AMOUNT, d.ACCT_ID MATCH (s)-[e]->(d) WHERE s.ACCT_ID=1002 or s.ACCT_ID=1001;

以下のSELECT文と同じです。

select * from sqlcl_bank_txns where from_acct_id in (1001,1002);

結局のところSQLでもできるのだから（２層モードの場合 - ３層ではSQLでできないこともあります）、プロパティ・グラフとして扱う意味は何だろう？と考えるかもしれません。

Oracle APEXでアプリケーションを作成する上で、一番難しいのはAPEXの部分ではなくリレーショナル・データとしてのモデリングだといえます。プロパティ・グラフで保存することがで自然であれば、無理にリレーショナル形式に落とし込まずにグラフとして保存してもよいでしょう。SQLclがPGQLをSQLに変換してくれるのであれば、グラフからリレーショナルへの形式変換は自動的に行われ、レポートを作成するときに頭を悩ませる必要は無くなります。

簡単なアプリケーションですが、今回作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/pgql-reports.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完