MySQL JSON型の使い方｜JSON_EXTRACT・->>・インデックス一覧

MySQL の JSON 型とは、JSON 文書を検証したうえで専用のバイナリ形式で保存し、パス指定で中身を取り出したり部分的に書き換えたりできるデータ型です。 値を取り出す基本は 列->>'$.キー'（JSON_UNQUOTE(JSON_EXTRACT(列, '$.キー')) の省略形）で、検索を速くするには生成列インデックスや多値インデックスを使います。

この記事では、-> と ->> の違いを実行結果で比べるところから始め、抽出・検索・更新・配列・展開の関数を対応バージョン付きの一覧表にまとめます。さらに、JSON_SET／JSON_REPLACE／JSON_INSERT／JSON_REMOVE の違い、20万行のテーブルでインデックスを付ける前後の EXPLAIN、JSON_TABLE による行展開、不正な JSON を入れたときのエラー文、そして「JSON 型を使わないほうがよい場面」の判断基準までを扱います。出力はすべて MySQL 8.0.46 で実行したものです。

MySQL 8.0 は2026年4月に公式サポートが終了しました。この記事で使う関数と構文はいずれも 8.0 の時点でそろっており、MySQL 8.4／9.7 でも同じ手順で使えます。関数が追加されたバージョンは一覧表に明記します。

MySQL JSON型の早見表

検証に使うテーブルとデータ（コピペ用）

CREATE TABLE members (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  profile JSON NOT NULL
);

INSERT INTO members (profile) VALUES
('{"name":"青木","plan":"gold","age":34,"tags":["sql","java"],"address":{"city":"東京","zip":"100-0001"}}'),
('{"name":"井上","plan":"free","age":28,"tags":["python"],"address":{"city":"大阪"}}'),
(JSON_OBJECT('name','上田','plan','gold','age',41,
             'tags',JSON_ARRAY('sql','go','aws'),'memo',NULL));

SELECT id, profile FROM members;

+----+----------------------------------------------------------------------------------------------------------------------+
| id | profile                                                                                                              |
+----+----------------------------------------------------------------------------------------------------------------------+
|  1 | {"age": 34, "name": "青木", "plan": "gold", "tags": ["sql", "java"], "address": {"zip": "100-0001", "city": "東京"}} |
|  2 | {"age": 28, "name": "井上", "plan": "free", "tags": ["python"], "address": {"city": "大阪"}}                         |
|  3 | {"age": 41, "memo": null, "name": "上田", "plan": "gold", "tags": ["sql", "go", "aws"]}                              |
+----+----------------------------------------------------------------------------------------------------------------------+

登録のしかたは2通りです。JSON の文字列をそのまま渡す方法と、JSON_OBJECT()／JSON_ARRAY() で組み立てる方法で、後者は引用符のエスケープを気にせずに済みます。

出力を見ると、入力した順番ではなく、キーが「短い順、同じ長さなら文字コード順」に並び替えられています（age、name、plan、tags、address）。JSON 型は文字列をそのまま保存するのではなく、検索しやすい内部形式に変換して保存するためです。キーの順番や空白に意味を持たせたい場合は、TEXT 型のほうが向いています。同じキーを2回書いた {"name":"重複","name":"あと勝ち"} を登録すると、後に書いた "あと勝ち" だけが残りました。

-> と ->> の違いを実行結果で比べる

-> は JSON の値として取り出し、->> は引用符を外した文字列として取り出します。

SELECT id,
       profile->'$.name'                            AS a1,
       profile->>'$.name'                           AS a2,
       JSON_EXTRACT(profile, '$.name')              AS a3,
       JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS a4
FROM members;

+----+----------+--------+----------+--------+
| id | a1       | a2     | a3       | a4     |
+----+----------+--------+----------+--------+
|  1 | "青木"   | 青木   | "青木"   | 青木   |
|  2 | "井上"   | 井上   | "井上"   | 井上   |
|  3 | "上田"   | 上田   | "上田"   | 上田   |
+----+----------+--------+----------+--------+

-> と ->> の左側には列名しか書けません。関数の戻り値や文字列リテラルから取り出すときは、JSON_EXTRACT() を使います。

数値は -> と ->> で型が変わる

