PostgreSQLの配列型のJSONで検索したい人生でしたが無理っぽいということがわかって悲しみにくれています。
たとえばこんな感じのテーブル

1
2
3
4
5

id   tags
---- ------------------------------
1    ["静岡", "沼津"]
2    ["相模大野", "東静岡"]
3    ["静修学園前", "札幌"]

こんな感じのデータに対して like %r% みたいな検索をしたいなと思ったときに無理だなと。
ChatGPT(GPT4)氏によると無理だと言っていた。

配列形式のJSONBに対してLIKE検索を行いたい場合、一般的にはアプリケーション側で配列をループして各要素に対してLIKE検索を行う、または配列の各要素をテキストにキャストしてからLIKE検索を行うなどの方法が考えられます。ただし、これらの方法は効率が良いとは言えませんので、可能であればデータモデルの設計を見直すことをお勧めします。

ちなみにオブジェクト型なら検索出来るらしい

1

WHERE (tags->>'key' LIKE '%静%')

jsonb_array_elements_textで無理やり検索できる？

stackoverflowのPostgresql: How to perform LIKE query on a json array?の記事を見てたらjsonb_array_elements_textでlike検索できるのでは？という気持ちになってきた。

1
2
3
4
5
6
7

SELECT id, tags
FROM tags
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements_text(tags) AS tag 
    WHERE tag LIKE '%静%'
)

こんな感じのクエリで検索できるようになった。
ちなみにJSON型だとキャストが必要なのでこんな感じで書きます。

1
2
3
4
5
6
7

SELECT id, tags
FROM tags
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements_text(tags::jsonb) AS tag 
    WHERE tag LIKE '%静%'
)

最後に

今回は特定の数人が使う管理画面で検索が必要になったので上記のような対応を考えました。
ChatGPTも「可能であればデータモデルの設計を見直すことをお勧めします」といってますし、多くのユーザーが使う場合にはもっと違う方法を考えたほうがいいかと思います。

PostgreSQL徹底入門 第4版 インストールから機能・仕組み、アプリ作り、管理・運用まで
amazon.co.jp