はじめに
Autonomous Databaseのアップデートで、クラウド・ストレージ上のJSONファイルに対してJSONサーチ・インデックスを作成し、クラウド・ストレージ上のJSONファイルに対しての全文検索、非定型検索を実行可能にする機能が追加されたので、早速検証してみました。
JSONサーチ・インデックスに関しての詳細は、こちらのマニュアルをご参照ください。
今回は、OCI Object Storage上にあるJSONファイルに対してJSONサーチ・インデックスを作成して全文検索、非定型検索を実行してみました。
1.事前準備
今回は、コース（講義）の情報のJSONドキュメントを含むファイルcourse_data.jsonを使用しました。
各コース情報のJSONドキュメントには、コース番号、コース名、受講する学生の情報、担当する講師の情報などが含まれています。
{"courseId":"MATH101","name":"Algebra","creditHours":3,"students":[{"studentId":1,"name":"Donald P."},{"studentId":5,"name":"Hye E."}],"teacher":{"teacherId":101,"name":"Abdul J."},"Notes":"Prerequisite for Advanced Algebra"}
{"courseId":"MATH102","name":"Calculus","creditHours":4,"students":[{"studentId":2,"name":"Elena H."},{"studentId":10,"name":"Ming L."},{"studentId":9,"name":"Luis F."},{"studentId":4,"name":"Georgia D."}],"teacher":{"teacherId":101,"name":"Abdul J."}}
{"courseId":"CS101","name":"Algorithms","creditHours":5,"students":[{"studentId":1,"name":"Donald P."},{"studentId":2,"name":"Elena H."},{"studentId":4,"name":"Georgia D."},{"studentId":9,"name":"Luis F."},{"studentId":7,"name":"Jatin S."}],"teacher":{"teacherId":102,"name":"Betty Z."}}
{"courseId":"CS102","name":"Data Structures","creditHours":3,"students":[{"studentId":1,"name":"Donald P."},{"studentId":2,"name":"Elena H."},{"studentId":5,"name":"Hye E."},{"studentId":7,"name":"Jatin S."},{"studentId":8,"name":"Katie H."}],"teacher":{"teacherId":102,"name":"Betty Z."}}
{"courseId":"MATH103","name":"Advanced Algebra","creditHours":3,"students":[{"studentId":3,"name":"Francis K."},{"studentId":4,"name":"Georgia D."},{"studentId":8,"name":"Katie H."},{"studentId":9,"name":"Luis F."},{"studentId":6,"name":"Ileana D."}],"teacher":{"teacherId":103,"name":"Colin J."}}
course_data.jsonをObject Storageのバケットにアップロードします。
また、今回はAutonomous DatabaseからOCI Object Storageにアクセスするためのクレデンシャルとしてリソース・プリンシパルを使用するので、リソース・プリンシパルの設定を実施しました。
リソース・プリンシパルの設定に関する詳細は、こちらのマニュアルをご参照ください。
2. Object Storage上にあるJSONファイルに対するJSONサーチ・インデックスの作成
Object Storage上にあるJSONファイルに対して、DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXプロシージャを使用してJSONサーチ・インデックスを作成します。
指定するパラメータは以下のとおりです。
credential_name：Object Storageにアクセスするためのクレデンシャルを指定
location_uri：Object StorageのバケットまたはオブジェクトのURIを指定
index_name：作成するJSONサーチ・インデックスの名前
format：JSON形式で各種オプションを指定、JSONサーチ・インデックスを作成する場合は、'json_index'にtrueをセットする
formatのオプションの詳細は、こちらのマニュアルをご参照ください。
ここでは、バケットcourse内のJSONファイルに対して、COURSE_INDEXという名前のJSONサーチ・インデックスを作成しています。
また、Object Storageのバケットにアクセスする際に使用するクレデンシャルとして、OCI$RESOURCE_PRINCIPALを指定しています。
SQL> BEGIN
2 DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
3 credential_name => 'OCI$RESOURCE_PRINCIPAL',
4 location_uri => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/course/o/',
5 index_name => 'COURSE_INDEX',
6 format => JSON_OBJECT ('json_index' value 'true', 'refresh_rate' value 5 )
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
JSONサーチ・インデックスの作成はスケジューラ・ジョブとして実行されます。
JSONサーチ・インデックスの作成には、バケット内のJSONファイルの大きさ、数に応じて時間がかかります。
all_scheduler_job_run_detailsビューで、スケジューラ・ジョブの状況を確認できます。
SQL> col job_name for a20
SQL> col run_duration for a20
SQL> col status for a10
SQL> SELECT job_name, run_duration, status
2 FROM all_scheduler_job_run_details
3 WHERE LOWER(job_name) = LOWER('COURSE_INDEX$JOB');
JOB_NAME RUN_DURATION STATUS
-------------------- -------------------- ----------
COURSE_INDEX$JOB +000 00:00:08 SUCCEEDED
SQL>
インデックス名と同じ名前のJSONサーチ・インデックス参照ビューが作成されます。
SQL> SELECT view_name FROM user_views
2 WHERE view_name = 'COURSE_INDEX';
VIEW_NAME
------------------------------------------------
COURSE_INDEX
SQL>
JSONサーチ・インデックス参照ビューはこちらのような構造になっています。
SQL> desc course_index
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(4000)
OBJECT_PATH VARCHAR2(4000)
FILE_LINE_JSON CLOB
MTIME TIMESTAMP(6)
DATA CLOB
SQL>
各列の詳細はこちらの表のとおりです。
| 列名 | 詳細 |
|---|---|
| OBJECT_NAME | 検索されたテキスト文字列を含む Object Storage 上のファイル名 |
| OBJECT_PATH | OBJECT_NAMEのオフジェクトを含むクラウド・ストレージのバケットまたはフォルダの URI |
| LENGTH | JSONドキュメントの長さ |
| OFFSET | ファイルの先頭のバイトオフセット |
| DATA | クラウド・ストレージ上のファイルの内容 |
| FILE_LINE_JSON | JSON_TEXTCONTAINSの実行対象となるカラム |
| MTIME | クラウド・ストレージ上のファイルの最終更新タイムスタンプ |
SQL条件JSON_TEXTCONTAINSを使用してJSONサーチ・インデックスと同名の参照ビューにクエリを実行すると、data列にデータが取り込まれます。
3. JSONサーチ・インデックスの利用
作成したJSONサーチ・インデックスを使用して、Object Storageバケット内にあるJSONファイルに対して、クエリを実行してみます。
こちらのSQLでは、名前（$.teacher.name）が'Betty Z.'の講師が担当しているコース情報（JSONドキュメント）を抽出しています。
SQL> SELECT JSON_QUERY (data, '$' returning CLOB pretty) AS course_data
2 FROM course_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.teacher.name', 'Betty Z.');
COURSE_DATA
--------------------------------------------------------------------------------
{
"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 9,
"name" : "Luis F."
},
{
"studentId" : 7,
"name" : "Jatin S."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
{
"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 5,
"name" : "Hye E."
},
{
"studentId" : 7,
"name" : "Jatin S."
},
{
"studentId" : 8,
"name" : "Katie H."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
SQL>
こちらのSQLでは、講師ID（$.teacher.teacherId）が103の講師が担当しているコース情報（JSONドキュメント）を抽出しています。
SQL> SELECT JSON_QUERY (data, '$' returning CLOB pretty) AS course_data
2 FROM course_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.teacher.teacherId', '103');
COURSE_DATA
--------------------------------------------------------------------------------
{
"courseId" : "MATH103",
"name" : "Advanced Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 3,
"name" : "Francis K."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 8,
"name" : "Katie H."
},
{
"studentId" : 9,
"name" : "Luis F."
},
{
"studentId" : 6,
"name" : "Ileana D."
}
],
"teacher" :
{
"teacherId" : 103,
"name" : "Colin J."
}
}
SQL>
こちらのSQLでは、名前（student.name）に'Donald'を含む学生が受講しているコース情報（JSONドキュメント）を抽出しています。
SQL> SELECT JSON_QUERY (data, '$' returning CLOB pretty) AS course_data
2 FROM course_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.students.name', 'Donald');
COURSE_DATA
--------------------------------------------------------------------------------
{
"courseId" : "MATH101",
"name" : "Algebra",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 5,
"name" : "Hye E."
}
],
"teacher" :
{
"teacherId" : 101,
"name" : "Abdul J."
},
"Notes" : "Prerequisite for Advanced Algebra"
}
{
"courseId" : "CS101",
"name" : "Algorithms",
"creditHours" : 5,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 4,
"name" : "Georgia D."
},
{
"studentId" : 9,
"name" : "Luis F."
},
{
"studentId" : 7,
"name" : "Jatin S."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
{
"courseId" : "CS102",
"name" : "Data Structures",
"creditHours" : 3,
"students" :
[
{
"studentId" : 1,
"name" : "Donald P."
},
{
"studentId" : 2,
"name" : "Elena H."
},
{
"studentId" : 5,
"name" : "Hye E."
},
{
"studentId" : 7,
"name" : "Jatin S."
},
{
"studentId" : 8,
"name" : "Katie H."
}
],
"teacher" :
{
"teacherId" : 102,
"name" : "Betty Z."
}
}
SQL>
こちらのSQLでは、学生ID（$.students.studentId）が2の学生が受講しているコース名のみを抽出しています。
SQL> col course_name for a20
SQL> SELECT JSON_VALUE (data, '$.name' returning VARCHAR2) AS course_name
2 FROM course_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.students.studentId', '2');
COURSE_NAME
--------------------
Calculus
Algorithms
Data Structures
SQL>
こちらのSQLでは、学生ID（$.students.studentId）が2の学生が受講しているコース名、その情報を含むJSONドキュメントが格納されているファイル名（オブジェクト名）およびそのパスを抽出しています。
SQL> col course_name for a20
SQL> col object_name for a20
SQL> col object_path for a80
SQL> SELECT JSON_VALUE (data, '$.name' returning VARCHAR2) AS course_name, object_name, object_path
2 FROM course_index WHERE JSON_TEXTCONTAINS(file_line_json, '$.students.studentId', '2');
COURSE_NAME OBJECT_NAME OBJECT_PATH
-------------------- -------------------- --------------------------------------------------------------------------------
Calculus course_data.json https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/course/o/
Algorithms course_data.json https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/course/o/
Data Structures course_data.json https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/course/o/
SQL>
まとめ
OCI Object Storage上にあるJSONファイルに対してJSONサーチ・インデックスを作成して、JSONデータに対するテキスト検索が簡単に行えることが確認できました。
参考情報
・Use JSON Search on Documents in Object Storage
・リソース・プリンシパルを使用したOracle Cloud Infrastructureリソースへのアクセス
・DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXプロシージャ
・JSON検索索引: 非定型の問合せおよび全文検索