LoginSignup
27
17

More than 5 years have passed since last update.

PostgreSQL 10全部ぬこAdvent Calendar 2017Day 12
@nuko_yokohama(nuko yokohama)

JSON/JSONBと全文検索

Last updated at Posted at 2017-12-11

はじめに

にゃーん
この記事は、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)
27
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
27
17