DBMS_JSON

100 DBMS_JSON

DBMS_JSONパッケージは、データ・ガイド操作のためのインタフェースを提供します。

この章のトピックは、次のとおりです:

100.1 DBMS_JSONの概要

DBMS_JSONパッケージは、Oracle Databaseに格納されているJavaScript Object Notation (JSON)データを操作するためのサブプログラムを提供します。

100.2 DBMS_JSONのセキュリティ・モデル

PUBLICには、DBMS_JSONパッケージに対するEXECUTE権限が付与されます。サブプログラムは、実行者権限で実行されます。

100.3 DBMS_JSONの定数

DBMS_JSONパッケージは、これらの定数を使用してJSONスキーマ・タイプおよびデータ・ガイドのフォーマット・オプションを定義します。

表100-1 JSONデータ・ガイド・フォーマットに定義されているDBMS_JSON定数

名前	値	説明
FORMAT_FLAT	2	フラット・フォーマットを表示します。
FORMAT_HIERARCHICAL	1	階層フォーマットを表示します。
PRETTY	1	読みやすくするために、適切なインデントを使用します。

表100-2 JSONスキーマ・タイプのDBMS_JSON定数

名前	タイプ	値	説明
`TYPE_ARRAY`	NUMBER(2)	6	JSON配列
`TYPE_BOOLEAN`	NUMBER(2)	2	JSONブール
`TYPE_GEOJSON`	NUMBER(2)	7	地理的なJSONデータ
`TYPE_NULL`	NUMBER(2)	1	`JSON NULL`値
`TYPE_NUMBER`	NUMBER(2)	3	JSON数値
`TYPE_OBJECT`	NUMBER(2)	5	JSONオブジェクト
`TYPE_STRING`	NUMBER(2)	4	JSON文字列
`TYPE_BINARY`	NUMBER(2)	17	Oracle拡張JSONタイプ・バイナリ
`TYPE_DATE`	NUMBER(2)	13	Oracle拡張JSONタイプ日付
`TYPE_DOUBLE`	NUMBER(2)	12	Oracle拡張JSONタイプ倍精度浮動小数点数
`TYPE_DSINTERVAL`	NUMBER(2)	16	Oracle拡張JSONタイプ日時時間隔
`TYPE_FLOAT`	NUMBER(2)	11	Oracle拡張JSONタイプ浮動小数
`TYPE_TIMESTAMP`	NUMBER(2)	14	Oracle拡張JSONタイプ・タイムスタンプ
`TYPE_YMINTERVAL`	NUMBER(2)	15	Oracle拡張JSONタイプ年月時間隔

表100-3 mvrefreshmodeパラメータのDBMS_JSON定数

名前	タイプ	値	説明
MV_REFRESH_ON_STATEMENT	NUMBER(2)	`1`	refresh on文でマテリアライズド・ビューを作成します。
MV_REFRESH_ON_COMMIT	NUMBER(2)	`2`	refresh on commitでマテリアライズド・ビューを作成します。
MV_REFRESH_ON_DEMAND	NUMBER(2)	`3`	refresh on demandでマテリアライズド・ビューを作成します。

参照:

JSON開発者ガイド

100.4 DBMS_JSONサブプログラムの要約

この表は、DBMS_JSONサブプログラムを示し、簡単に説明しています。

DBMS_JSONパッケージのサブプログラム

サブプログラム	説明
ADD_VIRTUAL_COLUMNSプロシージャ	データ・ガイド情報に基づいて仮想列を追加します。シャード・カタログ・サーバー上で実行した場合、効果はありません。仮想列は追加されません。
CREATE_VIEWプロシージャ	データ・ガイドでの指定に従い、リレーショナル列およびスカラーJSONフィールドを含むビューを作成します。
CREATE_VIEW_ON_PATHプロシージャ	データ・ガイドの情報に基づき、リレーショナル列、トップ・レベルのスカラー・タイプおよび指定されたパスの下で完全に展開されたサブツリーを使用してビューを作成します。シャード・カタログ・サーバー上で実行された場合は、データ・ガイドが空であることを示すエラーが表示されます。
DROP_VIRTUAL_COLUMNSプロシージャ	`add_virtual_columns`プロシージャにより作成された仮想列を削除します。シャード・カタログ・サーバー上で実行した場合、効果はありません。
GET_INDEX_DATAGUIDEファンクション	データ・ガイドが有効なJSON検索索引からJSONデータ・ガイドを取得します。シャード・カタログ・サーバー上で実行された場合は、単一の空の行が結果として戻されます。
GET_VIEW_SQLファンクション	実際にビューを作成することなく、ビューを作成するためのデータ定義言語(DDL)文を取得します。
JSON_TYPE_CONVERTIBLE_CHECKプロシージャ	JSONテキストとして格納されている既存のデータが、JSONデータ型に移行できるかどうかをチェックします。
RENAME_COLUMNプロシージャ	データ・ガイドを使用して作成されるビュー列または仮想列に適切な名前を設定します。シャード・カタログ・サーバー上で実行した場合、効果はありません。

