チームがconfigとevent logをJSON形式でMySQLに保存するようにした頃、最初はスッキリしていた。しかしレポートを出す段になって——フィールドを一つずつ取り出して集計し、他のテーブルとJOINする——ときに痛みを感じた。ネストされたJSON_EXTRACT()のクエリを見ると、もうメンテしたくなくなる。そこで見つけたのがJSON_TABLE()。MySQL 8.0から使える関数だが、一般的なチュートリアルではあまり取り上げられていない。
ネストされたJSONをクエリする3つのアプローチ
ordersテーブルにitemsカラムがあり、JSONアレイとして保存されている場合を想定する：
[
{"product_id": 101, "name": "Laptop", "qty": 1, "price": 25000000},
{"product_id": 205, "name": "Mouse", "qty": 2, "price": 350000}
]「商品ごとの販売数」というレポートが必要な場合、実用的な選択肢は3つある：
アプローチ1：JSON_EXTRACT() / JSON_VALUE()
SELECT
id,
JSON_EXTRACT(items, '$[0].name') AS first_item,
JSON_EXTRACT(items, '$[0].qty') AS first_qty
FROM orders;シンプルだが、インデックスをハードコード（$[0]、$[1]…）しなければならない問題がある。注文ごとにアイテム数が異なる場合、その都度クエリを書き直す必要がある。すべてのitemsをGROUP BYすることもできない。
アプローチ2：アプリケーション層での処理
全JSONをPythonやPHPに取り込んで各要素をループし、コードで集計する方法だ。柔軟だが、アプリサーバーのメモリとCPUを多く消費する。50万行のJSONを処理しようとしてOOMクラッシュしたPHPサービスを見たことがある——深夜のことで、まったく楽しくなかった。
アプローチ3：JSON_TABLE() ——直接リレーショナルテーブルに変換
SELECT
o.id AS order_id,
t.product_id,
t.name,
t.qty,
t.price
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty',
price DECIMAL(15,2) PATH '$.price'
)
) AS t;JSON_TABLE()はJSONアレイを「フラット化」して行にする。そこから通常のSQLが使える：GROUP BY、JOIN、WHERE、集計関数——すべて実際のテーブルと同じように動作する。
各アプローチのメリット・デメリット分析
JSON_EXTRACT() / JSON_VALUE() ——JSON objectから1〜2フィールドだけ取得する場合に適している（複数要素のアレイではなく）。構文が短く、クエリプランもシンプル。誤った場面で使うと、ぐちゃぐちゃなコードになる。
アプリケーション処理 ——柔軟性がある。しかしネットワーク帯域とメモリの消費が大きい——100万行のJSONをアプリに取り込んで集計すると、結果が返る前にリクエストがタイムアウトする。SQLで書くには複雑すぎるロジックの場合にのみ使う。
JSON_TABLE() ——アレイのフラット化と複雑なクエリに最も強力。処理はすべてデータベース側で行われ、MySQLが実行プランを管理する。デメリット：構文が長い、MySQL 8.0+が必要、仮想テーブルにインデックスが直接適用されない。
JSON_TABLE()を選ぶべき場面
JSON_TABLE()はすべてのJSONの問題に使えるわけではないが、次の4つの場面ではこれなしだとかなり辛い：
- JSONカラムにアレイが含まれており、各要素をアンパックしてGROUP BYや集計が必要な場合
- JSONの結果を他のリレーショナルテーブルとJOINする必要がある場合（例：JSON内の
product_idでproductsテーブルとJOIN） - JSONデータの定期レポート——クエリを一度書いて何度も実行する
- データ移行：古いJSONを読み込み、変換して正規スキーマに挿入する
一方、フラットなJSON objectの1フィールドでフィルタするだけなら、JSON_VALUE()の方がシンプルで十分だ。
JSON_TABLE()の実践的な実装ガイド
テストデータのセットアップ
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
items JSON
);
INSERT INTO orders (customer_id, order_date, items) VALUES
(1, '2024-01-15', '[{"product_id":101,"name":"Laptop","qty":1,"price":25000000},{"product_id":205,"name":"Mouse","qty":2,"price":350000}]'),
(2, '2024-01-16', '[{"product_id":101,"name":"Laptop","qty":2,"price":25000000},{"product_id":301,"name":"Keyboard","qty":1,"price":800000}]'),
(1, '2024-01-17', '[{"product_id":205,"name":"Mouse","qty":1,"price":350000}]');商品別総売上レポート
SELECT
t.product_id,
t.name,
SUM(t.qty) AS total_qty,
SUM(t.qty * t.price) AS total_revenue
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty',
price DECIMAL(15,2) PATH '$.price'
)
) AS t
GROUP BY t.product_id, t.name
ORDER BY total_revenue DESC;実際のリレーショナルテーブルへのクエリと同じ結果が得られる——アプリケーション側での追加処理は不要だ。
NESTED PATHによる多層ネストJSONの処理
JSONが多層にネストされている場合——例えば親objectが子アレイを含む場合——NESTED PATHを使う：
-- JSON構造: {"order_id": 1, "customer": {"name": "Nguyen Van A"}, "items": [...]}
SELECT
t.order_id,
t.customer_name,
t.product_name,
t.qty
FROM raw_import_data,
JSON_TABLE(
payload, '$'
COLUMNS (
order_id INT PATH '$.order_id',
customer_name VARCHAR(100) PATH '$.customer.name',
NESTED PATH '$.items[*]' COLUMNS (
product_name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty'
)
)
) AS t;NESTED PATHは子アレイの各アイテムをアンパックしながら親行のコンテキストを保持する——これはJSON_EXTRACT()単体では実現できない。
FOR ORDINALITYでアレイ内の位置を追跡する
SELECT
o.id,
t.row_num, -- 元のアレイ内の位置（1始まり）
t.name,
t.qty
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
row_num FOR ORDINALITY,
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty'
)
) AS t;ON ERROR / ON EMPTYによる非一貫データの処理
プロダクションのJSONデータは形式が乱れることがある——フィールドが欠けていたり、データ型が間違っていたりする。クエリがクラッシュしないようにON EMPTYとON ERRORを追加する：
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id' DEFAULT '0' ON EMPTY DEFAULT '-1' ON ERROR,
name VARCHAR(100) PATH '$.name' DEFAULT 'Unknown' ON EMPTY,
price DECIMAL(15,2) PATH '$.price' DEFAULT '0.00' ON EMPTY
)
)1行のJSONエラーでクエリ全体が失敗する代わりに、デフォルト値を受け取る——プロダクション環境ではるかに安全だ。
プロダクション環境での注意事項
インデックスが直接適用されない ——JSON_TABLE()は仮想行を生成するため、MySQLはJSONカラムのインデックスを高速化に使えない。クエリが遅い場合は、WHEREでよく使うフィールドに対してgenerated columnとインデックスの組み合わせを検討する。
デプロイ前に必ずEXPLAINを実行する ——EXPLAIN FORMAT=JSON SELECT ...で実行プランを確認する。10万行のテーブルで適切なフィルターのないJSON_TABLEクエリは、ミリ秒ではなく数秒かかるフルテーブルスキャンになる。
クエリ前にJSONを検証する ——カラムに無効なJSONがあるとJSON_TABLE()がエラーをスローする。事前に以下でフィルタリングする：
SELECT ...
FROM orders o, JSON_TABLE(...) AS t
WHERE JSON_VALID(o.items) = 1;移行前にバックアップを取る ——午前3時にデータベース障害に遭遇し、バックアップからの復元に2時間以上かかったことがある。それ以来、どのmigrationスクリプトの前にも必ずmysqldumpを実行している。JSON_TABLE()を使ってJSONカラムから正規スキーマへ移行する場合は、先にそのテーブルをダンプする。
JSON_TABLE()のパス式を書く前に、ToolCraftのJSON FormatterでJSON構造を確認するのがおすすめだ——ブラウザ上で素早くフォーマットと検証ができる。完全なクライアントサイドで動作するため、プロダクションデータが外部に送信される心配はない。
まとめ
JSON_TABLE()は特定の問題を解決する：JSONアレイをフラット化して複雑なクエリを可能にする。JSON_EXTRACT()ではこれはできないし、アプリケーション処理では帯域幅とメモリを浪費する。構文は一見長いが、数回書けばすぐに慣れる。GROUP BYやJOINのためだけにアプリ側でJSONをループしているなら、データベース側に移してみよう——クエリがすっきりして、たいていは大幅に速くなる。
