日日是Oracle APEX: 対話グリッドでJSON列を扱う

2023年6月15日木曜日

対話グリッドでJSON列を扱う

対話グリッドでJSON列を持っている表を扱ってみます。

例で使用する表は以下の定義で作成します。

create table jig_clothes (
    id                             number generated by default on null as identity 
                                   constraint jig_clothes_id_pk primary key,
    name                           varchar2(80 char) not null,
    features                       blob check (features is json),
    price                          number
)
;

JSON列のfeaturesには、色(color)とサイズ(size)の指定が含まれているとします。以下がJSONの例です。

{
    color: "red",
    size: "M"
}

対話グリッドで、列NAME、COLOR、SIZE、PRICEがある表と同じ操作ができるようにします。

まずは、表JIG_CLOTHESをデータ・ソースとした対話グリッドを含むAPEXアプリケーションを作成します。

アプリケーション作成ウィザードを起動します。ホーム・ページを削除し、代わりに対話グリッドのページを追加します。

追加する対話グリッドのページ名はClothesとします。表またはビュー、編集を許可を選択し、表またはビューとして表JIG_CLOTHESを選択します。

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

アプリケーションが作成されたら、対話グリッドのページを開きます。

最初に対話グリッドのソースのタイプをSQL問合せに変更し、SQL問合せとして以下を記述します。ファンクションjson_tableを使って、JSONの属性であるcolorとsizeが検索列に現れるようにします。

	select
	c.id
	,name
	-- ,features
	,jc.color
	,jc."SIZE"
	,price
	from
	jig_clothes c
	,json_table(c.features, '$'
	columns(
	color varchar2(16) path '$.color'
	,"SIZE" varchar2(6) path '$.size'
	)
	) as jc;

view raw jig_clothes_source.sql hosted with ❤ by GitHub

以下のようなドット記法による記述も可能です。

	select
	id
	,name
	,c.features.color
	,c.features."size" "SIZE"
	,price
	from jig_clothes c;

view raw jig_clothes_dot.sql hosted with ❤ by GitHub

対話グリッドのソースとなるSQL問合せを上記のように変更すると、標準の行の自動処理(DML)による行の挿入、更新、削除は行えません。

そのため行の自動処理(DML)の設定のターゲット・タイプをPL/SQL Codeに変更し、挿入/更新/削除するPL/SQLコードに以下を記述します。

	begin
	case :APEX$ROW_STATUS
	when 'C' then
	insert into jig_clothes(
	name
	,features
	,price
	)
	values
	(
	:NAME
	,json_object(
	key 'color' value :COLOR
	,key 'size' value :SIZE
	)
	,:PRICE
	)
	returning id into :ID;
	when 'U' then
	update jig_clothes set
	name = :NAME
	,features = json_object(
	key 'color' value :COLOR
	,key 'size' value :SIZE
	)
	,price = :PRICE
	where id = :ID;
	when 'D' then
	delete from jig_clothes where id = :ID;
	end case;
	end;