ノート:

シャーディングのコンテキスト内では、各シャードがそのシャードに格納されているJSONドキュメントから取得する、独自のデータ・ガイド情報を保持しています。データ・ガイド情報を使用するこのパッケージのプロシージャは、個別のシャードで実行された場合、そのシャードで保持されている情報のみを使用します。

100.4.1 ADD_VIRTUAL_COLUMNSプロシージャ

このプロシージャは、データ・ガイドに基づいて仮想列を追加します。

仮想列の名前は、データ・ガイドのo:preferred_vc_nameの値です。プロシージャは、データ・ガイドのJSONオブジェクト、配列および配列下のフィールドを無視します。仮想列が追加される前に、まずプロシージャADD_VIRTUAL_COLUMNSは、以前のADD_VIRTUAL_COLUMNSの起動、またはデータ・ガイド変更トリガー・プロシージャadd_vc (実質的に、このプロシージャの実行内容はプロシージャDBMS_JSON.DROP_VIRTUAL_COLUMNSと同じ)によって同じJSON列内のフィールドから投影された既存の仮想列を削除します。

参照:

構文

DBMS_JSON.ADD_VIRTUAL_COLUMNS (
    tablename               IN  VARCHAR2, 	                
    jcolname                IN  VARCHAR2, 	                
    dataguide               IN  CLOB,
    resolvenameconflicts    IN  BOOLEAN  DEFAULT TRUE,
    colnameprefix           IN VARCHAR2  DEFAULT NULL,
    mixedcasecolumns        IN BOOLEAN   DEFAULT FALSE);

次の署名では、JSON列にデータ・ガイドが有効な検索索引が含まれている必要があります。これは、前述の署名では必要ありません。

DBMS_JSON.ADD_VIRTUAL_COLUMNS (
    tablename  IN  VARCHAR2, 	                
    jcolname   IN  VARCHAR2, 	
    frequency      NUMBER    DEFAULT 0,                
    hidden         BOOLEAN   DEFAULT FALSE);

パラメータ

表100-4 ADD_VIRTUAL_COLUMNSプロシージャのパラメータ

パラメータ	説明
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	仮想列を作成する基となるデータを含む`tablename`表のJSON列の名前。
`dataguide`	データ・ガイド。データ・ガイドの特定のJSONフィールドで`o:hidden`が`TRUE`に設定されている場合、対応する仮想列が非表示列として追加されます。`o:hidden`のデフォルト値は`FALSE`です。
`resolvenameconflicts`	デフォルトでは`TRUEに設定されています`このプロシージャは、順序番号を付加することで仮想列名の競合を自動的に解決します。`FALSE`に設定すると、`o:preferred_column_name`の間で競合が発生した場合にエラーが発生します。
`colnameprefix`	デフォルトでは、仮想列名はJSONフィールド名と同じです。このパラメータを使用すると、仮想列名に接頭辞を追加できます。
`mixedcasecolumns`	デフォルトでは、仮想列名は大/小文字を区別します。このパラメータの値を`FALSE`に設定すると、仮想列名で大/小文字が区別されなくなります。
`frequency`	JSON列を表示するための最小頻度しきい値を設定します。頻度が0の場合、すべてのJSON列が表示されます。また、統計が収集されていない場合もすべてのJSON列が表示され、このパラメータによって設定された値が上書きされます。
`hidden`	`TRUE`は追加された仮想列が非表示になることを意味し、`FALSE`は非表示にならないことを意味します。デフォルトは`FALSE`です。

使用上のノート

DBMS_STATS.GATHER_STATSプロシージャは、データ・ガイドで統計を収集します。頻度統計が収集されていない場合、頻度はNULLです。頻度をゼロより大きい値に設定すると、頻度統計が収集されていない(統計がNULLの)列は含められません。ただし、DBMS_STATS.GATHER_STATSが実行されていない場合を除きます。その場合、頻度パラメータは無視され、すべての列がビューに表示されます。

100.4.2 CREATE_VIEWプロシージャ

