MySQLのJSON型入門：JSON_EXTRACTとパス記法

black laptop computer turned-on displaying source code on table SQL

MySQLはJSON型カラムをネイティブにサポートしており、商品属性・設定値・タグ一覧など「行ごとに形が異なるデータ」を1カラムにまとめて格納できます。単なるVARCHARと違い、挿入時にJSON形式として有効かどうかが自動検証されるうえ、内部的にバイナリ形式で最適化されて保存されるため、特定のキーへのアクセスが効率的です。ただし「どうやって値を取り出すのか」「更新するSQL構文が分からない」「インデックスはどう扱うのか」という疑問で詰まる方も多いのが現状です。この記事では、JSON型カラムの作成からJSON_EXTRACTによる値取得と$パス記法の基本、->と->>の違い、JSON_SETなど更新関数の使い分け、MySQL 8.0から使えるJSON_TABLEでの行展開まで、実例を交えて整理します。

このシナリオで考える

ECサイトの商品テーブルを例にします。衣類なら「サイズ・色」、家電なら「電圧・保証年数・対応ポート一覧」というようにカテゴリごとに属性が異なります。全カテゴリ共通の固定カラムを用意すると列が際限なく増えるため、attributesというJSON型カラムにまとめて格納する設計を取ります。

CREATE TABLE products (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  category   VARCHAR(50)  NOT NULL,
  attributes JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, category, attributes) VALUES
  ('Tシャツ（白）',    '衣類', '{"size": "M", "color": "white", "tags": ["casual", "summer"]}'),
  ('ノートPC',        '家電', '{"voltage": 100, "warranty_years": 1, "ports": ["USB-C", "HDMI"]}'),
  ('コーヒーメーカー', '家電', '{"voltage": 100, "warranty_years": 2, "capacity_ml": 600}');

このデータを土台に各関数の動きを確認します。

JSON_EXTRACTと$パス記法の基本

JSON_EXTRACTは、JSONドキュメントから特定の値を取り出す関数です。第1引数にカラム名、第2引数に「どこを取り出すか」を示すパス式を渡します。

SELECT
  name,
  JSON_EXTRACT(attributes, '$.size') AS size
FROM products
WHERE category = '衣類';

+------------------+-------+
| name             | size  |
+------------------+-------+
| Tシャツ（白）    | "M"   |
+------------------+-------+

結果の"M"にダブルクォートが付いています。JSON型では文字列をクォートつきで表現するためです。WHERE句で文字列と直接比較したい場面ではクォートが邪魔になります。そこで使うのが2種類のショートハンド演算子です。

記法 相当する処理 戻り値の例
col -> '$.key' JSON_EXTRACT(col, ‘$.key’) "M"（クォートつき）
col ->> '$.key' JSON_UNQUOTE(JSON_EXTRACT(col, ‘$.key’)) M（クォートなし） 
SELECT
  name,
  attributes ->  '$.size' AS size_json,  -- "M"
  attributes ->> '$.size' AS size_text   -- M
FROM products
WHERE category = '衣類';

配列要素へのアクセスは0始まりのインデックスを使います。[*]で全要素を取り出すことも可能です。

SELECT
  name,
  attributes ->> '$.tags[0]'           AS first_tag,
  JSON_EXTRACT(attributes, '$.tags[*]') AS all_tags
FROM products
WHERE name = 'Tシャツ（白）';

+------------------+-----------+----------------------+
| name             | first_tag | all_tags             |
+------------------+-----------+----------------------+
| Tシャツ（白）    | casual    | ["casual", "summer"] |
+------------------+-----------+----------------------+

パスの主なルールをまとめます。

  • $ — ドキュメントのルートを表す
  • $.key — オブジェクトのキーアクセス
  • $.key.nested — ネストしたキーへのアクセス
  • $.arr[0] — 配列の0番目の要素（0始まり）
  • $.arr[*] — 配列の全要素（結果は配列として返る）

JSON_SET・JSON_INSERT・JSON_REPLACE・JSON_REMOVEで更新する

JSONカラムを部分的に更新する際は、4種類の関数をキーの存在状況に応じて使い分けます。全体像を把握してから使う関数を選ぶと迷いが減ります。

関数 キーが既存の場合 キーが存在しない場合 主な用途
JSON_SET 上書き 新規追加 汎用的な更新
JSON_INSERT そのまま（変更なし） 新規追加 既存値を守りたいとき
JSON_REPLACE 上書き 変更しない 存在する値だけ変えたいとき
JSON_REMOVE 削除 何もしない キーごと消したいとき