SELECT profile->'$.age'             AS age,
       profile->'$.age' + 1         AS p1,
       profile->>'$.age'            AS age_s,
       JSON_TYPE(profile->'$.age')  AS t,
       JSON_TYPE(profile->'$.name') AS tn
FROM members WHERE id = 1;

+------+------+-------+---------+--------+
| age  | p1   | age_s | t       | tn     |
+------+------+-------+---------+--------+
| 34   |   35 | 34    | INTEGER | STRING |
+------+------+-------+---------+--------+

-> で取り出した 34 は JSON の INTEGER で、ORDER BY profile->'$.age' DESC は数値の大小で並びました（41、34、28 の順）。->> は文字列になるので、数値として並べたいときは -> を使うか、JSON_VALUE(... RETURNING UNSIGNED) や CAST で型を明示すると意図がはっきりします。

存在しないキーとJSONのnullは別もの

SELECT id,
       profile->'$.memo'         AS memo,
       profile->>'$.memo'        AS memo2,
       profile->'$.memo' IS NULL AS is_sql_null
FROM members;

+----+------+-------+-------------+
| id | memo | memo2 | is_sql_null |
+----+------+-------+-------------+
|  1 | NULL | NULL  |           1 |
|  2 | NULL | NULL  |           1 |
|  3 | null | null  |           0 |
+----+------+-------+-------------+

id 1・2 にはキー memo がないので SQL の NULL が返ります。id 3 は "memo": null という JSON の null を持っており、これは SQL の NULL ではないため IS NULL が 0 になります。「キーがない」と「null が入っている」を区別したいときは JSON_CONTAINS_PATH() でキーの有無を調べます。

パスの書き方一覧

入れ子は . でつなぎ、配列は [番号] で指定します。サンプルで profile->>'$.address.city' は 東京・大阪・NULL（id 3 は address なし）、profile->'$.tags[last]' は "java"・"python"・"aws"、profile->'$.tags[0 to 1]' は ["sql", "java"] のような配列を返しました。存在しないキー $.nothing は NULL です。

パスは必ず $ から始めます。profile->'tags' と書くと ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1. になりました。

JSON_VALUEで型と既定値を指定する

JSON_VALUE()（8.0.21 以降）は、取り出す型と、値がないときの扱いを1つの関数で書けます。

SELECT JSON_VALUE(profile, '$.age' RETURNING UNSIGNED)               AS age,
       JSON_VALUE(profile, '$.address.city')                         AS city,
       JSON_VALUE(profile, '$.address.zip' DEFAULT '不明' ON EMPTY)   AS zip
FROM members;

結果は、age が 34・28・41 の数値、zip は id 1 が 100-0001、キーのない id 2・3 が 不明 でした。

JSON関数の一覧表（対応バージョン付き）

すべて MySQL 8.0.46 で動作を確認した関数です。「追加」の列は MySQL リファレンスマニュアルの記載によります。どれも 8.4／9.7 でそのまま使えます。

抽出・調査

検索

作成・更新

JSON_MERGE() は非推奨で、JSON_MERGE_PRESERVE() が同じ働きをします。

展開・集約

JSONの中身で検索する

-- キーの値で絞る
SELECT id FROM members WHERE profile->>'$.plan' = 'gold';              -- 1, 3

-- 数値で絞る
SELECT id FROM members WHERE profile->'$.age' >= 30;                   -- 1, 3

-- 配列に 'sql' を含む
SELECT id FROM members WHERE 'sql' MEMBER OF (profile->'$.tags');      -- 1, 3
SELECT id FROM members WHERE JSON_CONTAINS(profile, '"sql"', '$.tags'); -- 1, 3

-- 配列が 'sql' と 'go' の両方を含む
SELECT id FROM members WHERE JSON_CONTAINS(profile->'$.tags', '["sql","go"]');   -- 3

-- 配列が 'python' か 'go' のどちらかを含む
SELECT id FROM members WHERE JSON_OVERLAPS(profile->'$.tags', '["python","go"]'); -- 2, 3

コメントの数字は、実際に返った id です。

JSON_CONTAINS() の第2引数は JSON として書く点がつまずきやすいところです。文字列の sql を探すなら、JSON の文字列である '"sql"'（二重引用符つき）を渡します。引用符を付け忘れると次のエラーになりました。

