弁護士ドットコム株式会社 Creators’ blog

弁護士ドットコムがエンジニア・デザイナーのサービス開発事例やデザイン活動を発信する公式ブログです。

MySQL JSON 型についての考察

リーガルブレイン開発 エンジニアリング

はじめに

初めまして、開発本部リーガルブレイン部でバックエンドエンジニアを担当しています、渡辺です。前回の記事から部署を移動しまして、現在は「リーガルブレインエージェント」などの法曹業界向けの SaaS 開発に携わっております。

最近、業務の中で MySQL の JSON 型を扱う機会がありました。業務で扱うのははじめてだったため、自分なりに JSON 型の仕様やメリット・デメリットについてまとめてみました。

JSON 型の基礎知識

MySQL では JSON 型のカラムを定義することによって、JSON データをネイティブに保存、操作できます。

dev.MySQL.com

内部的な処理について

MySQL 内の JSON データは、内部ではバイナリーフォーマットとして保存されています。

バイナリーフォーマット形式での保存には以下の特徴があります。

INSERT 時の挙動

JSON 文字列のパース → バリデーション → バイナリフォーマット変換 → ディスクに保存 の流れになります。

SELECT 時の挙動

ディスクから読み込み → バイナリ解析 → JSON 文字列変換 → クライアントに返却 の流れになります。

バイナリフォーマットを採用している理由

MySQL が JSON データをバイナリフォーマットで保存しているのは、主にパフォーマンスと効率性を向上させるためです。バイナリフォーマットでは、JSON ドキュメント内の特定の要素に直接アクセスできるため、部分的なデータの読み取りが高速に行えます。また通常のテキスト形式の JSON と比較してストレージ効率も優れており、ディスク容量の節約にもつながります。

ただし、このアプローチには以下のようなトレードオフも存在します。

  • INSERT 時には JSON 文字列からバイナリフォーマットへの変換が必要となるため、書き込み時にはオーバーヘッドが発生する
  • 大きな JSON ドキュメントを扱う場合、パース時にデータ全体を読み込む必要があるため非効率になる可能性がある
  • JSON 型カラムでは直接インデックスを作成できず、インデックスが必要な場合は Generated Column または関数インデックスを使用する必要がある（詳細は後述）

SQL 例

当然ですが、JSON データ型のカラムも SQL で操作できます。レコードの検索、追加はもちろんのこと、JSON データ操作のための関数も用途別に用意されています。

前提となるテーブル設計

カラム名 型 備考
id Int PK
email String
name String
profile Json
settings Json
createdAt DateTime
updatedAt DateTime

通常の SELECT 文

mysql> SELECT name, profile FROM users;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name  | profile                                                                                                                                                                                                                                       |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Alice | {"bio": "Software Engineer at Example Corp", "avatar": "https://example.com/avatars/alice.jpg", "location": {"city": "Tokyo", "country": "Japan"}, "socialLinks": {"github": "alice-dev", "twitter": "@alice", "linkedin": "alice-engineer"}} |
| Bob   | {"bio": "Designer & Creative Director", "avatar": "https://example.com/avatars/bob.jpg", "skills": ["UI/UX", "Graphic Design", "Illustration"], "experience": {"years": 8, "companies": ["Design Studio A", "Creative Agency B"]}}            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT name, settings FROM users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name  | settings                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Alice | {"theme": "dark", "privacy": {"showEmail": false, "profileVisible": true}, "language": "ja", "notifications": {"sms": false, "push": false, "email": true}} |
| Bob   | {"theme": "light", "language": "en", "notifications": {"sms": true, "push": true, "email": true}}                                                           |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

通常の INSERT 文

