https://www.okb-shelf.work/entry/db_json

最近、データベースのテーブル設計を担当したり、既存のテーブル設計を見ることが多いです。
テーブルの設計書を見てみるとJSON型が指定されているカラムが目につくことがあります。既存のテーブルでもJSON型が使われている箇所がありました。
「json型を使うな！」というわけではありませんが、前から何度も言っているように設計やアーキテクチャというのは常にトレードオフです。JSON型のカラムについても同様にメリット・デメリットがあります。
今回はRDBにおいてJSON型を使っても良いケース、避けるべきケースについて個人的な所感を書いてみたいと思います。なお、今回の内容はSQLアンチパターンでも紹介されている「ジェイウォーク(信号無視)」を参考にしています。

作者:Bill Karwin
オライリージャパン

とても良い書籍なので、データベースを触る人は一度は読んでおいて損はありません。

RDBの強み

まずJSON型のデメリットを紹介する前にPostgreSQLやMySQLのようなRDBが持つ強みについて紹介しておきます。
RDBが持つ強みの1つにテーブル同士の関係を定義することで、異なるテーブル間でデータを結びつけ、データの整合性や関連性を保つことができるという点があります。例えば企業に複数の従業員が存在しているというモデルはcompaniesテーブルとemployeesテーブルを定義して、1 対多の関連を定義することで表現できます。

employeesテーブルにはどの企業に所属しているかを分かるようにするためにcompany_idをカラムに持たせます。
さらにNULL値を許可せず、外部キー制約を指定しているものとします。

こうすることで得られる最大のメリットはemployeesは必ずcompaniesに紐付いていることが保証される点です。
逆を言えばemployeesはcompaniesに紐付けないことができません。つまり企業に所属していない従業員はこのデータベース上には存在しないことになります。

JSON型のデメリット

外部キー制約が設定できない
SQLが複雑になる
インデックスが効かないケースが多い

外部キー制約が設定できない

しかし、JSON型のカラムの場合には先ほどの保証がされなくなってしまいます。
もしかしたらサポートされているRDBもあるかもしれませんが、外部キー制約はJSON型に対して設定することは基本的に不可能です。先ほどの従業員(employees)のデータを企業(companies)にJSON型として持たせるとこんな感じでしょうか。

{
  "employees": [
    { "employee_id": 1, "employee_name": "山田一郎" },
    { "employee_id": 2, "employee_name": "山田二郎" },
    { "employee_id": 3, "employee_name": "山田三郎" }
  ]
}

上手くアプリケーション側でSQLを書いてあげれば、良い感じにデータの管理はできそうです。
ただしアプリケーション側で不具合があった場合にはあっという間にデータの整合性が破綻してしまいます。アプリケーション側にデータの整合性を保証してもらうことはハイリスク・ハイコストです。
先ほどのようにcompaniesとemployeesテーブルに分かれており、外部キー制約が設定されていたのであればアプリケーション側でデータの整合性を心配する必要はなくなります。

SQLが複雑になる

一般的にRDBではJSON型に対しての基本的な操作はサポートされています。
ただ、少し複雑なことをやろうとするとSQLが一気に複雑化してしまいます。WHEREやJOINなどは、基本的にどのRDBでもサポートされていますが、JSON型に対する関数はRDBによってあったり、なかったり、関数名が違ったり...と一貫性がありません。試しにPostgreSQLとMySQLのJSON型に対する関数のドキュメントページを見比べてみてください。

PostgreSQL: 9.16. JSON関数と演算子
MySQL: MySQL :: MySQL 8.0 Reference Manual :: 12.17.1 JSON Function Reference

自分は可能な限りRDBにおいて一貫性のあるSQLを書いてデータ処理をしたいので、JSON型に対する関数の違いを気にしたくないです。 JSON型ではなく、先ほどのcompaniesとemployeesのようにテーブルと関係を定義しておけばJOINを使って簡単にデータを処理することができます。

インデックスが効かないケースが多い

RDBやバージョンによもよりますが、JSON型に対するインデックスを作成することはできるので、シンプルな特定キーに対して検索をしたい場合ではインデックスが効きます。しかし、JSON型に対するSQLを書く際には、提供されているJSON型向けの関数を使うことが多いです。関数を使うとインデックスが効かなくなるというのはJSON型に限ったことではありませんが、WHERE句などで関数を使用すると結果的にインデックスが効かなくなります。