mysql> SELECT id FROM members WHERE JSON_CONTAINS(profile, 'sql', '$.tags');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.

MEMBER OF は左側に SQL の値をそのまま書けるので、1つの値を探すならこちらが書きやすいです。なお '123' MEMBER OF ('[1,123]') は 0 でした。文字列の '123' と数値の 123 は別の値として扱われます。

キーの有無は JSON_CONTAINS_PATH(profile, 'one', '$.address.zip') で調べられ、id 1 だけが 1 でした。JSON_SEARCH(profile, 'one', 'sql') は値が見つかったパス "$.tags[0]" を、JSON_KEYS(profile) は ["age", "name", "plan", "tags", "address"] を返します。

JSON_SET・JSON_REPLACE・JSON_INSERT・JSON_REMOVEの違い

4つの関数の違いは、「すでにあるキー」と「まだないキー」をどう扱うかです。{"a":1,"b":2} に対して、あるキー a と、ないキー c を同時に指定して比べます。

SELECT JSON_SET    ('{"a":1,"b":2}', '$.a', 10, '$.c', 30) AS j_set,
       JSON_INSERT ('{"a":1,"b":2}', '$.a', 10, '$.c', 30) AS j_insert,
       JSON_REPLACE('{"a":1,"b":2}', '$.a', 10, '$.c', 30) AS j_replace,
       JSON_REMOVE ('{"a":1,"b":2}', '$.a', '$.c')         AS j_remove;

+----------------------------+---------------------------+-------------------+----------+
| j_set                      | j_insert                  | j_replace         | j_remove |
+----------------------------+---------------------------+-------------------+----------+
| {"a": 10, "b": 2, "c": 30} | {"a": 1, "b": 2, "c": 30} | {"a": 10, "b": 2} | {"b": 2} |
+----------------------------+---------------------------+-------------------+----------+

これらの関数は新しい JSON を返すだけです。テーブルに反映するには、UPDATE で代入します。

UPDATE members
SET profile = JSON_SET(profile, '$.plan', 'silver', '$.address.city', '京都')
WHERE id = 2;

UPDATE members SET profile = JSON_ARRAY_APPEND(profile, '$.tags', 'docker')   WHERE id = 2;
UPDATE members SET profile = JSON_ARRAY_INSERT(profile, '$.tags[0]', 'linux') WHERE id = 2;
UPDATE members SET profile = JSON_REMOVE(profile, '$.memo')                   WHERE id = 3;

SELECT id, profile FROM members WHERE id IN (2,3);

+----+-------------------------------------------------------------------------------------------------------------------+
| id | profile                                                                                                           |
+----+-------------------------------------------------------------------------------------------------------------------+
|  2 | {"age": 28, "name": "井上", "plan": "silver", "tags": ["linux", "python", "docker"], "address": {"city": "京都"}} |
|  3 | {"age": 41, "name": "上田", "plan": "gold", "tags": ["sql", "go", "aws"]}                                         |
+----+-------------------------------------------------------------------------------------------------------------------+

親のキーがないと、入れ子の追加は何も起きない

上の出力の id 3 には、実はもう1つ UPDATE を実行していました。

UPDATE members SET profile = JSON_SET(profile, '$.address.city', '福岡') WHERE id = 3;

id 3 にはもともと address がありません。この場合、JSON_SET は途中のオブジェクトを自動では作らず、エラーにもならず、何も変えません。出力に address が現れていないのはそのためです。親ごと作るときは、オブジェクトを渡します。

UPDATE members
SET profile = JSON_SET(profile, '$.address', JSON_OBJECT('city','福岡'))
WHERE id = 3;

文字列で渡したJSONは「文字列」として入る

値に JSON を入れたいとき、文字列リテラルで渡すと JSON の文字列として保存されます。

UPDATE members
SET profile = JSON_SET(profile,
                       '$.opt',  '{"x":1}',                 -- 文字列として入る
                       '$.opt2', CAST('{"x":1}' AS JSON),   -- オブジェクトとして入る
                       '$.flag', true,
                       '$.n',    NULL)