JSON_OBJECT を使用
mysql> INSERT INTO users (name, email, profile, settings, updatedAt) VALUES (
    ->   'Charlie',
    ->   'charlie@example.com',
    ->   JSON_OBJECT(
    ->     'bio', 'Product Manager',
    ->     'avatar', 'https://example.com/avatars/charlie.jpg',
    ->     'location', JSON_OBJECT(
    ->       'city', 'Osaka',
    ->       'country', 'Japan'
    ->     ),
    ->     'socialLinks', JSON_OBJECT(
    ->       'twitter', '@charlie_pm',
    ->       'linkedin', 'charlie-pm'
    ->     )
    ->   ),
    ->   JSON_OBJECT(
    ->     'theme', 'light',
    ->     'language', 'ja',
    ->     'notifications', JSON_OBJECT(
    ->       'email', true,
    ->       'push', true,
    ->       'sms', false
    ->     ),
    ->     'privacy', JSON_OBJECT(
    ->       'profileVisible', true,
    ->       'showEmail', false
    ->     )
    ->   ),
    ->   CURRENT_TIMESTAMP(3)
    -> );
Query OK, 1 row affected (0.03 sec)
文字列を使用
mysql> INSERT INTO users (name, email, profile, settings, updatedAt) VALUES (
    ->   'David',
    ->   'david@example.com',
    ->   '{"bio":"Frontend Developer","location":{"city":"Kyoto","country":"Japan"}}',
    ->   '{"theme":"dark","language":"en"}',
    ->   CURRENT_TIMESTAMP(3)
    -> );
Query OK, 1 row affected (0.01 sec)

JSON_EXTRACT （ JSON 内の値で検索条件を定義し、一致するレコードを抽出する）

mysql> SELECT id, name, email
    -> FROM users
    -> WHERE JSON_EXTRACT(settings, '$.theme') = 'dark';
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
|  7 | Alice | alice@example.com |
+----+-------+-------------------+
1 row in set (0.02 sec)

JSON_CONTAINS_PATH （ 指定したパスが存在するレコードを抽出する）

mysql> SELECT id, name, email,
    ->        profile->>'$.socialLinks.github' AS github
    -> FROM users
    -> WHERE JSON_CONTAINS_PATH(profile, 'one', '$.socialLinks.github');
+----+-------+-------------------+-----------+
| id | name  | email             | github    |
+----+-------+-------------------+-----------+
|  7 | Alice | alice@example.com | alice-dev |
+----+-------+-------------------+-----------+
1 row in set (0.03 sec)

JSON_KEYS （カラムに存在する key を抽出する）

mysql> SELECT 
    ->   name,
    ->   JSON_KEYS(settings) AS setting_keys
    -> FROM users;
+-------+---------------------------------------------------+
| name  | setting_keys                                      |
+-------+---------------------------------------------------+
| Alice | ["theme", "privacy", "language", "notifications"] |
| Bob   | ["theme", "language", "notifications"]            |
+-------+---------------------------------------------------+
2 rows in set (0.02 sec)
mysql> SELECT 
    ->   name,
    ->   JSON_KEYS(profile) AS profile_keys
    -> FROM users;
+-------+----------------------------------------------+
| name  | profile_keys                                 |
+-------+----------------------------------------------+
| Alice | ["bio", "avatar", "location", "socialLinks"] |
| Bob   | ["bio", "avatar", "skills", "experience"]    |
+-------+----------------------------------------------+
2 rows in set (0.00 sec)

JSON_SET （ JSON 内の値を部分的に更新する）

mysql> UPDATE users
    -> SET settings = JSON_SET(settings, '$.theme', 'light')
    -> WHERE name = 'Alice';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE users
    -> SET settings = JSON_SET(
    ->   settings,
    ->   '$.fontSize', 'medium',
    ->   '$.animations', true
    -> )
    -> WHERE name = 'Alice';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

JSON_REMOVE （JSON 内の値を部分的に削除する）

mysql> UPDATE users
    -> SET settings = JSON_REMOVE(settings, '$.animations')
    -> WHERE JSON_CONTAINS_PATH(settings, 'one', '$.animations');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

より高度な利用方法、考慮すべき点

インデックス

