PostgreSQLでjsonb型カラムのデータを検索した話

はじめに

こんにちは！プロダクトエンジニアのhypermktとraikunです。SmartHRの開発をしている過程でjsonb型のカラムを持つテーブルに遭遇しました。JSONの特定のキーに入ってる値を条件に検索したいという要件がありましたが、今までjsonb型カラムを利用したことなく、どうしたら指定の条件で検索できるのか悩みました。その過程で学びもあったので紹介します。

対象

PostgreSQL バージョンは15

jsonb型とは

jsonデータ型は入力テキストの正確なコピーで格納し、処理関数を実行するたびに再解析する必要があります。 jsonbデータ型では、分解されたバイナリ形式で格納されます。

引用：8.14. JSONデータ型

つまり、json型ではテキスト形式で格納しているため処理の度にparseが必要になります。逆にjsonb型では分解されたBinary形式で格納されるため処理の度にparseする必要がなく、高速で処理できます。

今回のお題

今回は書籍『パーフェクトRuby on Rails』がお気に入りの従業員を検索したいと思います。データベースには従業員とjsonb型カラムでお気に入り書籍を持ったレコードが2件登録されています。jsonb型カラムには以下のJSONが格納されています。

{
    "name": "｛お名前｝",
    "job": "{職業}",
    "favorite_books": [ #お気に入りの書籍
        {
            "title": "｛書籍名｝"
        }
    ],
}

具体的には以下のテーブル設計と登録されているレコードから favorite_books　に { "title": "パーフェクトRuby on Rails" } が含まれているレコードを検索したいです。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data JSONB
);

# ID: 1
INSERT INTO users (data)
VALUES ('{
    "name": "須磨英子",
    "job": "エンジニア",
    "favorite_books": [
        {
            "title": "パーフェクトRuby on Rails"
        },
        {
            "title": "Ruby入門"
        }
    ],
}'::jsonb);

# ID:2
INSERT INTO users (data)
VALUES ('{
    "name": "須磨英知",
    "job": "エンジニア",
    "favorite_books": [
        {
            "title": "パーフェクトPHP"
        },
        {
            "title": "PHP入門"
        }
    ]
}'::jsonb);

jsonb型カラムのデータを検索したい

はじめに試した方法 -LIKE検索-

パッと思いついたのがLIKE検索でした。data カラムをテキストに変換してパーフェクトRuby on Railsを含むレコードをLIKE検索してみました。

SELECT * FROM items
WHERE data::text LIKE "%パーフェクトRuby on Rails%"

その結果、ID: 1のレコードが1件ヒットしました。

検索結果としては想定通りのものがヒットしましたが、もし将来的にdata カラムのJSONに購入予定の書籍のキー(plans_to_buy_books)が増えた場合、LIKE検索のままだとID: 1, 2の両方がヒットしてしまうので、JSONの構造を無視して検索していることが分かりました。

# ID: 1
{
    "name": "須磨英子",
    "job": "エンジニア",
    "favorite_books": [
        {
            "title": "パーフェクトRuby on Rails"
        },
        {
            "title": "Ruby入門"
        }
    ],
    "plans_to_buy_books": []　　　# このキーが増えた
}

# ID: 2
{
    "name": "須磨英知",
    "job": "エンジニア",
    "favorite_books": [
        {
            "title": "パーフェクトPHP"
        },
        {
            "title": "PHP入門"
        }
    ],
    "plans_to_buy_books": [
        {
            "title": "パーフェクトRuby on Rails"　　# LIKE検索だとここもヒットしてしまう!!
        }
    ]
}

jsonb型カラムに対するオペレーターを使った検索方法

調査の過程でPostgresSQLにはjsonb用に@> というオペレータが用意されていることを知りました。

PostgreSQL: Documentation: 15: 9.16. JSON Functions and Operators

@>は左側のJSON値に右側の値が含まれているかで該当するレコードを抽出することができるオペレータなので、これを使いfavorite_books 配列のtitleキーに パーフェクトRuby on Rails を含むレコードを検索してみました。

SELECT * FROM items
WHERE data @> { favorite_books: [title: 'パーフェクトRuby on Rails'] }

その結果、将来的にJSONの構造が変わっても影響を受けずにID:1のみがヒットするようになりました。また、検索したいJSONを視覚的にわかりやすく指定できるので、シンプルに検索することができます。

jsonb型に対する他のオペレーターも試してみました

PostgreSQLの公式ドキュメント(PostgreSQL: Documentation: 15: 9.16. JSON Functions and Operators)には、jsonb型カラムのデータを検索できる他のオペレーターも紹介されていました。オペレーターの一部を試してみたいと思います。

<@オペレーター

<@オペレーターは左側のJSON値が右側の値に含まれているかを判定し、該当するレコードを抽出することができます。検索対象の値が @> と逆なこと以外は同じように使えそうです。

SELECT * FROM "users" WHERE ('{"name":"須磨英子"}' <@ data)

配列の中を検索対象とする場合は、以下のようにすることで抽出することができます。

SELECT * FROM "users" WHERE ('{"favorite_books":[{"title":"Ruby入門"}]}' <@ data)

?オペレーター

?オペレーターはトップレベルのキーが存在するかを判定し、該当するレコードを抽出することができます。

SELECT * FROM "users" WHERE (data ? 'name' )

?|と?&オペレーター

?| オペレーターはdata カラム内のJSONデータが指定されたトップレベルのキーのいずれかを持つ場合に一致するレコードを抽出することができます。

SELECT * FROM "users" WHERE (data ?| ARRAY['name','job','favorite_books'] )

?& オペレーターはdata カラム内のJSONデータが指定されたトップレベルのキーをすべて持つ場合に一致するレコードを抽出することができます。

SELECT * FROM "users" WHERE (data ?& ARRAY['name','job','favorite_books'] )

どのオペレーターを使うべきか

特定の key/value のJSONを含むレコードで絞り込みたいときには @>, @< を使用し、トップレベルのキーを持っているレコードで絞り込みたいときには ?, ?|, ?& オペレーターを使用すると良いことがわかりました。

まとめ

今回、私達はjsonb型カラムのレコードを効率的に検索する基本的な方法を学びました。JSON構造に対して明示的に抽出したい条件をシンプルに指定できとても良いなぁと感じました。今後jsonb型カラムに出会っても臆することなく開発できそうです。

PostgreSQLの公式ドキュメント(PostgreSQL: Documentation: 15: 9.16. JSON Functions and Operators)には、今回紹介しなかった特定のキーの値を参照するオペレーターなども紹介されていますので、興味のある方はぜひご覧ください。

We Are Hiring!

SmartHR では一緒に SmartHR を作りあげていく仲間を募集中です！

少しでも興味を持っていただけたら、カジュアル面談でざっくばらんにお話ししましょう！

hello-world.smarthr.co.jp

SmartHR Tech Blog

SmartHR 開発者ブログ