WHERE id = 3;
SELECT profile FROM members WHERE id = 3;

{"n": null, "age": 41, "opt": "{\"x\":1}", "flag": true, "name": "上田", "opt2": {"x": 1}, "plan": "gold", "tags": ["sql", "go", "aws"], "address": {"city": "福岡"}}

opt は "{\"x\":1}" という文字列、opt2 は {"x": 1} というオブジェクトになりました。オブジェクトや配列を入れるときは CAST(... AS JSON)、JSON_OBJECT()、JSON_ARRAY() のいずれかを使います。

2つのJSONを統合する

JSON_MERGE_PATCH('{"a":1,"b":{"x":1,"y":2},"t":[1]}', '{"b":{"y":null,"z":3},"t":[2],"c":5}') は {"a": 1, "b": {"x": 1, "z": 3}, "c": 5, "t": [2]} を返しました。「差分を当てる」動きで、null を指定したキー（b.y）は削除され、配列は丸ごと置き換わります。JSON_MERGE_PRESERVE('{"a":1,"t":[1]}', '{"a":2,"t":[2]}') は {"a": [1, 2], "t": [1, 2]} で、両方の値を配列にまとめて残します。

JSON_TABLEで配列を行に展開する

JSON_TABLE() は、JSON の配列を「1要素1行」の表に変換します。FROM 句に書き、通常の表と同じように JOIN・GROUP BY できます。

SELECT m.id, m.profile->>'$.name' AS name, t.ord, t.tag
FROM members m,
     JSON_TABLE(m.profile, '$.tags[*]'
                COLUMNS (ord FOR ORDINALITY,
                         tag VARCHAR(20) PATH '$')) AS t
ORDER BY m.id, t.ord;

+----+--------+------+--------+
| id | name   | ord  | tag    |
+----+--------+------+--------+
|  1 | 青木   |    1 | sql    |
|  1 | 青木   |    2 | java   |
|  2 | 井上   |    1 | linux  |
|  2 | 井上   |    2 | python |
|  2 | 井上   |    3 | docker |
|  3 | 上田   |    1 | sql    |
|  3 | 上田   |    2 | go     |
|  3 | 上田   |    3 | aws    |
+----+--------+------+--------+

行に展開できれば、あとは普通の SQL です。GROUP BY t.tag で COUNT(*) を取ると、sql が 2、ほかのタグが 1 ずつ、と集計できました。

オブジェクトの配列も、列ごとにパスを指定して展開できます。値がないとき（ON EMPTY）と型が合わないとき（ON ERROR）の扱いも列ごとに書けます。

SELECT t.*
FROM JSON_TABLE(
  '[{"sku":"A1","qty":2,"opt":{"color":"red"}},{"sku":"B2","qty":"x"},{"sku":"C3"}]',
  '$[*]' COLUMNS (
    row_no  FOR ORDINALITY,
    sku     VARCHAR(10) PATH '$.sku',
    qty     INT         PATH '$.qty' DEFAULT '0' ON EMPTY NULL ON ERROR,
    color   VARCHAR(10) PATH '$.opt.color' DEFAULT '"none"' ON EMPTY,
    has_opt INT EXISTS  PATH '$.opt')
) AS t;

+--------+------+------+-------+---------+
| row_no | sku  | qty  | color | has_opt |
+--------+------+------+-------+---------+
|      1 | A1   |    2 | red   |       1 |
|      2 | B2   | NULL | none  |       0 |
|      3 | C3   |    0 | none  |       0 |
+--------+------+------+-------+---------+

2行目の qty は "x" で INT に変換できないので NULL ON ERROR が、3行目は qty がないので DEFAULT '0' ON EMPTY が効いています。DEFAULT の値は JSON として書くので、文字列は '"none"' と二重引用符で囲みます。

逆に、行を JSON にまとめるには集約関数を使います。

SELECT JSON_ARRAYAGG(profile->>'$.name')         AS names,
       JSON_OBJECTAGG(id, profile->>'$.plan')    AS plans
FROM members;
-- names: ["青木", "井上", "上田"]
-- plans: {"1": "gold", "2": "silver", "3": "gold"}

JSON型にインデックスを付ける：EXPLAINで比べる