sizeを「L」に変更しつつ、新たにstock（在庫数）を追加する場合はJSON_SETにまとめて書けます。

UPDATE products
SET attributes = JSON_SET(
  attributes,
  '$.size',  'L',
  '$.stock', 50
)
WHERE name = 'Tシャツ（白）';

JSON_SETは「パス, 値, パス, 値, …」と複数ペアをまとめて渡せます。attributes = JSON_SET(attributes, ...)という書き方が基本パターンです。

tagsキーをまるごと削除したい場合はJSON_REMOVEを使います。

UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.tags')
WHERE name = 'Tシャツ（白）';

存在しないキーへのJSON_REMOVEはエラーにならず、元のJSONをそのまま返します。JSON_EXTRACTで存在しないパスを指定した場合も同様にエラーではなくNULLが返ります。

また、配列に要素を追加したい場合はJSON_ARRAY_APPENDが便利です。

-- tagsに "sale" を追加
UPDATE products
SET attributes = JSON_ARRAY_APPEND(attributes, '$.tags', 'sale')
WHERE name = 'Tシャツ（白）';

JSON_TABLEで配列を行展開する（MySQL 8.0以降）

JSON_TABLEはFROM句に記述する仮想テーブル関数で、JSONの配列や入れ子構造を通常の行・列の表に変換します。ports（配列）を1行ずつ展開する例を見てみます。

SELECT
  p.name,
  jt.port
FROM products AS p,
  JSON_TABLE(
    p.attributes,
    '$.ports[*]' COLUMNS (
      port VARCHAR(20) PATH '$'
    )
  ) AS jt
WHERE p.category = '家電';

+------------+--------+
| name       | port   |
+------------+--------+
| ノートPC   | USB-C  |
| ノートPC   | HDMI   |
+------------+--------+

'$.ports[*]'で配列の各要素を1行として展開し、COLUMNS ( port VARCHAR(20) PATH '$' )で列名・型・値のパスを定義しています。コーヒーメーカーはportsキーを持たないため、結果に現れません（NULLとして扱われる）。

オブジェクトの配列を展開する場合は、COLUMNS内に複数の列定義を並べます。

-- [{"key":"CPU","value":"M1"}, ...] のようなオブジェクト配列を展開する例
JSON_TABLE(
  col,
  '$.specs[*]' COLUMNS (
    spec_key   VARCHAR(50)  PATH '$.key',
    spec_value VARCHAR(100) PATH '$.value'
  )
)

展開後の結果セットは通常のテーブルと同様に扱えるため、WHERE・GROUP BY・JOINを自由に組み合わせられます。

よくある落とし穴と注意点

インデックスを直接貼れない

JSON型カラムにはインデックスを直接付与できません。特定キーを使った絞り込み検索を高速化するには、生成列（Generated Column）を介してインデックスを設定します。

ALTER TABLE products
  ADD COLUMN size_col VARCHAR(20)
    AS (attributes ->> '$.size') VIRTUAL,
  ADD INDEX idx_size (size_col);

VIRTUALはディスク上にデータを保持しない仮想列です。STOREDにすると実データとして保存されますが、ストレージを消費します。検索頻度が高いキーにはSTORED＋インデックスの組み合わせが有効です。

->と->>の型ミスマッチ

->はJSON値を返すため、WHERE句で文字列リテラルと比較すると意図しない結果になることがあります。文字列として比較する際は->>を使う習慣をつけましょう。

-- 意図通り動かない場合がある
WHERE attributes -> '$.size' = 'M'

-- 文字列として確実に比較する
WHERE attributes ->> '$.size' = 'M'

JSONドキュメントのサイズと性能

1行のJSONドキュメントが大きくなると、部分更新でも内部でドキュメント全体を再構築するためコストが増します。頻繁に検索・更新するキーは通常のカラムに切り出し、JSONは「固定スキーマに収まらない補助的な属性」だけに留めるのが設計の基本方針です。数十キーを超えるような肥大したJSONはテーブル設計を見直すサインです。

まとめ

MySQLのJSON型は「形が決まらない属性データ」を扱う際の強力な選択肢です。->>で値を取り出し、JSON_SETで更新する流れを押さえれば多くの場面をカバーできます。配列データはJSON_TABLEで行展開でき、検索が必要なキーは生成列とインデックスで補います。「検索頻度が高いキーはカラムに切り出す」という判断基準を持つと、JSON型の乱用を防いで設計を健全に保てます。

参考リンク

アイキャッチ画像: Photo by Jantine Doornbos on Unsplash

タイトルとURLをコピーしました