PostgreSQLのJSON要素に全文検索（中間一致検索、LIKE "%word%"）する方法です。
今回の目標は以下のSQLにインデックスを使わせることです。valはJSONカラムです。
select * from test where val->>'name' like '%vcr%';
中間検索にインデックスを使うにはpg_trgmモジュールが必要です。
最初にcontribモジュールをインストールします。
そのためには前々回のインストール方法で使った
PostgreSQLのソースディレクトリが残ってる必要があります。
そのディレクトリに移動し以下を実行します。
make -C contrib
make -C contrib install
これで同ディレクトリにcontribディレクトリができたので、そこに移動し、さらにそこにあるpg_trgmディレクトリに移動します。そこでmake installすれば入ります。
cd contrib/
cd pg_trgm/
make install
つぎにインデックスを仕込みます。JSONカラムの作成は前回を見てください。
今回は以下のようなJSONのname項目を対象とします
{"j": {"k": {"val": 77}, "val": 83}, "val": 0, "name": "lnrqzxvcrh"}
こっからpsqlで操作です。
まずpg_trgmモジュールを有効にします。
create extension pg_trgm;
これで中間検索用のインデックスが使用可能になりました。さっそく作成します。なお対象のカラム名はvalです。valのname項目をテキストでアクセスするのでval->>'name'になります。またtrgmを有効にするためgist_trgm_opsをつけます。
create index namegist on test USING gist((val->>'name') gist_trgm_ops);
ではindexが使われるか見てみましょう。ちなみに１０万レコードです。
test=# explain analyze select * from test where val->>'name' like '%vcr%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=1878.29..19720.28 rows=40000 width=110) (actual time=69.950..70.776 rows=479 loops=1)
Recheck Cond: ((val ->> 'name'::text) ~~ '%vcr%'::text)
Heap Blocks: exact=476
-> Bitmap Index Scan on namegist (cost=0.00..1868.29 rows=40000 width=0) (actual time=69.858..69.858 rows=479 loops=1)
Index Cond: ((val ->> 'name'::text) ~~ '%vcr%'::text)
Planning time: 0.132 ms
Execution time: 70.871 ms
(7 rows)
test=#
使われてますね。やりました！
ちなみにない場合はSeqスキャンです。
では最後に検索結果をみてお別れしましょう。
test=# select * from test where val->>'name' like '%vcr%';
val
-----------------------------------------------------------------------
{"j": {"k": {"val": 0}, "val": 0}, "val": 0, "name": "fcqdvcrlxl"}
{"j": {"k": {"val": 67}, "val": 1}, "val": 0, "name": "ovcrldhvde"}
{"j": {"k": {"val": 36}, "val": 38}, "val": 0, "name": "kvcrklisnc"}
{"j": {"k": {"val": 32}, "val": 65}, "val": 0, "name": "apfhfhvcrz"}
{"j": {"k": {"val": 14}, "val": 71}, "val": 0, "name": "nyiwymfvcr"}
{"j": {"k": {"val": 81}, "val": 74}, "val": 0, "name": "vcryasriqw"}
{"j": {"k": {"val": 87}, "val": 75}, "val": 0, "name": "tsqyvcrwlr"}
{"j": {"k": {"val": 77}, "val": 83}, "val": 0, "name": "lnrqzxvcrh"}
{"j": {"k": {"val": 49}, "val": 96}, "val": 0, "name": "ovcrouzuwk"}
{"j": {"k": {"val": 93}, "val": 35}, "val": 1, "name": "xovszjvvcr"}
{"j": {"k": {"val": 34}, "val": 50}, "val": 1, "name": "gtwvcrvqyo"}
{"j": {"k": {"val": 2}, "val": 68}, "val": 1, "name": "csovcrxeui"}
{"j": {"k": {"val": 15}, "val": 20}, "val": 2, "name": "rvcrkxvvda"}
{"j": {"k": {"val": 67}, "val": 22}, "val": 2, "name": "yiedvcrrcv"}
{"j": {"k": {"val": 31}, "val": 26}, "val": 2, "name": "kdvcraoxpy"}
略