JSON 型の列そのものには、通常のインデックスを作成できません。 中の値を取り出す式に対してインデックスを作ります。方法は3つあります。

20万行のテーブルを用意して、付ける前後を比べます。

SET SESSION cte_max_recursion_depth = 300000;
CREATE TABLE member_big (id INT PRIMARY KEY, profile JSON NOT NULL);
INSERT INTO member_big
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000)
SELECT n, JSON_OBJECT('name',  CONCAT('user', n),
                      'email', CONCAT('user', n, '@example.com'),
                      'age',   20 + n % 50,
                      'plan',  ELT(1 + n % 4, 'free','free','silver','gold'),
                      'tags',  JSON_ARRAY(n % 500, (n * 7) % 500, 1000 + n % 3))
FROM s;

インデックスなし：全件を読む

EXPLAIN ANALYZE
SELECT id FROM member_big WHERE profile->>'$.email' = 'user12345@example.com'\G

-> Filter: (json_unquote(json_extract(member_big.`profile`,'$.email')) = 'user12345@example.com')  (cost=20288 rows=198313) (actual time=7.14..106 rows=1 loops=1)
    -> Table scan on member_big  (cost=20288 rows=198313) (actual time=0.00912..71.7 rows=200000 loops=1)

1件を探すために20万行をすべて読み、約106ミリ秒かかっています。通常の EXPLAIN では type: ALL、key: NULL でした。

生成列インデックス：type が ALL から ref に変わる

ALTER TABLE member_big
  ADD COLUMN email VARCHAR(100)
      GENERATED ALWAYS AS (profile->>'$.email') VIRTUAL,
  ADD INDEX idx_email (email);

EXPLAIN SELECT id FROM member_big WHERE profile->>'$.email' = 'user12345@example.com';

+----+-------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table      | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | member_big | ref  | idx_email     | idx_email | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

（partitions 列は省略しています。）WHERE を profile->>'$.email' = ... のまま変えていないのに、idx_email が使われています。 MySQL は、WHERE の式が生成列の定義と一致すると、その生成列のインデックスに読み替えます。EXPLAIN ANALYZE では次の1行になり、実行時間は約0.04ミリ秒でした。

-> Index lookup on member_big using idx_email (email='user12345@example.com')  (cost=0.35 rows=1) (actual time=0.0363..0.0387 rows=1 loops=1)

WHERE email = '...' と生成列を直接書いても、JSON_UNQUOTE(JSON_EXTRACT(profile,'$.email')) = '...' と関数で書いても、同じく idx_email が選ばれました。一方、生成列を作っていない profile->>'$.name' = 'user12345' は type: ALL のままです。

VIRTUAL の生成列は値をテーブルに保存せず、インデックスにだけ持つので、追加による行の増加はありません。

関数インデックス：列を増やさずに作る

生成列を表に出したくないときは、式に直接インデックスを作れます（内部的には隠れた生成列が作られます）。

ALTER TABLE member_big ADD INDEX idx_age ((CAST(profile->>'$.age' AS UNSIGNED)));

EXPLAIN SELECT COUNT(*) FROM member_big
WHERE CAST(profile->>'$.age' AS UNSIGNED) = 33;
-- type: ref / key: idx_age / rows: 4000

->> は LONGTEXT を返すため、そのままではインデックスにできません。

mysql> ALTER TABLE member_big ADD INDEX idx_plan ((profile->>'$.plan'));
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

文字列の場合は、CAST で長さを決め、照合順序を utf8mb4_bin にそろえます。

ALTER TABLE member_big
  ADD INDEX idx_plan ((CAST(profile->>'$.plan' AS CHAR(10)) COLLATE utf8mb4_bin));

EXPLAIN SELECT COUNT(*) FROM member_big WHERE profile->>'$.plan' = 'gold';
-- type: ref / key: idx_plan

COLLATE utf8mb4_bin を付けるのは、->>（JSON_UNQUOTE）の結果の照合順序が utf8mb4_bin で、CAST の結果の既定とは異なるためです。そろえておくと、profile->>'$.plan' = 'gold' という素直な WHERE でインデックスが使われました。JSON_VALUE(profile, '$.age' RETURNING UNSIGNED) を使った関数インデックスも作成でき、同じ式の WHERE で type: ref になることを確認しています。

