3.1 SQLを使用したカーレース二面性ビューの作成
カーレース・アプリケーション用のチーム、ドライバおよびレースの二面性ビューを、SQLを使用して作成します。
カーレース二面性ビューを定義するここでのSQL文では、SQL/JSON生成ファンクションを使用してJSONオブジェクトおよび配列を作成(生成)するための短縮形として、JSON型のコンストラクタ・ファンクションJSONを使用する簡略化された構文を使用します。JSON {…}はファンクションjson_objectを使用するための単純な構文で、JSON […]はファンクションjson_arrayまたはjson_arrayaggを使用するための単純な構文です。
JSON {…}およびJSON […]の構文は、他のこのような構文内に埋め込まれている場合に、{…}および[…]のように省略できます。これらは、包含するJSON生成ファンクションの一部であると認識されています。
生成ファンクションjson_objectの引数は、個々のJSONオブジェクト・メンバーの定義です。フィールド名(pointsなど)の後にコロン(:)またはキーワードISが続き、その後に定義フィールド値(110など)が続きます。つまり、'points' : 110または'points' IS 110となります。JSONフィールド名は一重引用符文字(')で囲まれることに注意してください。
フィールド値の一部は、ビューの上位レベルの表(ビューdriver_dvの表driver (別名d)、ビューrace_dvの表race (別名r)、ビューteam_dvの表team (別名t))から列値として直接定義されます。たとえば、ビューdriver_dvの場合、'name' : d.nameでは、driver表の列nameの値としてフィールドnameの値を定義します。
その他のフィールド値は、他の表のいずれかからデータを選択する副問合せ(SELECT ...)を使用して定義されます。そのデータは暗黙的に結合され、ビュー・データが形成されます。
一部の副問合せでは、JSON {…}という構文を使用します。この構文では、中カッコ({、})で囲まれた定義によって定義されたフィールドを使用してJSONオブジェクトを定義します。たとえば、JSON {'_id' : r.race_id, 'name' : r.name}は、表r (race)から、列race_idおよびnameの値によってそれぞれ定義されたフィールド_idおよびnameを使用してJSONオブジェクトを定義します。
その他の副問合せでは、JSON […]という構文を使用します。この構文では、副問合せが返す値を持つ要素のJSON配列を、返される順序で定義します。たとえば、[ SELECT JSON {…} FROM driver WHERE ... ]は、指定されたWHERE条件が保持される表driverから要素を選択するJSON配列を定義します。
二面性ビューdriver_dvおよびrace_dvには、マッピング表driver_race_mapの各ネスト・データがあります。これらの各ビューの2つのバージョンが定義され、そのうちの1つにはネストされたオブジェクトが含まれ、もう1つはキーワードUNNESTを使用して定義され、ネストされたオブジェクトが直接そのフィールドを組み込むようにフラット化されます。ビューdriver_dvの場合、ネストされたオブジェクトはフィールドteamInfoの値です。ビューrace_dvの場合、ネストされたオブジェクトはフィールドdriverInfoの値です。(必要に応じて、キーワードNESTを使用して、ネストのデフォルト動作を明示的に指定できます。)
このドキュメントのほとんどのカーレースの例では、これらのネストされたオブジェクトなしでビューおよびドキュメント・バージョンを使用します。
ネストは、ルート表以外の表のフィールドに対するデフォルトの動作です。ネスト解除は、ルート表のフィールドに対するデフォルトの動作です。デフォルトの動作を明示的にする場合は、キーワードNESTを使用します。例については、例9-1を参照してください。なお、ドキュメント識別子フィールド_idはネストできません。これは、ルート表の識別列に対応付けられています。試みるとエラーが発生します。
例3-1 SQLを使用した二面性ビューTEAM_DVの作成
この例では、チーム・オブジェクトが次のように表示される二面性ビューを作成します。チーム・オブジェクトには、値がチームのドライバを指定するネストされたオブジェクトの配列であるフィールドdriverが含まれています。
{"_id" : 301, "name" : "Red Bull", "points" : 0, "driver" : [...]}(作成されたビューは、例3-6のGraphQLを使用して作成されたビューと同じです。)
CREATE JSON RELATIONAL DUALITY VIEW team_dv AS
SELECT JSON {'_id' : t.team_id,
'name' : t.name,
'points' : t.points,
'driver' :
[ SELECT JSON {'driverId' : d.driver_id,
'name' : d.name,
'points' : d.points WITH NOCHECK}
FROM driver d WITH INSERT UPDATE
WHERE d.team_id = t.team_id ]}
FROM team t WITH INSERT UPDATE DELETE;
例3-2 SQLを使用した、ネストされたチーム情報での二面性ビューDRIVER_DVの作成
この例では、二面性ビューを作成し、ここでドライバ・オブジェクトは次のようになります。ドライバ・オブジェクトには、値がフィールドteamIdおよび(チーム) nameを持つネストされたオブジェクトであるフィールドteamInfoが含まれています。
{"_id" : 101,
"name" : "Max Verstappen",
"points" : 0,
"teamInfo" : {"teamId" : 103, "name" : "Red Bull"},
"race" : [...]}CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'_id' : d.driver_id,
'name' : d.name,
'points' : d.points,
'teamInfo' :
(SELECT JSON {'teamId' : t.team_id,
'name' : t.name WITH NOCHECK}
FROM team t WITH NOINSERT NOUPDATE NODELETE
WHERE t.team_id = d.team_id),
'race' :
[ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
'raceInfo' :
(SELECT JSON {'raceId' : r.race_id,
'name' : r.name}
FROM race r WITH NOINSERT NOUPDATE NODELETE
WHERE r.race_id = drm.race_id),
'finalPosition' : drm.position}
FROM driver_race_map drm WITH INSERT UPDATE NODELETE
WHERE drm.driver_id = d.driver_id ]}
FROM driver d WITH INSERT UPDATE DELETE;例3-3 SQLを使用した、ネストされていないチーム情報での二面性ビューDRIVER_DVの作成
この例では、二面性ビューを作成し、ここでドライバ・オブジェクトは次のようになります。ドライバ・オブジェクトには、値がフィールドteamIdおよびnameを持つネストされたオブジェクトであるフィールドteamInfoが含まれていません。かわりに、表teamのデータが上位レベルで取り込まれ、チーム名がフィールドteamとなります。
{"_id" : 101,
"name" : "Max Verstappen",
"points" : 0,
"teamId" : 103,
"team" : "Red Bull",
"race" : [...]}'teamInfo' :を使用して、表teamの副問合せから生成されるオブジェクト値で上位レベルのフィールドteamInfoを定義するかわりに、ビュー定義はその副問合せの前にキーワードUNNESTを付け、列nameのデータをフィールドteamの値として使用します。その他の点では、このビュー定義は例3-2と同じです。
(作成されたビューは、例3-7のGraphQLを使用して作成されたビューと同じです。)
CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'_id' : d.driver_id,
'name' : d.name,
'points' : d.points,
UNNEST
(SELECT JSON {'teamId' : t.team_id,
'team' : t.name WITH NOCHECK}
FROM team t WITH NOINSERT NOUPDATE NODELETE
WHERE t.team_id = d.team_id),
'race' :
[ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
UNNEST
(SELECT JSON {'raceId' : r.race_id,
'name' : r.name}
FROM race r WITH NOINSERT NOUPDATE NODELETE
WHERE r.race_id = drm.race_id),
'finalPosition' : drm.position}
FROM driver_race_map drm WITH INSERT UPDATE NODELETE
WHERE drm.driver_id = d.driver_id ]}
FROM driver d WITH INSERT UPDATE DELETE;なお、なんらかの理由でルート表driverのフィールド(_id以外)をネストされたオブジェクトに配置する必要がある場合は、そのようにできます。たとえば、このコードでは、フィールドnameおよびpointsがdriverInfoオブジェクトにネストされています。オプションで、フィールドdriverInfoの前にキーワードNESTを使用してネストのデフォルト動作をより明示的に指定できます。
CREATE JSON RELATIONAL DUALITY VIEW driver_dv AS
SELECT JSON {'_id' : d.driver_id,
'driverInfo' : {'name' : d.name,
'points' : d.points},
UNNEST (SELECT JSON {...}),
'race' : ...}
FROM driver d;例3-4 SQLを使用した、ネストされたドライバ情報での二面性ビューRACE_DVの作成
この例では、二面性ビューを作成し、ここでは配列resultの要素であるオブジェクトが次のようになります。これには、値がフィールドdriverIdおよびnameを持つネストされたオブジェクトであるフィールドdriverInfoが含まれています。
{"driverRaceMapId" : 3,
"position" : 1,
"driverInfo" : {"driverId" : 103, "name" : "Charles Leclerc"}}CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
SELECT JSON {'_id' : r.race_id,
'name' : r.name,
'laps' : r.laps WITH NOUPDATE,
'date' : r.race_date,
'podium' : r.podium WITH NOCHECK,
'result' :
[ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
'position' : drm.position,
'driverInfo' :
(SELECT JSON {'driverId' : d.driver_id,
'name' : d.name}
FROM driver d WITH NOINSERT UPDATE NODELETE
WHERE d.driver_id = drm.driver_id)}
FROM driver_race_map drm WITH INSERT UPDATE DELETE
WHERE drm.race_id = r.race_id ]}
FROM race r WITH INSERT UPDATE DELETE;例3-5 SQLを使用した、ネストされていないドライバ情報での二面性ビューRACE_DVの作成
この例では、二面性ビューを作成し、ここでは配列resultの要素であるオブジェクトが次のようになります。これには、値がフィールドdriverIdおよびnameを持つネストされたオブジェクトであるフィールドdriverInfoが含まれていません。
{"driverId" : 103, "name" : "Charles Leclerc", "position" : 1}'driverInfo' :を使用して、表driverの副問合せから生成されるオブジェクト値で上位レベルのフィールドdriverInfoを定義するかわりに、ビュー定義はその副問合せの前にキーワードUNNESTを付けます。その他の点では、このビュー定義は例3-4と同じです。
(作成されたビューは、例3-8のGraphQLを使用して作成されたビューと同じです。)
CREATE JSON RELATIONAL DUALITY VIEW race_dv AS
SELECT JSON {'_id' : r.race_id,
'name' : r.name,
'laps' : r.laps WITH NOUPDATE,
'date' : r.race_date,
'podium' : r.podium WITH NOCHECK,
'result' :
[ SELECT JSON {'driverRaceMapId' : drm.driver_race_map_id,
'position' : drm.position,
UNNEST
(SELECT JSON {'driverId' : d.driver_id,
'name' : d.name}
FROM driver d WITH NOINSERT UPDATE NODELETE
WHERE d.driver_id = drm.driver_id)}
FROM driver_race_map drm WITH INSERT UPDATE DELETE
WHERE drm.race_id = r.race_id ]}
FROM race r WITH INSERT UPDATE DELETE;関連項目:
『Oracle Database SQL言語リファレンス』のCREATE JSON RELATIONAL DUALITY VIEWに関する項
親トピック: 二面性ビューの作成