関数インデックスを定義すれば良いですが、検索対象のキー・バリューが多い場合には大量のインデックスを作る必要があるかもしれません。インデックスは無造作に定義することができますが、データが更新されるごとにインデックスも更新する必要があるため、データベースのパフォーマンス悪化を引き起こす可能性があるため、注意が必要です。

JSON型のメリット

メリットと合わせて個人的なJSON型の使い所を紹介したいと思います。
逆に考えると、上記で紹介したデメリットを満たさない場合にはJSON型を使っても良いかなと考えています。

外部テーブルを定義しなくて良い

テーブル設計は本当にトレードオフで究極まで正規化し続けると、とんでもない数のテーブル数になってしまいます。
テーブルをたくさん用意するというのもディスクのコストが高くなりますし、大量のインデックスが設定されていたりすると全体的にデータベースのパフォーマンスが悪化する可能性があります。ちょっとしたデータを記録したいだけなのに別テーブルとして定義するのもなぁ...という場合にはJSON型が良いです。

例えば、先ほどのemployeesにメタ情報を持たせるとします。
従業員が入社前に記入した趣味・好きな食べ物・複数回答の自由入力項目などを記録するためにJSON型を使うと幸せになれます。要するに基本的なカラム情報以外をまとめておくカラムという扱いをします。

{
  "meta": {
    "hobby": "釣り",
    "favorite_food": "ハンバーグ",
    "free_answers": [
      { "key": "釣りのスタイル", "value": "海釣りに行くことが多いです。" },
      { "key": "釣り歴", "value": "12歳ぐらいからやってます" },
      { "key": "好き嫌い", "value": "実は魚が食べられません" }
    ]
  }
}

外部テーブルを使って同じことをやろうとすると骨が折れます。
全ての項目のカラムを定義するのも手間ですし、自由入力項目をサポートする必要があります。結果的に良い感じにデータを記録しようとしてEAV(エンティティ・アトリビュート・バリュー)というアンチパターンを踏んでしまう可能性があります。

検索・更新しないデータ

JSON型に対するSQLは複雑になりやすいという問題がありますが、逆を言えばSQLであれこれやる必要のないデータであればJSON型に記録すれば良いです。例えばログデータにログを出力した時点での従業員の情報を記録しておくような場合です。ログデータの検索には日時・従業員IDを使用するものとして、ログ出力時点の従業員情報は使用しないものとします。当然、更新処理も必要ありません。

ログに出力当時の従業員情報が含まれていることで調査が容易になりそうです。
こういったケースではJSON型のカラムは効果を発揮します。ただ、無理にRDBにそういったデータを記録する必要はないと思います。ログデータのようなデータ容量が爆発的に増えていくものは自分だったらNoSQLのMongoDBなどを使って別のテーブルに記録します。

まとめ

RDBの強みはテーブル同士の関係を定義することでデータの整合性を保つことができる点
JSON型は外部キー制約を設定することができないため、アプリケーション側でデータの整合性を保たねばならない
JSON型に対するSQLは複雑で、各RDBごとに一貫性がない
インデックスが効かなくなるケースが可能性が高い
外部テーブルに定義するまでもないデータはJSON型のカラムに記録すると楽
逆に外部テーブルに記録しようとするとEAVたるアンチパターンになる可能性がある
検索・更新をしないようなデータはJSON型に記録すると複座なSQLが必要ない

JSON型は便利なものですが、使い所を間違えると痛い目をみます。
とりあえずJSON型でいいや、外部テーブルでいいや...と決め打ちをせずに求められているデータの特性・要件を踏まえてJSON型のカラムを使うべきかどうかを検討していきましょう。

偉そうに書いていますが、自分も過去に何度もJSON型にして後悔したことがあります...。
テーブル設計で悩んでいる方の参考になれば嬉しい限りです。今回の内容は「SQLアンチパターン」の内容をベースに執筆しています。テーブル設計に関わる方はぜひ一度、目を通してみてください。

作者:Bill Karwin
オライリージャパン

少しでも「ええな〜」と思ったらはてなスター・はてなブックマーク・シェアを頂けると励みになります。