SQL を用いた JSON 全文検索

![](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/410176/4012bcb5-7118-0ace-ba85-bc28a15e809e.png)

## 概要

SQL の JSON 関数 `JSON_TEXTCONTAINS` を用いると、任意の文字列が含まれている JSON データを検索することができます。

```sql
SELECT COUNT(*)
FROM wiki_en
WHERE JSON_TEXTCONTAINS(content_json, '$.text', 'データベース');
```

Oracle Database の場合には、次のような簡単な構文で JSON 列に対する全文索引を作成することができます。
```sql
CREATE SEARCH INDEX wiki_search_idx ON wiki (jsondata);
```

この全文索引が利用できると、RDBMS で大きなテキストデータを含む JSON を扱いやすくなるため便利ですが、一方で、データ量に対して性能や索引サイズが十分かわかっていないため、日本語 Wikipedia のデータを使って簡単に検証したいと思います。

日本語 Wikipedia の JSON データを作成して、データベースにロードするためのスクリプトとその解説は [こちらの記事](https://qiita.com/xkzm/items/4f176093422a0bb7c58b) に記載しています。

## Oracle Database の準備

無料版の [Oracle Database 23ai Free](https://www.oracle.com/database/free/) を利用しようと考えましたが、全文索引のサイズを含めるとユーザーデータの合計が 12GB の制限を超えてしまいました。そのため、以下の Oracle Cloud 環境を使用しました。

* Oracle Base Database Service
  * DB Version: 23.4.1.24.06 (23ai)
  * Edition: Standard Edition
  * Shape: VM.Standard.E5.Flex
  * CPU Core Count: 1

sys でログインして、一般ユーザー myuser を作成します。
```
sqlplus sys/<password>@<ip_address>:1521/<service_name> as sysdba
```
```sql:sys
CREATE USER myuser IDENTIFIED BY <password> QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO myuser;
```

myuser でログインできることを確かめておきます。
```
sqlplus myuser/<password>@<ip_address>:1521/<service_name>
```

## Wikipedia データのインポート

今回用いる wiki 表のダンプ（約 3.8 GB）をこちらに公開しています。2024年5月1日時点の日本語 Wikipedia データ（2,289,280 件）を JSON 列に格納しています。

* https://objectstorage.ap-osaka-1.oraclecloud.com/n/ax2juefahle0/b/download/o/wiki.dmp

OS ディレクトリを作成しておきます。
```
sudo mkdir /u01/dir01
sudo chown oracle:oinstall /u01/dir01/
```

ダンプファイルをダウンロードします。
```
sudo su - oracle
cd /u01/dir01/
wget https://objectstorage.ap-osaka-1.oraclecloud.com/n/ax2juefahle0/b/download/o/wiki.dmp
```

sys でログインして、myuser にディレクトリ作成権限を付与します。
```sql:sys
GRANT create any directory, drop any directory TO myuser;
```

myuser でログインして、ディレクトリを作成します。
```sql:myuser
CREATE DIRECTORY dir01 AS '/u01/dir01';
```

インポート用のコマンド impdp を使って表をインポートします。

```
impdp myuser/<password>@<ip_address>:1521/<service_name> \
tables=myuser.wiki directory=dir01 dumpfile=wiki.dmp logfile=wiki.log
```
```
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MYUSER"."WIKI"                              3.8 GB 2289280 rows
```

表には JSON データを格納する列のみが含まれています。
```sql:myuser
SELECT * FROM wiki FETCH FIRST 3 ROWS ONLY;
```
```
JSONDATA
--------------------------------------------------------------------------------
{"id":"60","revid":"2160598","url":"https://ja.wikipedia.org/wiki?curid=60","tit
{"id":"62","revid":"1788533","url":"https://ja.wikipedia.org/wiki?curid=62","tit
{"id":"63","revid":"516649","url":"https://ja.wikipedia.org/wiki?curid=63","titl
```

## 全文索引の作成

全文索引の作成には一時表領域を使用するため、一時表領域が十分に拡張できるよう、データファイルを追加しておきます。

sys でログインして、データファイルをひとつ追加します。
```sql:sys
ALTER TABLESPACE temp ADD TEMPFILE '+DATA';
```

myuser でログインして、全文索引を作成します。この環境では 15 分程度かかりました。
```sql:myuser
CREATE SEARCH INDEX wiki_search_idx ON wiki (jsondata);
```
```
INDEX WIKI_SEARCH_IDX created.
Elapsed: 00:15:08.640
```

sys でログインして、セグメントのサイズを確認します。
```sql:sys
SELECT
  SEGMENT_NAME
, SEGMENT_TYPE
, BYTES/1024/1024 AS MB
FROM DBA_SEGMENTS
WHERE segment_name LIKE '%WIKI%'
ORDER BY mb DESC;
```
```
SEGMENT_NAME		      SEGMENT_TYPE     MB
------------------------- ------------ ------
DR$WIKI_SEARCH_IDX$I	  TABLE 	     8758
DR$WIKI_SEARCH_IDX$X	  INDEX 	     5991
WIKI			          TABLE 	     3232
DR$WIKI_SEARCH_IDX$SN	  TABLE 	      316
DR$WIKI_SEARCH_IDX$SNI	  INDEX 	      216
DR$WIKI_SEARCH_IDX$KR	  INDEX 	       72
DR$WIKI_SEARCH_IDX$KD	  INDEX 	       72
DR$WIKI_SEARCH_IDX$K	  TABLE 	       54
DR$WIKI_SEARCH_IDX$H	  INDEX 	    .0625
DR$WIKI_SEARCH_IDX$STI	  INDEX 	    .0625
DR$WIKI_SEARCH_IDX$UI	  INDEX 	    .0625
DR$WIKI_SEARCH_IDX$NI	  INDEX 	    .0625
DR$WIKI_SEARCH_IDX$ST	  TABLE 	    .0625
DR$WIKI_SEARCH_IDX$B	  TABLE 	    .0625
DR$WIKI_SEARCH_IDX$C	  TABLE 	    .0625
DR$WIKI_SEARCH_IDX$Q	  TABLE 	    .0625
DR$WIKI_SEARCH_IDX$U	  TABLE 	    .0625
DR$WIKI_SEARCH_IDX$N	  TABLE 	    .0625
DR$WIKI_SEARCH_IDX$G	  TABLE 	    .0625
```

`DR$WIKI_SERACH_IDX$..` という名前の表と索引が内部的に作成されていて、3.2 GB 程度の wiki 表に対して、数倍のデータ容量が必要になることがわかります。

## JSON 内の全文検索

`JSON_TEXTCONTAINS` 関数を用いて、本文に「データベース」という単語を含む Wikipedia ページの数をカウントしてみると、0.1 秒以内に実行できることがわかります。

```sql
SELECT COUNT(*)
FROM wiki
WHERE JSON_TEXTCONTAINS(jsondata, '$.text', 'データベース');
```
```
  COUNT(*)
----------
       288

Elapsed: 00:00:00.028
```

試しに索引を使わないようにヒントをつけてみると実行時間は 26 秒になりました。

```sql
SELECT /*+ NO_INDEX(wiki wiki_search_idx) */ COUNT(*)
FROM wiki
WHERE JSON_TEXTCONTAINS(jsondata, '$.text', 'データベース');
```
```
  COUNT(*)
----------
       288

Elapsed: 00:00:26.320
```

JSON の値の内容を出力するときには `JSON_VALUE` 関数を用います。text 要素に含まれる長い文字列は CLOB にキャストします。
```sql
SELECT JSON_VALUE(jsondata, '$.id') AS id
     , JSON_VALUE(jsondata, '$.title') AS title
     , JSON_VALUE(jsondata, '$.text' RETURNING CLOB) AS text
FROM wiki
WHERE JSON_TEXTCONTAINS(jsondata, '$.text', 'データベース')
FETCH FIRST 3 ROWS ONLY;
```
```
ID      TITLE                 TEXT
------- --------------------- -------------------------------------------------------------------------------------------------------------------------
63      情報工学                情報工学（じょうほうこうがく、）とは、「計算機による情報処理に関連する科学技術の一分野」を指す言葉であり、「情報科学」や「計算機科学」ともいう。なお英語の は
1297    パーソナルコンピュータ    パーソナルコンピュータ（）は、個人によって占有されて使用されるコンピュータのことである。略称はパソコンまたはPC（ピーシー）。ただし「PC」という略称は、特に
1244    バイオインフォマティクス   バイオインフォマティクス（英語：bioinformatics）とは、生命科学と情報科学の融合分野のひとつであり、DNAやRNA、タンパク質をはじめとする、生命が

Elapsed: 00:00:00.051
```

以上の通り、200 万件以上の日本語 JSON データに対して、全文索引が有効であることを確認できました。索引のデータサイズは大きいため注意が必要ですが、JSON の内容と表のメタデータを用いて検索する場合などに便利そうです。

* 参考：[Wikipedia データをデータベースに格納する](https://qiita.com/xkzm/items/4f176093422a0bb7c58b)