このプロシージャは、データ・ガイドでの指定に従い、スカラーJSONフィールドを使用してリレーショナル列を含むビューを作成します。このプロシージャには、データ・ガイドが有効なJSON検索索引は必要ありません。データ・ガイドはプロシージャに渡されます。

参照:

Oracle Database JSON開発者ガイド

構文

PROCEDURE CREATE_VIEW (
   viewname                VARCHAR2,                       
   tablename               VARCHAR2,                       
   jcolname                VARCHAR2,                       
   dataguide               CLOB,
   resourcepath            VARCHAR2 DEFAULT NULL,
   materialize             BOOLEAN  DEFAULT FALSE,
   mvrefreshmode           NUMBER   DEFAULT MV_REFRESH_ON_STATEMENT,
   path                    VARCHAR2 DEFAULT '$',
   resolvenameconflicts    BOOLEAN  DEFAULT TRUE,
   colnameprefix           VARCHAR2 DEFAULT NULL,
   mixedcasecolumns        BOOLEAN DEFAULT FALSE);

パラメータ

表100-5 DBMS_JSON.CREATE_VIEWプロシージャのパラメータ

パラメータ	説明
`viewname`	ビューの名前。
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	ビューを作成するために使用される、`tablename`表のJSON列の名前。
`dataguide`	データ・ガイド。
`resourcepath`	このパラメータは、内部使用のためのものです。このパラメータの値は、常に`NULL`です。
`materialize`	このパラメータの値はブール値で、ビューがマテリアライズドかどうかを示します。
`mvrefreshmode`	マテリアライズがtrueのとき、このパラメータはマテリアライズド・ビューのリフレッシュ・モードを指定します。マテリアライズド・ビューのリフレッシュ・モード・オプションの詳細は、DBMS_JSON定数を参照してください。
`path`	展開する`JSON`フィールドのパス。`JSON`パス式の構文を使用します。これは、指定されたパスの下にある子を展開し、表示されたサブツリー内のスカラー値ごとにビュー列を作成します。パス$は、`JSON`ドキュメント・ルートから始まるビューを作成します。
`resolvenameconflicts`	デフォルトでは`TRUEに設定されています`このプロシージャは、順序番号を付加することで仮想列名の競合を自動的に解決します。`FALSE`に設定すると、`o:preferred_column_name`の間で競合が発生した場合にエラーが発生します。
`colnameprefix`	デフォルトでは、ビュー列名は`JSON`フィールド名と同じです。このパラメータを使用すると、ユーザーはビュー列名に付加する接頭辞を指定できます。
`mixedcasecolumns`	デフォルトでは、ビュー列名は大/小文字を区別します。このパラメータを使用して、ビュー列名の大/小文字を区別する動作を変更できます。

100.4.3 CREATE_VIEW_ON_PATHプロシージャ

このプロシージャは、トップ・レベルのスカラー値および指定されたパスの下で展開されたサブツリーのスカラー値を使用して、リレーショナル列を含むビューを作成します。JSON列には、データ・ガイドが有効な検索索引が含まれている必要があります。

参照:

Oracle Database JSON開発者ガイド

構文

PROCEDURE CREATE_VIEW_ON_PATH(
   viewname VARCHAR2, 
   tablename VARCHAR2, 
   jcolname VARCHAR2, 
   path VARCHAR2,
   frequency NUMBER DEFAULT 0);

パラメータ

表100-6 CREATE_VIEW_ON_PATHプロシージャのパラメータ

パラメータ	説明
`viewname`	ビューの名前。
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	ビューを作成するために使用される、`tablename`表のJSON列の名前。この列には、データ・ガイドが有効なJSON検索索引が含まれている必要があり、そうでない場合はエラーが発生します。
`path`	展開するJSONフィールドのパス。JSONパス式構文が使用されます。これは、指定されたパスの下にある子を展開し、表示されたサブツリー内のスカラー値ごとにビュー列を作成します。パス`$`を指定すると、JSONドキュメント・ルートから始まるビューが作成されます。
`frequency`	JSON列を表示するための最小頻度しきい値。頻度が0の場合、すべてのJSON列が表示されます。統計が収集されていない場合もすべてのJSON列が表示され、このパラメータによって設定された値が上書きされます。ビューには、`frequency`で指定された値より高い頻度を持つJSONフィールドのみが表示されます。指定された頻度が0より大きい場合、統計の収集後に追加されたJSONフィールドのうち、統計列が`NULL`であるフィールドは表示されません。

100.4.4 DROP_VIRTUAL_COLUMNSプロシージャ

PL/SQLプロシージャDBMS_JSON.add_virtual_columnsまたはデータ・ガイド変更トリガー・プロシージャadd_vcを使用して追加された仮想列をすべて削除します。