JSON 型カラムに対しては以下の 2 つの方法でインデックスを作成可能です。

  1. Generated Column を使用する方法（MySQL 5.7 以降）
  2. 関数インデックスを使用する方法（MySQL 8.0.13 以降）

それぞれの方法について、具体例とともに見ていきます。

方法1: Generated Column を使用する

Generated Column とは

他のカラムの値から自動的に計算されるカラムです。 JSON 型のカラムの場合、 JSON 内の値を抽出して、別カラムとして定義できます。また JSON 型から作成した Generated Column を元にインデックスを生成可能です。

インデックス作成前のインデックスとレコードを確認
mysql> SHOW INDEX FROM users;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY         |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          0 | users_email_key |            1 | email       | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.03 sec)

mysql> SELECT id, email, 
    ->        JSON_EXTRACT(profile, '$.location.city') as city,
    ->        JSON_EXTRACT(settings, '$.theme') as theme
    -> FROM users LIMIT 5;
+----+---------------------+---------+---------+
| id | email               | city    | theme   |
+----+---------------------+---------+---------+
|  7 | alice@example.com   | "Tokyo" | "light" |
|  8 | bob@example.com     | NULL    | "light" |
|  9 | charlie@example.com | "Osaka" | "light" |
| 10 | david@example.com   | "Kyoto" | "dark"  |
+----+---------------------+---------+---------+
4 rows in set (0.02 sec)
Generated Column の追加
mysql> ALTER TABLE users ADD COLUMN 
    ->   profile_city VARCHAR(100) 
    ->   GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.location.city')))
    ->   STORED
    ->   COMMENT '';
