NoSQLデータベースの台頭によりデータをJSONで保持し、それなりに検索性能も担保することが可能になってきた昨今ではありますが、ACID特性やデータの処理コスト、SQLという洗練されたデータ操作インターフェイスの存在で、まだまだRDBはあらゆるシステムで採用されているDBMSであると感じます。とは言え、

データをJSONで保持したい
JSON内の１属性に指定の値を持つレコードを検索したい
性能は犠牲にしたくない
ACID特性も犠牲にしないでね・・・

という要求はたしかに実在します。
最近のバージョンのMySQLやPostgreSQLはJSON型をサポートしており、特にPostgreSQLはJSONデータを操作するための関数や演算子が充実していること、JSONデータの検索に対して有用なGINインデックスが利用できることがメリットだと感じます。今回はPostgreSQLでのJSONデータ操作を軽く検証してみます。
なお、普段PostgresSQLを触っていない人がお試しで検証してみたものなので、初級編の記事としてお読みいただければ幸いです。

テストデータの準備
PostgreSQLにおけるJSONデータを扱うための各種手段
いざ、データ検索
総括
おわりに

テストデータの準備

下記構造のユーザデータを作成してみます。

{
    "name": "佐藤 太郎",
    "gender": "M",
    "address": "東京都千代田区大手町",
    "license": [
        "普通自動車",
        "普通二輪車"
    ],
    "zipcode": "100-0004",
    "birthday": "1993-03-18",
    "bloodtype": "A"
}

ユーザデータは下記の方針で作成しています。

名前・性別（name/gender）：男女とも日本人の姓名としてよく使われているものをある程度用意して、それらをランダムに組合せ
住所・郵便番号（address/zipcode）：日本国内の住所をランダムに割り当て（住所は都道府県市区町村町域までを格納）
保持運転免許（license）：全体からランダムに80%に普通自動車免許を割り当てし、うち10％に大型自動車免許・15％に大型二輪免許・25％に普通二輪免許を割り当て
生年月日（birthday）：1950/01/01～2005/12/31の日付をランダムに割り当て
血液型（bloodtype）：日本人の血液型割合に基づきランダムに設定

この方針でユーザデータを1000万件作成し

create table m_user (
    id serial not null
,   user_data jsonb
,   constraint pk_m_user primary key (id)
) ;
comment on table m_user is 'ユーザマスタ';
comment on column m_user.id is 'ＩＤ';
comment on column m_user.user_data is 'ユーザデータ';

で作成したテーブルに格納します。

PostgreSQLにおけるJSONデータを扱うための各種手段

まずデータ型について、PostgreSQLにはJSON型とJSONB型の２つが用意されています。

JSON型

入力されたJSON文字列をそのまま保存
空白やキーの順序も保持
JSON用の関数も利用可能
クエリパフォーマンスは良くない

JSONB型

入力されたJSON文字列を内部的に構造化されたバイナリ形式に変換して保存
空白は削除され、キーの順序は保持されない
JSON用の関数も利用可能
データの追記・編集は、一度パースして再構築が必要
クエリパフォーマンスは良い（GIN/GiSTインデックスが利用可能）

という違いがあります。今回は検索の性能やJSONB型で利用可能な演算子を検証してみたいので、上述のDDL（CREATE TABLE文）の通りJSONB型を利用します。

JSONB型専用演算子

包含演算子「@>」
左側のJSONオブジェクトに右型のJSONオブジェクトが含まれるかを判定

work01=> select '{"属性１":"値１","属性２":"値２"}'::jsonb @> '{"属性２":"値２"}';
 ?column?
----------
 t
(1 行)

存在演算子「?」左側のJSONオブジェクトのトップレベルのkeyに右側の値が存在するかを判定

work01=> select '{"属性１":"値１","属性２":{"属性３":"値３"}}'::jsonb ? '属性２';
 ?column?
----------
 t
(1 行)

