Autonomous Database：Object Storage上にあるJSONファイルに対してJSONサーチ・インデックスを作成して全文検索してみた

# はじめに
Autonomous Databaseのアップデートで、クラウド・ストレージ上のJSONファイルに対してJSONサーチ・インデックスを作成し、クラウド・ストレージ上のJSONファイルに対しての全文検索、非定型検索を実行可能にする機能が追加されたので、早速検証してみました。

JSONサーチ・インデックスに関しての詳細は、こちらのマニュアルをご参照ください。

https://docs.oracle.com/cd/F19136_01/adjsn/indexes-for-json-data.html#GUID-D7A604E1-F617-4C92-A9D9-00B6AB78EFD8

今回は、OCI Object Storage上にあるJSONファイルに対してJSONサーチ・インデックスを作成して全文検索、非定型検索を実行してみました。

# 1.事前準備

今回は、コース（講義）の情報のJSONドキュメントを含むファイルcourse_data.jsonを使用しました。
各コース情報のJSONドキュメントには、コース番号、コース名、受講する学生の情報、担当する講師の情報などが含まれています。

```json:course_data.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のバケットにアップロードします。

![スクリーンショット 2024-07-12 12.59.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/2ac65d30-de0b-402a-ba15-9a48327a3c1f.png)

![スクリーンショット 2024-07-12 13.00.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/6888bad1-6492-4cef-c19e-7fe4951b59ad.png)

また、今回はAutonomous DatabaseからOCI Object Storageにアクセスするためのクレデンシャルとしてリソース・プリンシパルを使用するので、リソース・プリンシパルの設定を実施しました。

リソース・プリンシパルの設定に関する詳細は、こちらのマニュアルをご参照ください。

https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/serverless/adbsb/resource-principal.html

# 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のオプションの詳細は、こちらのマニュアルをご参照ください。

https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html#GUID-F5559F63-EDEB-4138-A0A4-BE9914B2BDE4

ここでは、バケット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](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/use-json-textcontains.html#GUID-F1E58A88-13B1-4F8E-9EE2-3C982F5AF0E6)
・[リソース・プリンシパルを使用したOracle Cloud Infrastructureリソースへのアクセス](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/serverless/adbsb/resource-principal.html)
・[DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXプロシージャ](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html#GUID-F5559F63-EDEB-4138-A0A4-BE9914B2BDE4)
・[JSON検索索引: 非定型の問合せおよび全文検索](https://docs.oracle.com/cd/F19136_01/adjsn/indexes-for-json-data.html#GUID-D7A604E1-F617-4C92-A9D9-00B6AB78EFD8)