19 SQL/JSONファンクションJSON_TABLE
SQL/JSONファンクションjson_table
は様々なSQLデータ型の列に特定のJSONデータを投影します。これを使用してJSON文書の一部を新規仮想表の行および列にマップしますが、これは、インラインのビューとみなすこともできます。
この仮想表は、既存のデータベース表に挿入でき、またSQLを使用して(join式など)問合せできます。
json_table
の一般的な使用目的は、JSONデータのビューを作成することです。このようなビューは、任意の表またはビューを使用する場合と同じように使用できます。このため、アプリケーション、ツールおよびプログラマは、JSONまたはJSONパス式の構文を考慮せずにJSONデータを操作できます。
JSONデータに対してビューを定義することにより、実質的にある種のスキーマがそのデータにマップされます。このマッピングは、事後に行われます。つまり、基礎となるJSONデータは、スキーマまたは特定の使用パターンを考慮せずに定義および作成できます。データが最初で、スキーマが後です。
このようなスキーマ(マッピング)により、データベースに格納できるJSON文書の種類に(整形式のJSONデータであること以外の)制約が課されることはありません。ビューでは、ビューを定義するマッピング(スキーマ)に準拠するデータのみが公開されます。ビューを再定義するだけでスキーマを変更でき、基礎となるJSONデータを再編成する必要はありません。
json_table
はSQLのFROM
句で使用します。これは行ソースです。これにより、行パス式(行パターン)によって選択されたJSON値ごとに仮想表のデータの行が生成されます。生成された各行の列は、COLUMNS
句の列パス式によって定義されます。
通常、json_table
呼出しは、FROM
リスト内のソース表と暗黙的に横方向に結合されます。その行にはそれぞれ、ファンクションへの入力として使用されるJSON文書が含まれています。json_table
では、入力文書に対して行パス式を評価することにより決定される、0行以上の新規行を生成します。
json_table
の最初の引数はSQL式です。これは、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。式の評価の結果は、行パス式を評価するためのコンテキスト項目として使用されます。
json_table
の2番目の引数はSQL/JSON行パス式であり、行を処理するためのオプションのエラー句と(必須の) COLUMNS
句が付加されます。この句により、作成する仮想表の列が定義されます。RETURNING
句はありません。
json_table
のエラー処理には2つのレベルがありますが、これらは、パス式の2つのレベルである行と列に対応しています。存在する場合、列のエラー・ハンドラにより、行レベルのエラー処理がオーバーライドされます。両レベルのデフォルトのエラー・ハンドラはNULL ON ERROR
です。
行パス式配列ステップでは、索引と範囲の順序、配列索引の複数出現、および範囲の重複による指定された位置の重複は、すべて通常の効果を持ちます。指定した位置はデータと順番に照合され、位置の一致ごとに1つの行が生成されます。
コンテキスト項目引数と行パス式を渡すかわりに、単純なドット表記法構文を使用できます。(引き続きエラー句を使用でき、COLUMNS
句が必要です。)ドット表記法では、対象のJSONデータへの単純なパスとともに表またはビューの列を指定します。たとえば、この2つの問合せは等価です。
json_table(t.j, '$.ShippingInstructions.Phone[*]' ...)
json_table(t.j.ShippingInstructions.Phone[*] ...)
行パス式が'$'
のみで、文書全体を対象とする場合、パスの部分を省略できます。次の問合せは等価です。
json_table(t.j, '$' ...)
json_table(t.j ...)
例19-1に、単純なドット表記法の使用と、より完全で明示的な表記法の使用の違いを示します。
SQL/JSONパス式を使用するかわりに、COLUMNS
句のPATH
句でドット表記を使用することもできます。たとえば、PATH '$.ShippingInstructions.name'
のかわりにPATH 'ShippingInstructions.name'
を使用できます。
例19-1 等価のJSON_TABLE問合せ: 単純な構文と完全な構文
この例では、2つの等価の問合せにjson_table
を使用します。最初の問合せでは、行および列データを対象とする式に単純なドット表記法構文を使用します。2番目の問合せでは、完全な構文を使用します。
SQL識別子が引用符で囲まれている列Special Instructions
を除いて、実際のSQL列名は大文字です。(識別子Special Instructions
には空白文字が含まれています。)
最初の問合せでは、列名の記述は、大/小文字の区別を含め、対象となるオブジェクト・フィールドの名前と完全に同じです。引用符で囲まれるかどうかに関係なく、これらは、デフォルト・パス(明示的なPATH
句がない場合に使用されるパス)を設定するために大/小文字を区別して解釈されます。
2番目の問合せには次のものが含まれています。
-
JSON列式およびSQL/JSON行パス式の別々の引数
-
VARCHAR2(4000)
の明示的な列データ型 -
投影されるオブジェクト・フィールドを対象とするための、明示的な
PATH
句およびSQL/JSON列パス式
SELECT jt.*
FROM j_purchaseorder po,
json_table(po.po_document
COLUMNS ("Special Instructions",
NESTED LineItems[*]
COLUMNS (ItemNumber NUMBER,
Description PATH Part.Description))) AS "JT";
SELECT jt.*
FROM j_purchaseorder po,
json_table(po.po_document,
'$'
COLUMNS (
"Special Instructions" VARCHAR2(4000)
PATH '$."Special Instructions"',
NESTED PATH '$.LineItems[*]'
COLUMNS (
ItemNumber NUMBER PATH '$.ItemNumber',
Description VARCHAR(4000) PATH '$.Part.Description'))
) AS "JT";
関連項目:
json_table
の詳細は、Oracle Database SQL言語リファレンスを参照してください
- JSON_TABLEの代替のSQL NESTED句
SELECT
句で、SQL/JSONファンクションjson_table
のかわりにNESTED
句を使用することがよくあります。これにより、問合せ式が単純化されることがあります。これには、JSON列がNULL
の場合、NULL
以外のリレーショナル列を含む行を含めるという利点もあります。 - SQL/JSONファンクションJSON_TABLEのCOLUMNS句
SQL/JSONファンクションjson_table
の必須のCOLUMNS
句は、このファンクションによって作成される仮想表の列を定義します。 - JSON_TABLEによるSQL/JSON問合せファンクションおよび条件の一般化
SQL/JSONファンクションjson_table
は、SQL/JSON条件json_exists
と、SQL/JSONファンクションjson_value
およびjson_query
を一般化します。これらの関数を使用して実行できる処理はすべて、json_table
を使用して実行できます。これらによって実行されるジョブについては、これらの関数の構文の方がjson_table
の構文よりも簡単に使用できます。 - JSON_TABLEとJSON配列の使用
JSON値は、1つの配列にすることも、1つ以上の配列を含めることも可能であり、他のJSON配列またはオブジェクト内の任意の数のレベルにネストしてもかまいません。json_table
NESTED
パス句を使用して、配列の特定の要素を投影できます。 - JSON_TABLEを使用したJSONデータに対するビューの作成
問合せのパフォーマンスを向上させるために、SQL/JSONファンクションjson_table
を使用して、列に投影するJSONデータに対してビューを作成できます。問合せのパフォーマンスをさらに向上させるには、マテリアライズド・ビューを作成し、JSONデータをインメモリーに配置できます。
親トピック: JSONデータの問合せ
19.1 JSON_TABLEの代替のSQL NESTED句
SELECT
句で、SQL/JSONファンクションjson_table
のかわりにNESTED
句を使用することがよくあります。これにより、問合せ式が単純化されることがあります。これには、JSON列がNULL
の場合、NULL
以外のリレーショナル列を含む行を含めるという利点もあります。
NESTED
句は、ANSI左外部結合でjson_table
を使用するためのショートカットです。つまり、この2つの問合せは等価です。
SELECT ...
FROM mytable NESTED jcol COLUMNS (...);
SELECT ...
FROM mytable t1 LEFT OUTER JOIN
json_table(t1.jcol COLUMNS (...)
ON 1=1;
json_table
で左外部結合を使用したり、NESTED
句を使用すると、選択結果に対応するJSON列データがない(つまり、JSON列がNULL
である)リレーショナル列を含む行を含めることができます。この2つの間の唯一のセマンティックの相違点は、NESTED
句を使用する場合、JSON列自体が結果に含まれないことです。
NESTED
句は、ネストした列の可能性を含め、json_table
と同じCOLUMNS
句を指定します。NESTED
を使用するメリットは、次のとおりです。
-
単純なドット表記法を使用している場合でも、表別名を指定する必要はありません。
-
JSON列が
JSON
型でない場合でも、is json
チェック制約を指定する必要はありません。(列がJSON
型でないかぎり、単純なドット表記を使用したjson_table
には制約が必要です。) -
LEFT OUTER JOIN
を指定する必要はありません。
NESTED
句の構文はより単純で、COLUMNS
句のすべての柔軟性が実現され、暗黙的な左外部結合が実行されます。これを例19-2に示します。
例19-3に、単純なドット表記法を使用したNESTED
句の使用を示します。
例19-2 等価: SQL NESTEDとLEFT OUTER JOINを指定したJSON_TABLE
次の2つの問合せは同じです。1つは、明示的なLEFT OUTER JOIN
を指定したでSQL/JSONファンクションjson_table
を使用します。もう1つは、SQLのNESTED
句を使用します。
SELECT id, requestor, type, "number"
FROM j_purchaseorder LEFT OUTER JOIN
json_table(po_document
COLUMNS (Requestor,
NESTED ShippingInstructions.Phone[*]
COLUMNS (type, "number")))
ON 1=1);
SELECT id, requestor, type, "number"
FROM j_purchaseorder NESTED
po_document
COLUMNS (Requestor,
NESTED ShippingInstructions.Phone[*]
COLUMNS (type, "number");
出力はどちらの場合でも同じです。
7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Office 909-555-7307
7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Mobile 415-555-1234
7C3A54B183066369E0536DE05A0A15E4 Sarah Bell
表j_purchaseorder
に、列id
およびrequestor
の値がNULL
以外で、列po_document
の値がNULL
である行がある場合、その行は両方のケースで表示されます。一方、LEFT OUTER JOIN
が存在しない場合、json_table
のケースには表示されません。
例19-3 SQL NESTEDを使用したネスト配列の拡張
この例では、表j_purchaseorder
から列id
およびdate_loaded
を、JSON列po_document
のフィールドShippingInstructions
の値にネストされているフィールドPhone
の配列要素とともに選択します。Phone
配列値は列type
およびnumber
として展開されます。
(列指定"number"
には、number
がSQLの予約語であるため二重引用符が必要です。)
SELECT *
FROM j_purchaseorder NESTED
po_document.ShippingInstructions.Phone[*]
COLUMNS (type, "number")
親トピック: SQL/JSONファンクションJSON_TABLE
19.2 SQL/JSONファンクションJSON_TABLEのCOLUMNS句
SQL/JSONファンクションjson_table
の必須のCOLUMNS
句は、このファンクションによって作成される仮想表の列を定義します。
これは、キーワードCOLUMNS
の後ろに次のエントリをカッコで囲んだ形式で構成されます。オプションのFOR ORDINALITY
エントリ以外、COLUMNS
句の各エントリは、標準列指定またはネストした列指定です。
-
COLUMNS
句内の多くても1つのエントリは、生成される行数の列(SQLデータ型NUMBER
)を指定するキーワードFOR ORDINALITY
を列名の後ろに付けた形式にすることができます。これらの数は1から始まります。次に例を示します。COLUMNS (linenum FOR ORDINALITY, ProductID)
行パス式の配列ステップは、パス式と一致する任意の行数になります。特に、配列ステップの索引と範囲の順序、配列索引の複数出現、および範囲の重複による指定された位置の重複によって、位置の一致ごとに1つの行が生成されます。順序行番号にはこれが反映されます。
-
通常の列の指定では、列名の後にオプションの列のデータ型が続きます。これには、
json_value
のRETURNING
句に使用できる任意のSQLデータ型とそれに続くオプションの値句およびオプションのPATH
句を指定できます。デフォルトのデータ型はVARCHAR2(4000)
です。列のデータ型は、
JSON
、VARCHAR2
、NUMBER
、DATE
、TIMESTAMP
、TIMESTAMP WITH TIME ZONE
またはSDO_GEOMETRY
のいずれかにすることができます。データ型
SDO_GEOMETRY
は、Oracle Spatial and Graphデータ向けに使用されます。特に、これは、json_table
をGeoJSONデータで使用できることを意味します。このデータは、JSONで地理データをエンコーディングするための形式です。OracleではSQL/JSON標準を拡張し、列の戻りデータ型が
VARCHAR2(N)
の場合は、データ型の直後にオプションのキーワードTRUNCATE
を指定できるようにしました。TRUNCATE
が存在し、かつ戻り値がN
よりも長い場合は、値が切り捨てられ、先頭N
文字のみが戻されます。TRUNCATE
がない場合、このケースはエラーとして扱われ、エラー句またはデフォルトのエラー処理動作によって通常どおり処理されます。 -
ネストした列指定は、キーワード
NESTED
の後ろにオプションのPATH
キーワード、SQL/JSON行パス式、およびCOLUMNS
句が付いた形式で構成されます。このCOLUMNS
句は、ネストしたデータを表す列を指定します。ここで使用される行パス式により、指定したネストした列のコンテキストが洗練されます。ネストした各列のパス式は行パス式を基準にしたものになります。同じ行の列に異なるレベルで配列に存在する投影値に列の句をネストできます。COLUMNS
句は(ネストしていてもネストしていなくても)どのレベルでも同じ特性を持ちます。つまり、COLUMNS
句は再帰的に定義されます。ネストのレベルごとに(つまり、キーワードNESTED
が使用されるたびに)、ネストしたCOLUMNS
句は、ネスト元のCOLUMNS
句(その親)の子と呼ばれます。同じ親句を持つ複数のCOLUMNS
句は兄弟です。親子の
COLUMNS
句によって定義される仮想表は、外部結合を使用して結合されますが、この場合、親が外部表になります。兄弟のCOLUMNS
句によって定義される仮想列は、和
結合を使用して結合されます。
標準列指定に必要なものは、列名のみです。スカラー・データ型、値の処理またはターゲット・パスを指定することによる列の投影の詳細な定義はオプションです。
-
オプションの値句は、列に投影されたデータを処理する方法、つまり、データを
json_value
、json_exists
またはjson_query
と同じように処理するかどうかを指定します。この値処理には、戻りデータ型、戻り形式(prettyまたはASCII)、ラッパー、およびエラーの処理が含まれます。キーワード
EXISTS
を使用すると、投影されるデータは、json_exists
と同様に処理されます(列のデータ型に関係なく)。そうでない場合は、次のようになります。
-
JSON
データ型の列の場合、投影されるデータはjson_query
と同様に処理されます。 -
JSON
型以外の列(json_value
のRETURNING
句に使用できる任意の型)の場合、デフォルトでは、投影されるデータはjson_value
による処理と同様に処理されます。ただし、キーワードFORMAT JSON
を使用すると、json_query
によって処理されたかのように処理されます。投影されたデータがJSONオブジェクトまたは配列の場合は通常、FORMAT JSON
のみを使用します。(JSON
型の列にFORMAT JSON
を使用するとエラーが発生します。)
たとえば、ここで、列
FirstName
の値はjson_value
セマンティクスを使用して直接投影されており、列Address
の値はjson_query
セマンティクスを使用してJSON文字列として投影されます。COLUMNS (FirstName, Address FORMAT JSON)
json_query
のセマンティクスは、投影されるJSONデータが整形式であることを意味します。列がJSON
型以外の場合、これには、文字列値の非ASCII文字が必要に応じてエスケープされていることも含まれます。たとえば、タブ文字(CHARACTER TABULATION、U+0009)は\t
としてエスケープされます。(JSON
型のデータの場合、json_query
の使用時ではなく、JSON
データの作成時にそのようなエスケープが行われます。)列に
json_query
のセマンティクスがある場合:-
データベース初期化パラメータ
compatible
が少なくとも20
の場合は、キーワードDISALLOW SCALARS
を使用して、スカラーJSON値を除外することによって、json_query
の動作に影響を与えることができます。 -
明示的なラッパー句を追加することにより、デフォルトのラッパー動作をオーバーライドできます。
特定のハンドラ(
json_exists
、json_value
またはjson_query
)のデフォルトのエラー処理を、それに適した明示的なエラー句を追加することでオーバーライドできます。 -
-
オプションの
PATH
句は、列の内容として使用される行の部分を指定します。キーワードPATH
に続く列パス式は、仮想行によって提供されるコンテキスト項目と照合されます。この列パス式は、行パス式によって指定されるパスに対して相対的であるため、相対パスの表記にする必要があります。PATH
句が存在しない場合、動作は'$.<column-name>'
のパスで存在している場合と同様です。ここで、<column-name>
は列名です。つまり、対象となるオブジェクト・フィールドの名前が列名として暗黙的に取得されます。対象となるフィールドのみを指定するために、<column-name>
に使用されるSQL識別子は、引用符で囲まれていない場合でも大/小文字を区別して解釈されます。列のSQL名は通常のルールに従います。二重引用符("
)で囲まれている場合、使用される文字の大/小文字は区別されますが、そうでない場合、区別されません(大文字と同様に処理されます)。たとえば、これらの2つの
COLUMNS
句は等価です。SQLの場合、大/小文字が区別されるのは、列Comments
のみです。COLUMNS(ProductId, quantity NUMBER, "Comments") COLUMNS(ProductId VARCHAR2(4000) PATH '$.ProductId', quantity NUMBER PATH '$.quantity', "Comments" VARCHAR2(4000) PATH '$.Comments')
例19-1に、これを示す等価の問合せを示します。
SQL/JSONパス式のかわりに、
PATH
句でドット表記法を使用することもできます。例19-2および例19-9に、この例を示します。
列パス式配列ステップでは、索引と範囲の順序、配列索引の複数出現、および範囲の重複による指定された位置の重複は、列に使用される特定のセマンティクス(json_exists
、json_query
またはjson_value
)の場合と同様の効果があります。
-
json_exists
: 考慮されるのは、指定された位置のセットであり、指定された順序、回数を含む指定方法ではありません。チェックされるのは、少なくとも1つの指定された位置に一致が存在することです。 -
json_query
: 指定された各位置がデータと順番に照合されます。 -
json_value
: 指定された位置が1つのみの場合、データと照合されます。それ以外の場合は一致しません。デフォルト(NULL ON ERROR
)では、SQLのNULL
値が返されます。
関連項目
関連項目:
-
Oracle Spatial and Graphデータの使用の詳細は、『Oracle Spatial and Graph開発者ガイド』を参照してください。
親トピック: SQL/JSONファンクションJSON_TABLE
19.3 JSON_TABLEによるSQL/JSON問合せファンクションおよび条件の一般化
SQL/JSONファンクションjson_table
は、SQL/JSON条件json_exists
と、SQL/JSONファンクションjson_value
およびjson_query
を一般化します。これらの関数を使用して実行できる処理はすべて、json_table
を使用して実行できます。これらによって実行されるジョブについては、これらの関数の構文の方がjson_table
の構文よりも簡単に使用できます。
json_exists
、json_value
またはjson_query
を複数回使用して、またはこれらを組み合せて使用して同じデータにアクセスする場合、json_table
を1回呼び出す方が、データが解析されるのが1回のみであるという利点があります。
このため、オプティマイザがjson_exists
、json_value
およびjson_query
の複数の呼出し(任意の組合せ)を、データの解析が1回だけになるように、より少ないjson_table
の呼出しに自動的にリライトすることがよく起こります。
例19-4および例19-5に、この例を示します。これらではそれぞれ、列j_purchaseorder.po_document
内の各オブジェクトによって使用される要求者および一連の電話を選択しています。ただし、例19-5では、この列を4回ではなく1回のみ解析しています。
例19-5と関連して次の点に注意してください。
-
JSON値の
null
は、SQLに関するかぎりは1つの値であり、SQLで値の欠如(存在しないデータ、不明なデータまたは適用できないデータ)を表すNULL
とは異なります。例19-5で、オブジェクト属性zipCode
のJSON値がnull
である場合、SQL文字列'true'
が戻されます。 -
json_exists
はSQL条件です。これをSQLWHERE
句、CASE
文、またはチェック制約で使用できます。例19-4では、これをWHERE
句で使用しています。ファンクションjson_table
では、キーワードEXISTS
を指定したときに、暗黙的にjson_exists
のセマンティクスが使用されます。これは仮想列にSQL値を戻す必要があります。Oracle SQLにはブール・データ型がないため、SQL文字列'true'
または'false'
を使用してブール値を表します。これは例19-5の事例です。VARCHAR2
値は列jt.has_zip
に格納されてから、リテラルのSQL文字列'true'
と等価かどうかが明示的にテストされています。 -
JSONフィールド
AllowPartialShipment
には、JSONのブール値があります。json_value
はその値に適用される場合、これは文字列として戻されます。例19-5では、データ型VARCHAR2
は列のデータ型として使用されています。ファンクションjson_table
は、暗黙的にこの列に対してjson_value
を使用し、VARCHAR2
値として値を戻します。これにより、リテラルのSQL文字列'true'
と等価かどうかがテストされます。
例19-4 JSONデータに複数回アクセスすることによるデータの抽出
SELECT json_value(po_document, '$.Requestor' RETURNING VARCHAR2(32)),
json_query(po_document, '$.ShippingInstructions.Phone'
RETURNING VARCHAR2(100))
FROM j_purchaseorder
WHERE json_exists(po_document, '$.ShippingInstructions.Address.zipCode')
AND json_value(po_document, '$.AllowPartialShipment'
RETURNING VARCHAR2(5 CHAR))
= 'true';
例19-5 JSON_TABLEの使用によって複数回の解析が不要なデータの抽出
(JSONデータがJSON
データ型の場合は、キーワードFORMAT JSON
を使用しないでください。使用するとエラーが発生します。)
SELECT jt.requestor, jt.phones
FROM j_purchaseorder,
json_table(po_document, '$'
COLUMNS (
requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
phones VARCHAR2(100 CHAR) FORMAT JSON
PATH '$.ShippingInstructions.Phone',
partial VARCHAR2(5 CHAR) PATH '$.AllowPartialShipment',
has_zip VARCHAR2(5 CHAR) EXISTS
PATH '$.ShippingInstructions.Address.zipCode')) jt
WHERE jt.partial = 'true' AND jt.has_zip = 'true';
19.4 JSON_TABLEとJSON配列の使用
JSON値は、1つの配列にすることも、1つ以上の配列を含めることも可能であり、他のJSON配列またはオブジェクト内の任意の数のレベルにネストしてもかまいません。json_table
NESTED
パス句を使用して、配列の特定の要素を投影できます。
例19-6では、JSONデータ内の要求者および関連する電話番号を列po_document
内に投影しています。JSON配列Phone
全体がJSONデータph_arr
の列として投影されています。このJSONデータをVARCHAR2
列としてフォーマットするには、JSONデータがJSON
データ型ではない場合、キーワードFORMAT JSON
が必要となります(型がJSON
データの場合、このキーワードはエラーになります)。
JSON配列Phone
全体ではなく、配列の個別要素のみを投影する場合はどうすればよいでしょうか。例19-7は、これを行う方法の1つを示しており、投影する必要があるデータが配列要素のみである場合、この方法を使用できます。
要求者と関連する電話データの両方を投影する場合、例19-7の行パス式($.Phone[*]
)は適切ではありません。この式は、配列Phone
の(電話オブジェクト)要素のみを対象としています。
例19-8は、両方を対象とする方法の1つを示しています。ここでは、名前と電話配列全体の両方を対象とする行パス式を使用するとともに、個別電話オブジェクトのフィールドtype
およびnumber
を対象とする列パス式を使用しています。
例19-8では例19-6の場合のように、JSONデータがJSON
データ型でない場合にキーワードFORMAT JSON
が必要です。これは、結果のVARCHAR2
列にJSONデータ(つまり、電話ごとに1つの配列要素という形式の電話の種類または電話番号の配列)が含まれるためです。また、例19-6の事例とは異なり、列phone_type
およびphone_num
にラッパー句が必要ですが、これは、フィールドtype
およびnumber
を持つオブジェクトが配列Phone
に複数含まれるためです。
場合によっては、例19-8の効果が必要ないことがあります。たとえば、電話番号のJSON配列が含まれる列(特定の発注書のすべての番号に対して1つの行)ではなく、単一の電話番号が含まれるリレーショナル列(番号当たり1つの行)が必要な場合があります。
この結果を得るために、配列に対してjson_table
のNESTED
パス句を使用することにより、配列要素を投影するようjson_table
に命令を出す必要があります。NESTED
パス句は実質的に、追加の行ソース(行パターン)として機能します。例19-9に、これを示します。
キーワードNESTED
は1回のjson_table
の呼出しで何回でも使用できます。
例19-9では、外部のCOLUMNS
句は、ネストした(内部の) COLUMNS
句の親です。定義されている仮想表は外部結合を使用して結合されますが、この場合、親句によって定義される表が結合における外部表になります。
(同じ親の下で直接ネストされた2番目の列の句がある場合、これら2つのネストした句は兄弟のCOLUMNS
句になります。)
例19-6 JSON配列全体のJSONデータとしての投影
SELECT jt.*
FROM j_purchaseorder,
json_table(po_document, '$'
COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
ph_arr VARCHAR2(100 CHAR) FORMAT JSON
PATH '$.ShippingInstructions.Phone')) AS "JT";
例19-7 JSON配列の要素の投影
SELECT jt.*
FROM j_purchaseorder,
json_table(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (phone_type VARCHAR2(10) PATH '$.type',
phone_num VARCHAR2(20) PATH '$.number')) AS "JT";
PHONE_TYPE PHONE_NUM
---------- ---------
Office 909-555-7307
Mobile 415-555-1234
例19-8 JSON配列の要素と他のデータの投影
SELECT jt.*
FROM j_purchaseorder,
json_table(po_document, '$'
COLUMNS (
requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
phone_type VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
PATH '$.ShippingInstructions.Phone[*].type',
phone_num VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
PATH '$.ShippingInstructions.Phone[*].number')) AS "JT";
REQUESTOR PHONE_TYPE PHONE_NUM
--------- ---------- ---------
Alexis Bull ["Office", "Mobile"] ["909-555-7307", "415-555-1234"]
例19-9 JSON_TABLE: NESTEDを使用した配列要素の投影
この例では、配列要素を投影する2つの等価の問合せを示しています。最初の問合せでは、行および列データを対象とする式に単純なドット表記法構文を使用します。2番目の問合せでは、完全な構文を使用します。
SQL識別子が引用符で囲まれている列number
を除いて("number"
)、実際のSQL列名は大文字です。(列number
は小文字です。)
最初の問合せでは、列名の記述は、大/小文字の区別を含め、対象となるフィールド前と完全に同じです。引用符で囲まれるかどうかに関係なく、これらは、適切なパスを設定するために大/小文字を区別して解釈されます。
2番目の問合せには次のものが含まれています。
-
JSON列式およびSQL/JSON行パス式の別々の引数
-
VARCHAR2(4000)
の明示的な列データ型 -
投影されるオブジェクト・フィールドを対象とするための、明示的な
PATH
句およびSQL/JSON列パス式
SELECT jt.*
FROM j_purchaseorder po,
json_table(po.po_document
COLUMNS (Requestor,
NESTED ShippingInstructions.Phone[*]
COLUMNS (type, "number"))) AS "JT";
SELECT jt.*
FROM j_purchaseorder po,
json_table(po.po_document, '$'
COLUMNS (Requestor VARCHAR2(4000) PATH '$.Requestor',
NESTED
PATH '$.ShippingInstructions.Phone[*]'
COLUMNS (type VARCHAR2(4000) PATH '$.type',
"number" VARCHAR2(4000) PATH '$.number'))
) AS "JT";
19.5 JSON_TABLEを使用したJSONデータに対するビューの作成
問合せのパフォーマンスを向上させるために、SQL/JSONファンクションjson_table
を使用して、列に投影するJSONデータに対してビューを作成できます。問合せのパフォーマンスをさらに向上させるには、マテリアライズド・ビューを作成し、JSONデータをインメモリーに配置できます。
例19-10では、JSONデータに対してビューを定義しています。ここでは、NESTED
パス句を使用して配列LineItems
の要素を投影しています。
例19-11では、例19-10と同じデータおよび構造を持つマテリアライズド・ビューを定義しています。このようなマテリアライズド・ビューを直接更新することはできません。読取り専用のビューとして扱い、元表を更新することにより間接的に更新する必要があります。ビューを直接変更しようとすると、エラーが発生します。マテリアライズド・ビューはキーワードREFRESH
およびON STATEMENT
を使用して作成されている場合、元表を更新するたびにビューが自動的に更新されます。
例19-10と例19-11の違いは、次にあげるもののみです。
-
キーワード
MATERIALIZED
の使用 -
BUILD IMMEDIATE
の使用 -
REFRESH FAST ON STATEMENT WITH PRIMARY KEY
の使用。
REFRESH FAST
の使用は、マテリアライズド・ビューが増分的にリフレッシュされることを意味します。これが発生するようにするには、WITH PRIMARY KEY
またはWITH ROWID
(主キーがない場合)を使用する必要があります。表に基づいてマテリアライズド・ビューを作成する際には、JSON列が含まれるベースとなる表に主キーを指定し、WITH PRIMARY KEY
を使用することをお薦めします。
ビューの作成にON COMMIT
(ON STATEMENT
ではなく)を使用できます。前者は、表の更新トランザクションがコミットされた場合にのみ、元表を使用してビューを同期します。それまで表の変更はビューに反映されません。ON STATEMENT
を使用すると、ビューがDML文ごとにただちに同期されます。これは、ON STATEMENT
を使用して作成したビューでは、実行される可能性のあるロールバックを反映することを意味します。(後続のCOMMIT
文がトランザクションを終了し、ロールバックを回避します。)
例19-10 JSONデータに対するビューの作成
CREATE OR REPLACE VIEW j_purchaseorder_detail_view
AS SELECT jt.*
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',
costcenter VARCHAR2(16) PATH '$.CostCenter',
ship_to_name VARCHAR2(20 CHAR)
PATH '$.ShippingInstructions.name',
ship_to_street VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.street',
ship_to_city VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.city',
ship_to_county VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.county',
ship_to_postcode VARCHAR2(10 CHAR)
PATH '$.ShippingInstructions.Address.postcode',
ship_to_state VARCHAR2(2 CHAR)
PATH '$.ShippingInstructions.Address.state',
ship_to_zip VARCHAR2(8 CHAR)
PATH '$.ShippingInstructions.Address.zipCode',
ship_to_country VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.country',
ship_to_phone VARCHAR2(24 CHAR)
PATH '$.ShippingInstructions.Phone[0].number',
NESTED PATH '$.LineItems[*]'
COLUMNS (
itemno NUMBER(38) PATH '$.ItemNumber',
description VARCHAR2(256 CHAR) PATH '$.Part.Description',
upc_code VARCHAR2(14 CHAR) PATH '$.Part.UPCCode',
quantity NUMBER(12,4) PATH '$.Quantity',
unitprice NUMBER(14,2) PATH '$.Part.UnitPrice'))) jt;
例19-11 JSONデータに対するマテリアライズド・ビューの作成
CREATE OR REPLACE MATERIALIZED VIEW j_purchaseorder_materialized_view
BUILD IMMEDIATE
REFRESH FAST ON STATEMENT WITH PRIMARY KEY
AS SELECT jt.*
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',
costcenter VARCHAR2(16) PATH '$.CostCenter',
ship_to_name VARCHAR2(20 CHAR)
PATH '$.ShippingInstructions.name',
ship_to_street VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.street',
ship_to_city VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.city',
ship_to_county VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.county',
ship_to_postcode VARCHAR2(10 CHAR)
PATH '$.ShippingInstructions.Address.postcode',
ship_to_state VARCHAR2(2 CHAR)
PATH '$.ShippingInstructions.Address.state',
ship_to_zip VARCHAR2(8 CHAR)
PATH '$.ShippingInstructions.Address.zipCode',
ship_to_country VARCHAR2(32 CHAR)
PATH '$.ShippingInstructions.Address.country',
ship_to_phone VARCHAR2(24 CHAR)
PATH '$.ShippingInstructions.Phone[0].number',
NESTED PATH '$.LineItems[*]'
COLUMNS (
itemno NUMBER(38) PATH '$.ItemNumber',
description VARCHAR2(256 CHAR) PATH '$.Part.Description',
upc_code VARCHAR2(14 CHAR) PATH '$.Part.UPCCode',
quantity NUMBER(12,4) PATH '$.Quantity',
unitprice NUMBER(14,2) PATH '$.Part.UnitPrice'))) jt;