JSON/JSONBと全文検索

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の12日目のエントリです。

JSON/JSONBとは

JSONを知らない人はたぶんいないとは思うけど、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	外部製品	N-gram	○	2文字以下の語の検索に強い
textsearch_ja	外部製品	形態素	○	Mecab依存。現状、公式のメンテナがいない？
pgroonga	外部製品	形態素,N-gram	○	高機能・高速が売り。groongaのインストールが必要。

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

検証モデル

こんな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文でロードする。

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型)のテーブルに対して、以下のようなクエリをそれぞれ実行してみる。

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

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

text型への検索

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型への検索

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型への検索

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)にロードしておく。

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

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

SELECT data FROM  <テーブル名> WHERE to_tsvector('japanese', data) @@ to_tsquery('japanese', '名前');

さっきとの違いは、to_tsvector()とto_tsquery()の第一引数に日本語での検索を示す"japanese"が追加されていること。この指定はtextsearch_jaをインストールすることで利用可能になる。

text型への検索

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型への検索

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)