多値インデックス：配列の検索が MEMBER OF で速くなる

配列の各要素にインデックスを張るのが多値インデックス（multi-valued index）です。CAST(... AS 型 ARRAY) と書きます。

ALTER TABLE member_big
  ADD INDEX idx_tags ((CAST(profile->'$.tags' AS UNSIGNED ARRAY)));

EXPLAIN ANALYZE
SELECT id FROM member_big WHERE 123 MEMBER OF (profile->'$.tags')\G

インデックスを作る前：

-> Filter: <cache>(123) member of (json_extract(member_big.`profile`,'$.tags'))  (cost=20288 rows=198313) (actual time=0.105..126 rows=800 loops=1)
    -> Table scan on member_big  (cost=20288 rows=198313) (actual time=0.0146..72 rows=200000 loops=1)

作った後：

-> Filter: json'123' member of (cast(json_extract(`profile`,'$.tags') as unsigned array))  (cost=280 rows=800) (actual time=0.0755..3.15 rows=800 loops=1)
    -> Index lookup on member_big using idx_tags (cast(json_extract(`profile`,'$.tags') as unsigned array)=json'123')  (cost=280 rows=800) (actual time=0.0727..2.91 rows=800 loops=1)

（インデックスを別の文字セット設定のクライアントから作成したため、実際の出力には式中に _latin1 という文字セット指定が表示されました。ここでは省いています。）Table scan（20万行）が Index lookup（800行）に変わり、約126ミリ秒が約3ミリ秒になりました。

多値インデックスが使われる書き方と、使われなかった書き方を並べます。

インデックスの定義と同じ profile->'$.tags' という式を WHERE に書くのがポイントです。4行目のようにパスを第3引数に分けると、今回の環境ではインデックスが選ばれませんでした。

多値インデックスを付けると、配列の中身は宣言した型に合っている必要があります。UNSIGNED ARRAY の列に文字列の配列を入れようとすると、更新がエラーで止まりました。

mysql> UPDATE member_big SET profile = JSON_SET(profile, '$.tags', JSON_ARRAY('abc')) WHERE id = 1;
ERROR 3903 (22018): Invalid JSON value for CAST for functional index 'idx_tags'.

不正なJSONを入れたときのエラー

JSON 型は、登録の時点で構文を検査します。よくある書き間違いを試しました。

INSERT INTO members (profile) VALUES ('{"name":"遠藤",}');    -- 末尾のカンマ
INSERT INTO members (profile) VALUES ("{'name':'遠藤'}");     -- 一重引用符
INSERT INTO members (profile) VALUES ('');                    -- 空文字
INSERT INTO members (profile) VALUES ('abc');                 -- 引用符なしの文字

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 24 in value for column 'members.profile'.
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'members.profile'.
ERROR 3140 (22032): Invalid JSON text: "The document is empty." at position 0 in value for column 'members.profile'.
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'members.profile'.

エラー文の at position N が、問題のある位置（0始まりのバイト位置）を教えてくれます。事前に確かめたいときは JSON_VALID() が使えます。

JSON_VALID('{"a":1}') は 1、JSON_VALID('{"a":1,}') は 0、JSON_VALID('123') と JSON_VALID('"abc"') は 1、JSON_VALID('abc') は 0 でした。

123 や "abc" のようなスカラー値も、正しい JSON として受け付けられます。「必ずオブジェクトで、name を持つ」といった形まで保証したいときは、CHECK 制約に JSON_SCHEMA_VALID() を書きます。

ALTER TABLE members ADD CONSTRAINT chk_profile
  CHECK (JSON_SCHEMA_VALID('{"type":"object","required":["name"]}', profile));

INSERT INTO members (profile) VALUES ('{"plan":"free"}');

ERROR 3819 (HY000): Check constraint 'chk_profile' is violated.

どこが合わなかったかは JSON_SCHEMA_VALIDATION_REPORT() で調べられます。age に minimum: 0 を指定したスキーマへ {"name":"x","age":-1} を渡すと、"reason": "The JSON document location '#/age' failed requirement 'minimum' at JSON Schema location '#/properties/age'" を含むレポートが返りました。

