A blog about Oracle Technology Network Japan
- October 27, 2020
津島博士のパフォーマンス講座 第78回 Oracle DatabaseのJSONについて
皆さんこんにちは、今年は10月から気温が低いので、日々の急激な寒暖差に身体がついていけませんね。
今回は、Oracle Databaseに格納されたJSONデータを扱うための機能について取り上げます。後半に、Oracle Database 12cR2（Oracle12cR2）からの問合せパフォーマンスを向上させるインメモリJSONデータについても説明していますので、参考にしてください。
1. JSONデータとOracle Database
従来のアプリケーション開発では、急速に変化するビジネス要件に迅速に対応できないため、スキーマレス開発を採用する機会が増えています。その中でよく使用されるのが、XML（Extensible Markup Language）よりシンプルなJSON（JavaScript Object Notation）です。Oracle Databaseは、スキーマを必要としないJSONを、Simple Oracle Document Access (SODA) APIを使用したNoSQLスタイルのアプリケーション開発以外に、リレーショナル・データベース機能（トランザクション、索引、SQL問合せ、ビューなど）を使用してネイティブにサポートしています（つまり、他のデータと同じように扱うことが可能です）。ここでは、SQLとJSONデータの基本的な連携方法について説明します。
（１）JSONとは
まずは、JSONについて簡単に説明します。
JSONは、言語に依存しない軽量のデータ交換フォーマットです（コードの理解や記述が容易なため、現在は多くで利用されています）。JavaScriptでオブジェクトを作成する際は、中カッコ({})や角カッコ ([])などを使って記述しますが、JSONはその表記法を元にしています。そのため、オブジェクト（キーの名前/値のペア）と配列の構造に基づいていることから、ほとんどのプログラミング言語から簡単に扱えるので、理想的なデータ交換言語となっています。以下に、簡単なJSONドキュメントを示します（"... "は、省略を意味しています）。
{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Alexis Bull",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : [{"type" : "Office", "number" : "909-555-7307"},
{"type" : "Mobile", "number" : "415-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
... ]
}
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
JSONの値は、オブジェクト、配列、数値、文字列、ブール値(trueまたはfalse)、NULLのいずれかです。上記の発注書オブジェクトの例では、"PONumber"には数値が、"Reference"には文字列が、"ShippingInstructions"にはオブジェクトが、"LineItems"には配列が、"AllowPartialShipment"にはブール値が、" Special Instructions"にはNULLが含まれています。
（２）JSONドキュメントの格納
次に、JSONドキュメントの格納方法について説明します。
Oracle Databaseでは、JSONデータを既存のデータ型（VARCHAR2、CLOB、BLOB）に格納することで、データ挿入も同じように行います（以下は、VARCHAR2の例ですが、CLOBにはTO_CLOBファンクション、BLOBにはutl_raw.cast_to_rawファンクションを使用して挿入を行います）。このときに、"IS JSON"チェック制約を指定しないと使用できない機能があるので注意してください（ただし、構文解析のためにJSON文書の取り込みが若干遅くなります）。
CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document VARCHAR2 (32767) CONSTRAINT ensure_json CHECK (po_document IS JSON));
INSERT INTO j_purchaseorder VALUES (SYS_GUID(), to_date('30-DEC-2014'),
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Alexis Bull",
"Address" : { ... },
"Phone" : [ ... ]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [ ... ]
}');
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
Oracle12cR2では、外部表を使用することで、データベースの外部に格納されたJSONドキュメントの操作も可能です。
（３）JSONデータの問合せ
次に、JSONデータのSQL問合せについて説明します。 Oracle Databaseでは、以下の2つの方法（ドット表記法の式、SQL/JSONパス式）を使用したJSONデータのSQLアクセスが提供され、リレーショナル・データと同じように問合せや分析が可能になります（ドット表記法の式やJSONパス式では、この式と一致するか、この式を満たすJSON値が選択されます）。
・単純な問合せ（ドット表記法を使用した問合せ）
ドット表現法は、ドット(.)で区切られた1つ以上のJSONフィールド（キーの名前）から構成されます。ただし、"IS JSON"チェック制約とテーブル・エイリアスが必要で（"IS JSON"チェック制約は、DISABLEにするだけでもORA-00904エラーになりません）、戻り値は常にVARCHAR2(4000)の文字列です（4000バイトよりも長い値は戻せません）。以下に、ドット表記法の代表的な例を示します。
-- JSON値（ShippingInstructionsのAddressのcity）の問合せ SELECT po.po_document.ShippingInstructions.Address.city FROM j_purchaseorder po; -- 配列の使用（LineItemsの最初の要素） SELECT po.po_document.LineItems[0] FROM j_purchaseorder po; -- WHERE句の条件での使用 SELECT po.po_document FROM j_purchaseorder po WHERE po.po_document.PONumber = 1600;
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
・高度な問合せ（JSONパス式を使用した問合せ）
より多くの機能が必要な場合には、以下の5つのSQL/JSON関数を使用できます。JSON関数は、JSONパス言語を完全にサポートし、ドット表記式で可能な範囲よりも強力で柔軟性があります（RETURNING句、ラッパー句、エラー句、空白フィールド句などが指定できます）。
| 関数 | 説明 |
| json_query() | JSONデータから1つ以上の値をSQL文字列として選択する。特に、JSON文章の断片（オブジェクト、配列）を取得するために使用する。 |
| json_value() | JSONデータからスカラー値を選択する。SELECTリストやWHERE句で使用したり、ファンクション索引を作成したりするために、最も多く使用される。 |
| json_exists() | JSONデータ内に特定の値が存在するかどうかをテストする。 |
| json_table() | JSONから表形式でデータを取得する。JSON_VALUEを大量に使用する代わりにも使用する。 |
| json_textcontains() | JSONデータのフル・テキスト検索を行う（テキスト述語に基づいて存在をテストする）。 |
以下に、JSONパス式の代表的な例を示します（最初の3つは、ドット表記法と同じ結果を得る問合せです。つまり、ドット表記法は、JSON_VALUE関数とJSON_QUERY関数の省略表記法です）。
--- JSON_VALUESでの絶対パス（パス式のコンテキスト項目を表すドル記号($)から始まります） SELECT JSON_VALUE(po_document,'$.ShippingInstructions.Address.city') City FROM j_purchaseorder; -- JSON_QUERYを使用した配列の問合せ（配列はスカラー値でないのでJSON_VALUEは使用できない） SELECT JSON_QUERY(po_document,'$.LineItems[0]') FROM j_purchaseorder; -- JSON_VALUESでのRETURNING句（返される値のデータ型）とエラー句（実行時エラーの処理方法を「特別な処理なし」に変更） SELECT po_document FROM j_purchaseorder WHERE JSON_VALUE(po_document,'$.PONumber' RETURNING NUMBER(10) ERROR ON ERROR) = 1600; -- JSON_EXISTSでの相対パス（フィルタ式内で@から始まります） SELECT po_document FROM j_purchaseorder WHERE JSON_EXISTS(po_document,'$.LineItems?(@.Part.UPCCode == 13131092899)'); -- JSON_TABLEでのCOLUMNS句を使用した仮想表（インライン・ビュー） SELECT jt.* FROM j_purchaseorder po, JSON_TABLE(po.po_document,'$' COLUMNS(po_number NUMBER(10) PATH '$.PONumber', reference VARCHAR2(30 CHAR) PATH '$.Reference', … )) AS jt;
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
（４）JSONデータの索引
最後に、JSONデータの索引について説明します。
問合せのパフォーマンスを向上するには、通常のデータ型と同じように、JSONフィールドに対する索引作成を行います。以下のように、定型検索のための索引と非定型検索のための索引があります。
・定型検索のためのファンクション索引
定型検索に対して、JSON_VALUE関数を使ったファンクション索引、JSON_EXISTS関数を使ったファンクション・ビットマップ索引などを作成することができます。
-- JSON_VALUE関数を使ったファンクション索引（Bツリー索引またはビットマップ索引） CREATE UNIQUE INDEX po_num_idx1 ON j_purchaseorder (JSON_VALUE(po_document,'$.PONumber' RETURNING NUMBER)); -- JSON_EXISTS関数を使ったファンクション索引（値がtrueとfalseだけのためビットマック索引） CREATE BITMAP INDEX po_zipcode_idx ON j_purchaseorder (JSON_EXISTS(po_document,'$.ShippingInstructions.Address.zipCode'));
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
複数列Bツリー索引は、以下のように仮想列を作成してから行う必要があります。
ALTER TABLE j_purchaseorder ADD (userid VARCHAR2(20) GENERATED ALWAYS AS (JSON_VALUE(po_document,'$.User' RETURNING VARCHAR2(20)))); ALTER TABLE j_purchaseorder ADD (costcenter VARCHAR2(6) GENERATED ALWAYS AS (JSON_VALUE(po_document, '$.CostCenter' RETURNING VARCHAR2(6)))); CREATE INDEX user_cost_ctr_idx ON j_purchaseorder (userid, costcenter);
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
以下のようにSQL文を実行することで索引が使用されます。
SQL> SELECT po_document FROM j_purchaseorder WHERE JSON_VALUE(po_document,'$.PONumber' RETURNING NUMBER) = 1600; ------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS BY INDEX ROWID| J_PURCHASEORDER | |* 2 | INDEX UNIQUE SCAN | PO_NUM_IDX1 | -------------------------------------------------------
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
・非定型検索のための索引（JSON検索索引）
これの実体は、Oracle Text索引(ドメイン索引)で、一般的な索引で特定のJSONパス式が対象ではありません。JSON_TEXTCONTAINS関数では、JSON検索索引がない場合はエラー「ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without a JSON-enabled context index」が発生します（Oracle TextのCONTAINS()に相当する関数です）。JSON検索索引は、JSON_EXISTS関数、JSON_VALUE関数でも使用されます。以下の例は、LineItemのPartのDescriptionに'Magic'が含まれる発注書を検索するフル・テキスト問合せです。
SQL> CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document) FOR JSON;
SQL> SELECT po_document FROM j_purchaseorder WHERE JSON_TEXTCONTAINS(po_document,'$.LineItems.Part.Description', 'Magic');
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| J_PURCHASEORDER |
|* 2 | DOMAIN INDEX | PO_SEARCH_IDX |
-------------------------------------------------------
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
2. インメモリJSONデータ
ここでは、Oracle12cR2からのインメモリJSONデータについて説明します。
JSONデータをIM列ストアに格納することで、以下の二つのメリットにより高速にアクセスすることができます。
- JSON文書を高度に最適化されたインメモリ・バイナリ形式（OSON）で格納
- SQL/JSON関数の仮想列をインメモリ仮想列（IM仮想列）に格納
（１）インメモリ・バイナリ形式（OSON）
インメモリJSONデータは、SQL/JSONパス式のアクセス・パフォーマンスが強化されています。SQL関数 (json_table、json_query、json_value、json_exists)は、JSONパス引数を受け入れているので、JSONデータをインメモリ化することで、高速にアクセスすることができます。
JSON列が含まれる表にINMEMORY句を指定すると、以下の条件のときに各JSON列に対する仮想列が、その表のインメモリ式ユニット（IMEU）に自動的に追加されます。これには、対応するJSON列と同じJSONデータが含まれますが、その形式はOracleのバイナリ形式OSON（バイナリJSON列）になります。
- 初期化パラメータMAX_STRING_SIZEはEXTENDEDに設定されている。
- 初期化パラメータINMEMORY_VIRTUAL_COLUMNSがENABLEに設定されている（明示的にINMEMORYを指定していない仮想列も対象にする）。
- JSONデータ列に"IS JSON"チェック制約が設定されている（JSONデータであることが分かっている）。
これがJSON列の問合せに使用されると、JSONパス式を解析せずに評価することができ、ディスク読取りや解析に関連するオーバーヘッドが不要になります。そのため、非定型問合せでは、データ・スキャンを迅速に行えるため、JSONデータをインメモリ化するメリットが受けられます。定型問合せは、「（２）JSON関数のIM仮想列」を使用することで、さらにパフォーマンスが向上されます。
IM列ストアは、32,767バイトまでのJSON文書の問合せに使用されます。これより大きな文書の問合せでは、IM列ストアを利用するメリットがないので注意してください。
（２）JSON関数のIM仮想列
JSONデータ列が格納された表をIM列ストアにポピュレートすると、コストがかかる式を使用する問合せパフォーマンスも向上することができます。第61回のIM仮想列は、JSON関数のスカラー値を抽出する仮想列でも使用できるので、関数の事前処理結果を格納することができ、より高速なアクセスが可能になります（JSONデータのファンクション索引は、明示的に仮想列を作成する必要はありません）。ただし、JSON検索索引は、ファンクション索引ではないので、IM仮想列にできません。そのため、JSON_TEXTCONTAINS関数を使用したフル・テキスト検索では、IM列ストアを使用するメリットはないので、JSON検索索引を使用する必要があります（Oracle20cからのJSONデータ型とIn-Memory Textで改善されます）。
定型問合せでは、以下のようなJSON_TABLE関数にマテリアライズド・ビューを作成し、そのビューをIM列ストアにロードするのも有効です。
CREATE MATERIALIZED VIEW j_purchaseorder_materialized_view BUILD IMMEDIATE REFRESH FAST ON COMMIT WITH PRIMARY KEY AS SELECT po.id, d.* FROM j_purchaseorder po, JSON_TABLE(po.po_document, '$' COLUMNS (po_number NUMBER(10) PATH '$.PONumber', reference VARCHAR2(30 CHAR) PATH '$.Reference', requestor VARCHAR2(128 CHAR) PATH '$.Requestor', userid VARCHAR2(10 CHAR) PATH '$.User', ...
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
（３）インメモリ列ストアへのポピュレート
ポピュレートは、通常のデータ型と同じように、JSON列が格納された表にINMEMORY句を設定することで、その表をIM列ストアに格納します（以下の例は、PRIORITYがNONEのため、フル・スキャンでポピュレートしています）。
SELECT COUNT(1) FROM j_purchaseorder WHERE JSON_EXISTS(po_document,'$.ShippingInstructions?(@.Address.zipCode == 99236)'); ---------------------------------------------- | Id | Operation | Name | ---------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | |* 2 | TABLE ACCESS FULL| J_PURCHASEORDER | <- 実行計画は、TABLE ACCESS FULLです ---------------------------------------------- -- テーブルをINMEMORYとして指定します。デフォルトのPRIORITY設定はNONEであるため、 -- フル・スキャンからトリガーされたときにのみデータがポピュレートされます。 ALTER TABLE j_purchaseorder INMEMORY; -- テーブルを再度クエリして、IM列ストアにポピュレートします。 SELECT COUNT(1) FROM j_purchaseorder WHERE JSON_EXISTS(po_document,$.ShippingInstructions?(@.Address.zipCode == 99236)'); ------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | |* 2 | TABLE ACCESS INMEMORY FULL| J_PURCHASEORDER | <- 実行計画は、TABLE ACCESS INMEMORY FULLです -------------------------------------------------------
※コードがうまく表示されない方はこちらから.txtをダウンロードしてご確認ください。
ただし、以下で作成されたJSON列が含む表は、IM列ストアにポピュレートする前にアップグレードする必要があります。
- Oracle12cR2以降の互換性設定（初期化パラメータCOMPATIBLE）のないデータベース
- MAX_STRING_SIZE=EXTENDED設定のないデータベース
これを行うには、スクリプトrdbms/admin/utlimcjson.sqlを実行します。実行すると、JSON列が含まれるすべての既存の表が、IM列ストアにポピュレートできるようにアップグレードされます。
3. おわりに
今回は、Oracle DatabaseのJSONについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。