Query OK, 4 rows affected (0.15 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESCRIBE users;
+--------------+--------------+------+-----+----------------------+-------------------+
| Field        | Type         | Null | Key | Default              | Extra             |
+--------------+--------------+------+-----+----------------------+-------------------+
| id           | int          | NO   | PRI | NULL                 | auto_increment    |
| email        | varchar(191) | NO   | UNI | NULL                 |                   |
| name         | varchar(191) | NO   |     | NULL                 |                   |
| profile      | json         | YES  |     | NULL                 |                   |
| settings     | json         | YES  |     | NULL                 |                   |
| createdAt    | datetime(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| updatedAt    | datetime(3)  | NO   |     | NULL                 |                   |
| profile_city | varchar(100) | YES  |     | NULL                 | STORED GENERATED  |
+--------------+--------------+------+-----+----------------------+-------------------+
8 rows in set (0.00 sec)
Generated Column からインデックスを作成
mysql> CREATE INDEX idx_profile_city ON users(profile_city);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM users;
+-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY          |            1 | id           | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          0 | users_email_key  |            1 | email        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_profile_city |            1 | profile_city | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.02 sec)
EXPLAIN での実行結果
mysql> EXPLAIN SELECT * FROM users WHERE profile_city = 'Tokyo';
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_profile_city | idx_profile_city | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

方法2: 関数インデックスを使用する

MySQL 8.0.13 以降では、関数インデックス（Functional Index） を使用することで、JSON カラムの特定のパスに対して直接インデックスを作成できます。

関数インデックスの作成
mysql> ALTER TABLE users ADD INDEX idx_settings_theme ((CAST(settings->>'$.theme' AS CHAR(50))));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM users;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+--------------------------------------------------------------------------------------------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression                                                                                 |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+--------------------------------------------------------------------------------------------+
| users |          0 | PRIMARY            |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                                                                                       |
| users |          0 | users_email_key    |            1 | email       | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                                                                                       |
| users |          1 | idx_settings_theme |            1 | NULL        | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(json_unquote(json_extract(`settings`,_latin1\'$.theme\')) as char(50) charset latin1) |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+--------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

関数インデックスを使用する場合、カラムは追加されず、インデックスのみが作成されます。Expression カラムに、インデックスに使用されている式が表示されています。

関数インデックスを使用したクエリ
mysql> EXPLAIN SELECT * FROM users WHERE CAST(settings->>'$.theme' AS CHAR(50)) = 'dark';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_settings_theme | idx_settings_theme | 53      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

関数インデックスを利用する際は、クエリ内でインデックス定義と同じ式を使用する必要があります。

Generated Column と関数インデックスのメリット・デメリット

Generated Column と関数インデックスについて、それぞれのメリット・デメリットと適切な利用シーンを紹介します。

Generated Column
メリット

Generated Column の最大のメリットは、クエリの記述がシンプルになることです。カラム名のみで参照できるため、 SELECT ・ WHERE ・ ORDER BY ・ GROUP BY などさまざまな場所で使いやすくなります。また MySQL 5.7 以降で利用可能なため、比較的古いバージョンでも使用できます。

デメリット

Generated Column を使用すると、テーブルにカラムが追加されます。カラムが増える分、メンテコストが増加します。また STORED 型を選択した場合は実際のデータが保存されるため、ストレージ容量が増える点にも注意が必要です。

適切な利用シーン

以下のようなシーンでは Generated Column が有効です。

  • JSON から取り出した値を頻繁に参照する場合
  • 検索条件だけでなく、表示や集計にも使う場合
  • 古いバージョンの MySQL（5.7 系）を使用している場合
関数インデックス
メリット

関数インデックスは、テーブル構造がシンプルに保てる点が大きな特長です。カラムを追加する必要がなく、インデックスのみが保存されるため、ストレージ効率が良くなります。また一時的な検索最適化が必要な場合にも適しています。

デメリット

関数インデックスを使用する場合、クエリ内で式を記述する必要があるため、やや複雑になります。またインデックス定義と完全に一致する式でないとインデックスが使われないという制約があります。さらに、MySQL 8.0.13 以降でなければ利用できないため、バージョン要件にも注意が必要です。

適切な利用シーン

以下のようなシーンでは関数インデックスが有効です。

  • WHERE 句での検索にのみ使う場合
  • テーブル構造をシンプルに保ちたい場合
  • 特定のクエリのみを高速化したい場合

prisma での利用

JSON 型のカラムは当然 O/R マッパでも扱うことが可能です。今回は例として prisma での実装例を紹介します。

prisma の場合、 JSON 型の値をオブジェクトとして扱えるため、直感的な実装になります。

値の参照

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  console.log('JSON_EXTRACT - JSONから値を抽出\n');

  // 例1: 単一の値を抽出
  const users = await prisma.user.findMany({
    where: { settings: { not: null } }
  });
  
  users.forEach(user => {
    const theme = user.settings?.theme;
    console.log(`${user.name}: ${theme}`);
  });

  // 例2: ネストした値を抽出
  const usersWithProfile = await prisma.user.findMany({
    where: { profile: { not: null } }
  });
  
  usersWithProfile.forEach(user => {
    const city = user.profile?.location?.city;
    const country = user.profile?.location?.country;
    console.log(`${user.name}: ${city}, ${country}`);
  });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

値の追加・更新

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  console.log('JSON_SET - JSON内の値を設定/更新\n');

  // 例1: 既存の値を更新
  const user1 = await prisma.user.findFirst({
    where: { email: 'alice@example.com' }
  });
  
  if (user1?.settings) {
    console.log(`更新前: theme = ${user1.settings.theme}`);
    
    const updatedSettings = {
      ...user1.settings,
      theme: user1.settings.theme === 'dark' ? 'light' : 'dark'
    };
    
    await prisma.user.update({
      where: { id: user1.id },
      data: { settings: updatedSettings }
    });
    
    console.log(`更新後: theme = ${updatedSettings.theme}`);
  }

  // 例2: 新しいフィールドを追加
  const user2 = await prisma.user.findFirst({
    where: { email: 'bob@example.com' }
  });
  
  if (user2?.settings) {
    const updatedSettings = {
      ...user2.settings,
      fontSize: 'medium',
      notifications: {
        ...user2.settings.notifications,
        desktop: true
      }
    };
    
    await prisma.user.update({
      where: { id: user2.id },
      data: { settings: updatedSettings }
    });
    
    console.log(`\nフィールド追加: fontSize, notifications.desktop`);
  }

  // 例3: ネストしたオブジェクトの部分更新
  const user3 = await prisma.user.findFirst({
    where: { profile: { not: null } }
  });
  
  if (user3?.profile?.location) {
    const updatedProfile = {
      ...user3.profile,
      location: {
        ...user3.profile.location,
        city: 'Osaka'
      }
    };
    
    await prisma.user.update({
      where: { id: user3.id },
      data: { profile: updatedProfile }
    });
    
    console.log(`\n都市更新: ${updatedProfile.location.city}`);
  }
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

値の削除

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  console.log('JSON_REMOVE - JSON内の値を削除\n');

  // 例1: 単一のフィールドを削除
  const user1 = await prisma.user.findFirst({
    where: { email: 'bob@example.com' }
  });
  
  if (user1?.settings) {
    console.log(`削除前: [${Object.keys(user1.settings).join(', ')}]`);
    
    const { fontSize, ...newSettings } = user1.settings;
    
    await prisma.user.update({
      where: { id: user1.id },
      data: { settings: newSettings }
    });
    
    console.log(`削除後: [${Object.keys(newSettings).join(', ')}]`);
  }

  // 例2: ネストしたフィールドを削除
  const user2 = await prisma.user.findFirst({
    where: { email: 'alice@example.com' }
  });
  
  if (user2?.settings?.notifications) {
    const { desktop, ...remainingNotifications } = user2.settings.notifications;
    
    await prisma.user.update({
      where: { id: user2.id },
      data: {
        settings: {
          ...user2.settings,
          notifications: remainingNotifications
        }
      }
    });
    
    console.log(`\nネストしたフィールド削除: notifications.desktop`);
  }

  // 例3: null/undefinedを除外
  const user3 = await prisma.user.findFirst({
    where: { settings: { not: null } }
  });
  
  if (user3?.settings) {
    const cleanSettings = Object.fromEntries(
      Object.entries(user3.settings).filter(([, value]) => 
        value !== null && value !== undefined
      )
    );
    
    console.log(`\nクリーンアップ: ${Object.keys(cleanSettings).length}個のキー`);
  }
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

生クエリを使用しないといけないケース

JSON 型の場合、一部の操作は生クエリを使用しないといけないケースが出てくるため、注意が必要です。

JSON 内の値でフィルタリング
// NG（エラー）
await prisma.user.findMany({
  where: {
    'settings.theme': 'dark'
  }
});
await prisma.user.findMany({
  where: {
    settings: {
      path: ['theme'],
      equals: 'dark'
    }
  }
});

// OK
const darkThemeUsers = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE JSON_EXTRACT(settings, '$.theme') = 'dark'
`;
const filtered = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE JSON_EXTRACT(settings, '$.theme') = 'dark'
    AND JSON_EXTRACT(settings, '$.language') = 'ja'
`;
const searchResults = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE JSON_EXTRACT(profile, '$.bio') LIKE ${'%developer%'}
`;
JSON データの部分更新
// 非効率：全体を取得→変更→保存
const user = await prisma.user.findUnique({ where: { id: 1 } });
const updatedSettings = {
  ...user.settings,
  theme: 'dark'
};
await prisma.user.update({
  where: { id: 1 },
  data: { settings: updatedSettings }
});

// 効率的：必要な部分だけ更新
await prisma.$executeRaw`
  UPDATE users
  SET settings = JSON_SET(settings, '$.theme', 'dark')
  WHERE id = 1
`;
await prisma.$executeRaw`
  UPDATE users
  SET settings = JSON_SET(
    settings,
    '$.theme', 'dark',
    '$.fontSize', 'large',
    '$.notifications.email', true
  )
  WHERE id = ${userId}
`;
await prisma.$executeRaw`
  UPDATE users
  SET settings = JSON_SET(settings, '$.lastLogin', NOW())
  WHERE JSON_EXTRACT(settings, '$.theme') = 'dark'
`;

MySQL で JSON 型を扱うメリット・デメリット

メリット

JSON 型を利用する最大のメリットは、スキーマを柔軟に設定できる点です。従来の RDB では、カラムを追加・変更する際にマイグレーション作業が必要となり、サービス規模によっては影響範囲の調査やダウンタイムの考慮が必要でした。しかし JSON 型を使用すれば、機能改修や機能拡張に伴うテーブル構造の変更が不要になります。またデータの正規化を気にする必要もなくなるため、結果として開発スピードの向上につながります。

デメリット

一方で、以下の制約も存在します。

  • インデックス作成に制約がある
    • JSON 型カラムでは直接インデックスを作成できず、Generated Column または関数インデックスを使用する必要がある
    • Generated Column を使用する場合、インデックスを追加するたびに新しいカラムが増え、メンテナンスコストの増加につながる可能性がある
    • 関数インデックスを使用する場合、クエリ内でインデックス定義と同じ式を記述する必要があり、クエリがやや複雑になる
  • JSON データが肥大化した場合のパフォーマンス劣化
    • 部分的なデータを参照する場合でも JSON データ全体を読み込む必要がある
    • ドキュメントサイズが大きくなるとメモリ使用量が増加し、パフォーマンスに影響を与える
  • リレーション機能が使用できない
    • JSON データ内の値に対して外部キーを設定できない
    • データの整合性を保つには別の手段を検討する必要がある
  • 型安全性の低さ
    • スキーマレスであるがゆえに型チェックが緩くなる
  • O/R マッパーの制約
    • 使用する O/R マッパーによっては生クエリに頼らざるを得ないケースが存在する

どういったシチュエーションで有効か

有効なシチュエーション

JSON 型が特に効果を発揮するのは、スキーマに柔軟性を持たせたい場合、つまり仕様変更が頻繁に発生するようなデータ構造です。加えて、データサイズが軽量であり、検索や集計の頻度が少ないデータ群であれば、前述したデメリットの影響を最小限に抑えられます。

以下のようなシチュエーションが有効と考えられます。

  • ユーザー設定
    • ユーザーごとに異なる設定項目を柔軟に設けることができる
    • スキーマレスの恩恵を最大限に受けられる
  • コンテンツのメタ情報
    • ブログ記事、動画、画像など、コンテンツの種類ごとに必要な情報が異なる場合でも 1 つのカラムで柔軟に対応できる

避けたほうが良いシチュエーション

一方で、JSON 型の使用を避けたほうが良いケースもあります。リレーション機能を使用したい場合や、データサイズが大きい場合、そして検索や集計が頻繁に行われるデータ群では、従来の正規化されたテーブル設計のほうが適しています。

以下のようなシチュエーションは避けたほうがよいです。

  • マスタデータ
    • 検索・フィルタリングの頻度が高い
    • 他のテーブルとのリレーションが必要になる
  • 売上・購買データ
    • 集計処理が頻繁に発生する
  • アクセスログ
    • データ量が肥大化しやすい
    • 時系列での検索・集計が頻繁に行われる

まとめ

MySQL は RDB である都合上、正規化を前提とした設計がほとんどであり、 JSON 型のカラムはどうしてもアンチパターンとして紹介されるケースが多いです。 しかし、有効なシチュエーションは確かに存在し、それらを見極めて現場に取り込んでいく姿勢が重要かと自分は考えています。 弊社開発チームでも、 JSON 型の利用についてより良い形を模索していきたいと思っています。 ご清覧いただきありがとうございました。

参考リンク

dev.MySQL.com

dev.MySQL.com

dev.MySQL.com

dev.MySQL.com

dev.MySQL.com

dev.MySQL.com

© Bengo4.com, Inc. 2005