そのほか、関数やパスの書き間違いで出るエラーをまとめます。

JSON型を使うべきでないケースの判断基準

JSON 型は便利ですが、通常の列で持ったほうが扱いやすい場面もあります。

迷ったら、次の3つで整理できます。

1. その値を WHERE・JOIN・ORDER BY に使うか。 使うなら通常の列か、少なくとも生成列にしてインデックスを張る。
2. その値に制約（必須・一意・範囲・参照）を掛けたいか。 掛けたいなら通常の列が素直。JSON でも CHECK と JSON_SCHEMA_VALID() で一部は表現できる。
3. 項目が行ごとに変わるか。 変わるなら JSON 型の出番。

「よく使う項目は通常の列、可変な部分だけ JSON」という組み合わせが扱いやすい形です。

サイズと部分更新の目安

JSON 型の列は、max_allowed_packet の値までの文書を保存できます。保存サイズは JSON_STORAGE_SIZE() で分かり、検証用の id 1 の文書は 134 バイトでした。

JSON_SET・JSON_REPLACE・JSON_REMOVE で、同じ列を profile = JSON_SET(profile, ...) の形で更新し、新しい値がもとの値より大きくならない場合、MySQL 8.0 は文書全体を書き直さず一部だけを更新します。空いた領域は JSON_STORAGE_FREE() に現れます。

member_big で 'silver' を 'f' に JSON_SET したところ、JSON_STORAGE_SIZE は 104 のまま、JSON_STORAGE_FREE が 0 から 5 に増えました。

PostgreSQL で同じ用途に使う jsonb 型については、「PostgreSQL jsonb操作一覧」の記事にまとめています。

よくある質問

Q. -> と ->> はどちらを使えばよいですか？ A. 文字列として表示・比較したいときは ->>、配列やオブジェクトを取り出すとき・数値として比較や並べ替えをしたいときは -> です。-> で取り出した文字列には "青木" のように二重引用符が付くので、画面に出す値には ->> を使うと扱いやすくなります。

Q. JSON型の列にインデックスは張れますか？ A. 列そのものには張れませんが、中の値には張れます。単一の値は生成列＋インデックスか関数インデックス、配列は多値インデックス（CAST(... AS UNSIGNED ARRAY)）を使います。20万行の検証では、多値インデックスで MEMBER OF の検索が約126ミリ秒から約3ミリ秒になりました。

Q. JSON_EXTRACT で取り出した値に二重引用符が付きます。外すには？ A. JSON_UNQUOTE() で包むか、->> を使います。profile->>'$.name' は JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) と同じ結果になります。

Q. JSON の一部だけを更新できますか？ A. UPDATE 表 SET 列 = JSON_SET(列, '$.キー', 値) で、指定したキーだけを書き換えられます。あるときだけ更新するなら JSON_REPLACE、ないときだけ追加するなら JSON_INSERT、削除は JSON_REMOVE です。親のキーがない入れ子のパスを指定しても、途中のオブジェクトは自動では作られない点に注意してください。

まとめ

• -> は JSON のまま、->> は引用符を外した文字列。->> は JSON_UNQUOTE(JSON_EXTRACT()) の省略形。
• 数値の比較・並べ替えは -> か JSON_VALUE(... RETURNING 型)。キーがない（SQL の NULL）と JSON の null は別もの。
• JSON_SET は追加と上書き、JSON_INSERT は追加だけ、JSON_REPLACE は上書きだけ、JSON_REMOVE は削除。親のキーがない入れ子の追加は何も起きない。
• オブジェクトや配列を値として入れるときは CAST(... AS JSON) か JSON_OBJECT()／JSON_ARRAY()。文字列で渡すと文字列として保存される。
• インデックスは式に対して張る。単一の値は生成列または関数インデックス、配列は多値インデックスで、EXPLAIN の type が ALL から ref／range に変わる。
• JSON_TABLE で配列を行に展開すれば、GROUP BY や JOIN がそのまま使える。
• 検索・結合・制約に使う値は通常の列、行ごとに変わる可変な部分は JSON 型、と分けると扱いやすい。

株式会社RJCでは、MySQLのJSON型の使い方やインデックスの考え方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。