JSON/JSONBと全文検索

# はじめに
にゃーん
この記事は、[PostgreSQL 10全部ぬこ Advent Calendar 2017](https://qiita.com/advent-calendar/2017/pg10-nuko-only) の12日目のエントリです。

# JSON/JSONBとは
[JSON](https://ja.wikipedia.org/wiki/JavaScript_Object_Notation)を知らない人はたぶんいないとは思うけど、PostgreSQLではビルトインのデータタイプとして、JSON型をPostgreSQL 9.2からサポートしていた。
また、PostgreSQL 9.4からは検索時の効率を良くするバイナリ形式で格納する、JSONB型がサポートされた。

|バージョン|主な追加機能|
|:--|:--|
|9.2|JSON型を導入。格納時のパース程度しかやってない。|
|9.3|JSON型に対する関数・演算子の大幅な追加。|
|9.4|バイナリJSON型(JSONB型)の追加。|
|9.5|jsonb_set(), jsonb_pretty()の追加。|
|9.6|jsonb_insert()、hstoreからの変換|
|10|全文検索対応、"-"演算子など|

# PostgreSQL全文検索
PostgreSQLで扱える全文検索機能として、現状以下のようなものがある。

|名称|分類|検索方式|日本語対応|メモ|
|:--|:--|:--|:-:|:--|
|textsearch|本体機能|形態素|✗|PostgreSQL本体機能|
|pg_trgm|contrib|N-gram|△|2文字以下の語で検索すると効率悪い|
|[pg_bigm](http://pgbigm.osdn.jp/)|外部製品|N-gram|○|2文字以下の語の検索に強い|
|textsearch_ja|外部製品|形態素|○|Mecab依存。現状、公式のメンテナがいない？|
|[pgroonga](https://pgroonga.github.io/ja/)|外部製品|形態素,N-gram|○|高機能・高速が売り。groongaのインストールが必要。|

今回は、一番上の本体機能のtextsearchとJSON/JSONBの組合せで検証した。

## 検証モデル

こんなJSONデータを用意しておく。

```json
{"name":"Work, Melos!", "authors":["Osamu Dasai"], "price":4.80}
{"name":"What's your name?", "authors":["Makoto Shinkai"], "price":6.00}
{"name":"PostgreSQL Administration is tired", "authors":["nuko_yokohama", "kingtomo", "masa_sae"], "price":34.00}
{"name":"Interview with Authors!", "authors":["hogehoge editors"], "price":8.3}
{"name":"PostgreSQL is wonderful", "authors":["nuko_yokohama"], "price":18.5}
```

で、以下の3つのテーブルに対して上記データをCOPY文でロードする。

```postgres-console
ts=# \d books*
             Table "public.books_j"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 data   | json |           |          |

             Table "public.books_jb"
 Column | Type  | Collation | Nullable | Default
--------+-------+-----------+----------+---------
 data   | jsonb |           |          |

             Table "public.books_t"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 data   | text |           |          |
```


## name を含むデータを全文検索する
books_t(テキスト型)、books_j(JSON型)、books_jb(JSONB型)のテーブルに対して、以下のようなクエリをそれぞれ実行してみる。

```postgres
SELECT data FROM  <テーブル名> WHERE to_tsvector(data) @@ to_tsquery('name');
```

* to_tscevtor()は全文検索するために文書を前処理しておく関数。実は単体でも実行可能(後述)
* to_tsquery()は検索用の語を全文検索の問い合わせに変換する関数。

### text型への検索

```postgres-console
ts=# SELECT data FROM  books_t WHERE to_tsvector(data) @@ to_tsquery('name');
                                                       data
-------------------------------------------------------------------------------------------------------------------
 {"name":"Work, Melos!", "authors":["Osamu Dasai"], "price":4.80}
 {"name":"What's your name?", "authors":["Makoto Shinkai"], "price":6.00}
 {"name":"PostgreSQL Administration is tired", "authors":["nuko_yokohama", "kingtomo", "masa_sae"], "price":34.00}
 {"name":"Interview with Authors!", "authors":["hogehoge editors"], "price":8.3}
 {"name":"PostgreSQL is wonderful", "authors":["nuko_yokohama"], "price":18.5}
(5 rows)
```
text型の場合、JSONのキーと値を区別するわけではないので、"name"という名称のキーも、""What's your name?"という値に入っている"name"も区別なくヒットしてしまう。
"name"などのような汎用的な語がキーに使われている場合、検索のノイズとなってしまい些か具合が悪い。

### json型への検索

```postgres-console
ts=# SELECT data FROM  books_t WHERE to_tsvector(data) @@ to_tsquery('name');
                                                       data
-------------------------------------------------------------------------------------------------------------------
ts=# SELECT data FROM  books_j WHERE to_tsvector(data) @@ to_tsquery('name');
                                   data
--------------------------------------------------------------------------
 {"name":"What's your name?", "authors":["Makoto Shinkai"], "price":6.00}
(1 row)

```
### jsonb型への検索

```postgres-console
ts=# SELECT data FROM  books_jb WHERE to_tsvector(data) @@ to_tsquery('name');
                                    data
-----------------------------------------------------------------------------
 {"name": "What's your name?", "price": 6.00, "authors": ["Makoto Shinkai"]}
(1 row)
```

json/jsonb型の場合には、"name"というキーは検索対象とはならず、値に格納されている"name"のみがヒットする。
文章がJSON文書内に格納されている場合には、大変ありがたい機能だと言える。

# textsearch_jaでもやってみた
最初のほうでtextsearch_jaは公式のメンテナがいないと書いたが、実は自分の環境にはtextsearch_jaのソースが残っているので、PostgreSQLがバージョンアップたびに、textseach_jaが動作するのかを確認していたりする。
一応、PostgreSQL 10でもそれなりに動作するっぽいので、json/jsonbとの連携を確認してみた。

こんなデータを用意して、さっきのテーブル(books_t, books_j)にロードしておく。

```json
{"名前":"働けメロス", "著者":["ださいおさむ"], "価格":480}
{"名前":"貴様の名前は", "著者":["深海真言"], "価格":680}
{"名前":"PostgreSQLの運用管理は面倒だね", "著者":["王之友", "ぬこ＠横浜","マサ冴木"], "価格":3400}
{"名前":"著者に聞いてみました！", "著者":["ほげほげ編集部"], "価格":800}
{"名前":"PostgreSQLはいいぞ", "著者":["ぬこ＠横浜"], "価格":1800}
```

これを以下のようなクエリで検索する。

```postgres
SELECT data FROM  <テーブル名> WHERE to_tsvector('japanese', data) @@ to_tsquery('japanese', '名前');
```
さっきとの違いは、to_tsvector()とto_tsquery()の第一引数に日本語での検索を示す"japanese"が追加されていること。この指定はtextsearch_jaをインストールすることで利用可能になる。

## text型への検索

```postgres-console
ts=# SELECT data FROM  books_t WHERE to_tsvector('japanese', data) @@ to_tsquery('japanese', '名前');
                                                data
----------------------------------------------------------------------------------------------------
 {"名前":"働けメロス", "著者":["ださいおさむ"], "価格":480}
 {"名前":"貴様の名前は", "著者":["深海真言"], "価格":680}
 {"名前":"PostgreSQLの運用管理は面倒だね", "著者":["王之友", "ぬこ＠横浜","マサ冴木"], "価格":3400}
 {"名前":"著者に聞いてみました！", "著者":["ほげほげ編集部"], "価格":800}
 {"名前":"PostgreSQLはいいぞ", "著者":["ぬこ＠横浜"], "価格":1800}
(5 rows)
```
## json型への検索

```postgres-console
ts=# SELECT data FROM  books_j WHERE to_tsvector('japanese', data) @@ to_tsquery('japanese', '名前');
                           data
----------------------------------------------------------
 {"名前":"貴様の名前は", "著者":["深海真言"], "価格":680}
(1 row)
```

英語文書での検証と同様に、"名前"というキーは無視され、値内の"名前"のみがヒットするようになっている。

# おわりに
* PostgreSQL 10からはJSON/JSONB文書内の値のみが検索対象となる。
* とりあえず、textsearch_jaでもJSON/JSONB連携はできそう。

# 参考：該当するリリースノート
本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.5. Data Types

> * AAdd full text search support for JSON and JSONB (Dmitry Dolgov)