日日是Oracle APEX: JSONが保存された列を対象としたレポートとフォームを作る

2023年6月2日金曜日

JSONが保存された列を対象としたレポートとフォームを作る

Oracle APEXは表の定義情報を参照することにより、レポートの列やフォームのアイテムを生成します。列にJSONが保存されている場合は、その内容からレポートの列やフォームのアイテムを生成するといったことはできません。

なんとなくJSON_DATAGUIDEとDBMS_JSON.CREATE_VIEWを使うことで、JSON列を対象としたレポートとフォームの作成が容易になるように思ったので実装してみました。

最初にデータを準備します。

サンプル・データセットのEMP/DEPTに含まれる表EMPを、JSONとして保存する表EMPJを作成します。

create table empj (
    "_ID" number primary key,
    document blob check (document is json)
);

表EMPの主キーは列EMPNOですが、この表EMPJに列EMPNOを作成すると、列DOCUMENTのJSONのデータにもEMPNOが含まれていることから、列名が競合します。そのため、競合しない名前"_ID"という列を作成し、主キーとしています。

表EMPよりJSONデータを生成し、表EMPJに入れ替えます。

insert into empj select empno, json_object(*) from emp;

列DOCUMENTのJSONデータガイドを取得し、それを元にビューEMP_JVを作成します。

作成したビューEMP_JVより、表EMPJの内容を確認します。

select * from emp_jv;

JSONなのでHIREDATEがISO8601形式になっていますが、JSON形式で保存されているデータが表形式で確認できています。

作成されたビューEMP_JVの列の型を確認してみます。

select column_name, data_type, data_length from all_tab_cols where table_name = 'EMP_JV';

COLUMN_NAME	DATA_TYPE	DATA_LENGTH
JOB	VARCHAR2	16
MGR	VARCHAR2	4
SAL	NUMBER	22
COMM	VARCHAR2	2
EMPNO	NUMBER	22
ENAME	VARCHAR2	8
DEPTNO	NUMBER	22
HIREDATE	VARCHAR2	32
_ID	NUMBER	22

この定義は、以下のJSONデータガイドが元になっています。

select json_dataguide(document,dbms_json.format_hierarchical) from empj;

JSONデータガイドの出力です。

{
  "type": "object",
  "o:length": 1,
  "properties": {
    "JOB": {
      "type": "string",
      "o:length": 16,
      "o:preferred_column_name": "JOB"
    },
    "MGR": {
      "type": "string",
      "o:length": 4,
      "o:preferred_column_name": "MGR"
    },
    "SAL": {
      "type": "number",
      "o:length": 4,
      "o:preferred_column_name": "SAL"
    },
    "COMM": {
      "type": "string",
      "o:length": 2,
      "o:preferred_column_name": "COMM"
    },
    "EMPNO": {
      "type": "number",
      "o:length": 4,
      "o:preferred_column_name": "EMPNO"
    },
    "ENAME": {
      "type": "string",
      "o:length": 8,
      "o:preferred_column_name": "ENAME"
    },
    "DEPTNO": {
      "type": "number",
      "o:length": 2,
      "o:preferred_column_name": "DEPTNO"
    },
    "HIREDATE": {
      "type": "string",
      "o:length": 32,
      "o:preferred_column_name": "HIREDATE"
    }
  }
}

ビューEMP_JVができたので、これを元にAPEXのアプリケーションを作成します。

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

名前はJSONフォームとします。