参照:

構文

PROCEDURE DROP_VIRTUAL_COLUMNS(
   tablename VARCHAR2,                                
   jcolname VARCHAR2);

パラメータ

表100-7 DBMS_JSON.DROP_VIRTUAL_COLUMNSプロシージャのパラメータ

パラメータ	説明
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	`tablename`表のJSON列の名前。

100.4.5 GET_INDEX_DATAGUIDEファンクション

GET_INDEX_DATAGUIDEは、データ・ガイドが有効なJSON検索索引からJSONデータ・ガイドを取得します。

参照:

Oracle Database JSON開発者ガイド

構文

FUNCTION GET_INDEX_DATAGUIDE(
   tablename VARCHAR2,
   jcolname VARCHAR2,
   format NUMBER,
   pretty NUMBER DEFAULT 0) 
   RETURN CLOB;

パラメータ

表100-8 DBMS_JSON.GET_INDEX_DATAGUIDEプロシージャのパラメータ

パラメータ	説明
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	データ・ガイドが有効なJSON検索索引を持つ、`tablename`表のJSON列の名前。
`format`	データ・ガイド・フォーマットは次のとおりです。 `FORMAT_HIERARCHICAL` — 階層フォーマット `FORMAT_FLAT` — フラット・フォーマット
`pretty`	`DBMS_JSON.PRETTY`の値を指定すると、読みやすくするためにインデントを使用し、整形されたデータ・ガイドが出力されます。

例100-1 整形された階層フォーマットでデータ・ガイドを出力する例

この例では、階層フォーマットでデータ・ガイドが戻されます。

SELECT DBMS_JSON.GET_INDEX_DATAGUIDE(‘T1’, ‘PO’, DBMS_JSON.FORMAT_HIERARCHICAL, DBMS_JSON.PRETTY) 
FROM DUAL;

100.4.6 GET_VIEW_SQLファンクション

このファンクションは、ビューを実際に作成せずにビュー作成DDLを戻します。このファンクションには、データ・ガイドが有効なJSON検索索引は必要ありません。データ・ガイドはファンクションに渡されます。

参照:

Oracle Database JSON開発者ガイド

構文

FUNCTION GET_VIEW_SQL (
   viewname                VARCHAR2,                       
   tablename               VARCHAR2,                       
   jcolname                VARCHAR2,                       
   dataguide               CLOB,
   materialize             BOOLEAN  DEFAULT FALSE,
   mvrefreshmode           NUMBER   DEFAULT MV_REFRESH_ON_STATEMENT,
   path                    VARCHAR2 DEFAULT '$',
   resolvenameconflicts    BOOLEAN  DEFAULT TRUE,
   colnameprefix           VARCHAR2 DEFAULT NULL,
   mixedcasecolumns        BOOLEAN DEFAULT TRUE)
   RETURN CLOB;

パラメータ

表100-9 DBMS_JSON.GET_VIEW_SQLファンクションのパラメータ

パラメータ	説明
`viewname`	ビューの名前。
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	ビューを作成するために使用される、`tablename`表のJSON列の名前。
`dataguide`	データ・ガイド。
`materialize`	このパラメータの値はブール値で、ビューがマテリアライズドかどうかを示します。
`mvrefreshmode`	マテリアライズがtrueのとき、このパラメータはマテリアライズド・ビューのリフレッシュ・モードを指定します。マテリアライズド・ビューのリフレッシュ・モード・オプションの詳細は、DBMS_JSON定数を参照してください。
`path`	展開する`JSON`フィールドのパス。`JSON`パス式の構文を使用します。これは、指定されたパスの下にある子を展開し、表示されたサブツリー内のスカラー値ごとにビュー列を作成します。パス$は、`JSON`ドキュメント・ルートから始まるビューを作成します。
`resolvenameconflicts`	デフォルトでは`TRUEに設定されています`このファンクションのパラメータは、順序番号を付加することで仮想列名の競合を自動的に解決します。`FALSE`に設定すると、`o:preferred_column_name`の間で競合が発生した場合にエラーが発生します。
`colnameprefix`	デフォルトでは、ビュー列名は`JSON`フィールド名と同じです。このパラメータを使用すると、ユーザーはビュー列名に付加する接頭辞を指定できます。
`mixedcasecolumns`	デフォルトでは、ビュー列名は大/小文字を区別します。このパラメータを使用して、ビュー列名の大/小文字を区別する動作を変更できます。

使用上のノート