work01=> select '{"属性１":"値１","属性２":{"属性３":"値３"}}'::jsonb ? '属性３';
 ?column?
----------
 f
(1 行)

その他、 * |? ：左側のJSONオブジェクトに右側のkeyまたは要素を１つでも含むか判定 * ?&：左側のJSONオブジェクトに右側のkeyまたは要素を全て含むか判定が良く使う演算子かと思います。

いざ、データ検索

それでは用意してみた1000万件のユーザデータに対して、検索性能を検証してみましょう。

住所が「東京都千代田区大手町」のユーザという条件で、それぞれ検索を実行してみました。

select
    *
from
    m_user
where
    user_data @> '{"address":"東京都千代田区大手町"}';
;

結果を取得するまで1秒強ほどの性能でした。画面からの検索要件で実行された場合にちょっと不安になる性能です。
ではここで、JSONB型専用演算子と組み合わせて使うと効力絶大なGINインデックスを設定してみます。

create index ix_m_user_gin on m_user using gin(user_data);

GIN（Generalized Inverted Index）インデックスは単語の位置情報を格納するための索引構造を持った転置インデックスで、包含している要素ごとにインデックスを作ってくれます。このGINインデックスが作成された状態でどれくらい性能が異なるのかを見てみると、0.05秒程度で結果が取得できました。JSONB型専用演算子とGINインデックスの組合せは効果絶大であることが確認できました。実行計画を確認しても

Aggregate  (cost=3888.54..3888.55 rows=1 width=8)
 +-Bitmap Heap Scan on m_user  (cost=52.35..3886.04 rows=1000 width=0)  Recheck Cond: (user_data @> '{"address": "東京都千代田区大手町"}'::jsonb)
    +-Bitmap Index Scan on ix_m_user_gin  (cost=0.00..52.10 rows=1000 width=0)  Index Cond: (user_data @> '{"address": "東京都千代田区大手町"}'::jsonb)

「Sequential Scan」ではなく、「Index Scan」でインデックスを使ってアクセスしていることが確認できます。

かなり効果的であることは確認できましたが、デメリットもいくつかありますし、部分検索ではGINインデックスが機能しないという点も注意が必要です。また、今回少し試したみたものとして、年齢を指定したい場合は

-- 20代の人を抽出
(user_data->>'birthday')::date BETWEEN (CURRENT_DATE - INTERVAL '30 years') AND (CURRENT_DATE - INTERVAL '20 years')

という条件を指定してあげる必要がありますが、これだとGINインデックスは機能しません。検索性能を確保するためには、仮想列（Generated Column）をカラム追加して、そこに対して通常のB-treeインデックスを設定してあげる必要がありそうです。このように、検索要件に見合ったERを作ってあげる工夫が必要になります。
また、GINインデックスのデメリットとしては、

作成・更新のコストが高い
インデックスのサイズが大きい

というデメリットもあるため、要件を踏まえ、採用可否の判断は必要ですね。とは言え、１つの強力な手段として役立つことがあるのではないでしょうか。

ちなみに、配列に対しての検索として保持している運転免許が特定の条件の検索もやってみようと思ったのですが、カーディナリティが低くインデックスが十分に機能しなかったので取りやめました・・・。極々少数の人が保持している運転免許を設定しておけば良かったと反省しております。

総括

業務システムを実装していて、JSONでデータを保持したい、でもJSONの要素を指定して検索したり更新したりしたい、NoSQLではなくRDBを採用したい、と思うことはあります。その際、PostgreSQLを選択肢の一つとして考えてみてはいかがでしょうか。

おわりに

KENTEMでは、様々な拠点でエンジニアを大募集しています！建設×ITにご興味頂いた方は、是非下記のリンクからご応募ください。 recruit.kentem.jp career.kentem.jp

KENTEM TechBlog

建設業のDXを実現するKENTEMの技術ブログです。

RDBによるJSONデータ戦略