データベースで幅の広い表が有効になっている場合(MAX_COLUMNS=EXTENDED):
- viewnameがNULLの場合、ファンクションはビューDDLのSELECT文のみを戻し、4096を超える列を選択できます。
- viewnameがNULLでない場合、ファンクションはビュー作成DDLを戻し、最大4096個の列を選択します。
- 1つのjson_tableに生成できる列は最大で4096のみとなるため、viewnameがNULLの場合にパスが4096を超えると、ファンクションはパスを複数のjson_tables間の結合に分割します。
データベースで幅の広い表が有効になっていない場合(MAX_COLUMNS=STANDARD):
- viewnameがNULLの場合、ファンクションはビューDDLのSELECT文のみを戻し、1000を超える列を選択できます。
- viewnameがNULLでない場合、ファンクションはビュー作成DDLを戻し、最大1000個の列を選択します。
- 1つのjson_tableに生成できる列は最大で1000のみとなるため、viewnameがNULLの場合にパスが1000を超えると、ファンクションはパスを複数のjson_tables間の結合に分割します。

参照:

MAX_COLUMNS初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください

100.4.7 JSON_TYPE_CONVERTIBLE_CHECKプロシージャ

このプロシージャは、JSONテキストとして格納されている既存のデータが、JSONデータ型に移行できるかどうかをチェックします。

参照:

Oracle Database JSON開発者ガイド

構文

PROCEDURE JSON_TYPE_CONVERTIBLE_CHECK (
   owner                   VARCHAR2,
   tableName               VARCHAR2,
   columnName              VARCHAR2,
   statusTableName         VARCHAR2,
   fastCheck               BOOLEAN DEFAULT FALSE,
   appendStatus            BOOLEAN DEFAULT FALSE);

パラメータ

表100-10 DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECKプロシージャのパラメータ

パラメータ	説明
`owner`	表の所有者の名前。
`tableName`	表の名前。
`columnName`	JSONタイプに変換するデータを格納する表`tableName`の列の名前。
`statusTableName`	操作のトラッキング・ステータスを追加するために使用する表の名前。この表はすでに存在していることも、作成が必要になることもあります。すでに存在する場合は、このプロシージャによって予期される形状に準拠していることが確認されます。
`fastCheck`	このオプション・パラメータの値はブールです。このパラメータが`TRUE`に設定されている場合は、入力データが変換可能なことを確認するために`is_json`チェック制約が実行されます。このパラメータが`FALSE`の場合は、入力データが変換可能なことを確認するために`oson`コンストラクタが実行されます。デフォルト値は`FALSE`です。
`appendStatus`	このオプション・パラメータの値はブールです。このパラメータが`TRUE`に設定されている場合、ステータス表は切り捨てられません。このパラメータが`FALSE`の場合は、既存のステータス表があれば切り捨てられます。いずれの場合も、ステータス表がまだ存在していないときには、その表が作成され、このプロシージャの実行による新しいデータのみが含まれます。デフォルト値は`FALSE`です。

100.4.8 RENAME_COLUMNプロシージャ

このプロシージャは、ビューの作成または仮想列の追加プロシージャにより使用されるJSON列に適切な名前を設定します。

参照:

Oracle Database JSON開発者ガイド

構文

PROCEDURE RENAME_COLUMN(
   tablename VARCHAR2,
   jcolname VARCHAR2,
   path VARCHAR2,
   type NUMBER,
   preferred_name VARCHAR2);

パラメータ

表100-11 RENAME_COLUMNプロシージャのパラメータ

パラメータ	説明
`tablename`	JSON列`jcolname`を含む表の名前。
`jcolname`	`tablename`表のJSON列の名前。データ・ガイドが有効なJSON検索索引が含まれている必要があり、そうでない場合はエラーが発生します。
`path`	適切な列名を設定するJSONフィールドのパス。
`type`	`path`の対象であるJSONフィールドのタイプ。タイプが異なっていれば、2つのJSONフィールドに同じパスを指定できます。有効な値は、次のとおりです。 `TYPE_NULL` `TYPE_STRING` `TYPE_NUMBER` `TYPE_BOOLEAN` `TYPE_OBJECT` `TYPE_ARRAY`
`preferred_name`	`path`により指定されるJSONフィールドの適切な名前。名前の競合がある場合は、かわりにシステム生成の名前が使用されます。

例100-2 列の名前変更の例

この例では、フィールドの名前をitem_nameに変更します。

EXEC DBMS_JSON.RENAME_COLUMN(‘T1’, ‘PO’, ‘$.purchaseOrder.items.name’, DBMS_JSON.TYPE_STRING, ‘item_name’);