MochiuWiki : SUSE, EC, PCB

MySQL - JSON関数

2026年2月13日 (金) 08:35時点におけるWiki (トーク | 投稿記録)による版 (ページの作成:「== 概要 == MySQLは、RFC 7159準拠のネイティブJSON型をサポートしている。<br> JSON型は、MySQL 5.7.8で初めて導入され、MySQL 8.0で大幅に機能が拡張された。<br> <br> JSON値は、テキスト形式で入力されるが、内部ではバイナリ形式で保存される。<br> バイナリ形式により、パース処理を省略し、効率的なパス指定による値の抽出と変更を実現している。<br> <br> MySQ…」)
(差分) ← 古い版 | 最新版 (差分) | 新しい版 → (差分)

概要

MySQLは、RFC 7159準拠のネイティブJSON型をサポートしている。
JSON型は、MySQL 5.7.8で初めて導入され、MySQL 8.0で大幅に機能が拡張された。

JSON値は、テキスト形式で入力されるが、内部ではバイナリ形式で保存される。
バイナリ形式により、パース処理を省略し、効率的なパス指定による値の抽出と変更を実現している。

MySQLのJSON型には、以下に示す特徴がある。

  • 自動検証
    無効なJSON値はエラーとして拒否される
  • 効率的なストレージ
    バイナリ形式での内部保存により、高速なアクセスを実現
  • パス式による柔軟な参照
    JSONドキュメント内の任意の要素を直接参照可能
  • 部分更新のサポート
    JSON値全体を書き換えることなく、一部の要素のみを更新可能
  • インデックス対応
    Generated Columns や Multi-Valued Index によるインデックス作成が可能



JSON型

JSON型の定義

JSON型のカラムは、通常のデータ型と同様に定義できる。

定義例を以下に示す。

 CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
 );


JSON型のカラムには、JSON形式の値を直接挿入できる。

挿入例を以下に示す。

 INSERT INTO users (id, data) VALUES
   (1, '{"name": "Alice", "age": 30, "city": "Tokyo"}'),
   (2, '{"name": "Bob", "age": 25, "city": "Osaka"}');


JSON値は、挿入時に自動的に検証され、バイナリ形式に変換されて保存される。
無効なJSON形式の値を挿入しようとすると、エラーが返される。

JSON値の最大サイズは、max_allowed_packet システム変数によって制限される。(デフォルト: 4[MB])

JSON値の検証

MySQLは、JSON値の挿入時に自動的に妥当性検証を実行する。

以下の条件を満たさない値は、エラーとして拒否される。

  • JSON null、true、falseは小文字である必要がある。
    NULL、True、False は不可
  • 文字列はダブルクォートで囲まれている必要がある。
    シングルクォートは不可
  • オブジェクトのキーは文字列である必要がある。
    数値やnullはキーとして使用不可


SQL NULL値とJSON null値は異なる値である。
SQL NULLは、ソート時にJSON nullよりも前に配置される。

JSON_VALID() 関数により、値の妥当性を明示的に検証できる。

検証例を以下に示す。

 SELECT JSON_VALID('{"name": "Alice"}');   -- 1 (valid)
 
 SELECT JSON_VALID('{name: "Alice"}');     -- 0 (invalid: キーがクォートされていない)

 SELECT JSON_VALID('{"name": \'Alice\'}'); -- 0 (invalid: シングルクォート使用)



JSON値の作成

JSON_ARRAY() / JSON_OBJECT()

JSON_ARRAY() 関数は、引数からJSON配列を作成する。
JSON_OBJECT() 関数は、キーと値のペアからJSONオブジェクトを作成する。

JSON_ARRAY() 関数の構文を以下に示す。

JSON_ARRAY([value[, value] ...])


JSON_OBJECT() 関数の構文を以下に示す。

JSON_OBJECT([key, value[, key, value] ...])


使用例を以下に示す。

 SELECT JSON_ARRAY(1, 2, 3, 'text', NULL);
 -- 結果: [1, 2, 3, "text", null]
 
 SELECT JSON_OBJECT('name', 'Alice', 'age', 30, 'active', true);
 -- 結果: {"age": 30, "name": "Alice", "active": true}


JSON_ARRAY() 関数 と JSON_OBJECT() 関数 の動作を以下に示す。

  • SQL NULL値は、JSON nullに自動変換される。
  • JSON_OBJECT() 関数のキーが重複する場合、最後の値が採用される。
  • 引数が空の場合、空の配列またはオブジェクトが返される。


JSON_QUOTE() 関数は、文字列をJSON文字列としてクォートする。

構文を以下に示す。

JSON_QUOTE(string)


使用例を以下に示す。

 SELECT JSON_QUOTE('Hello "World"');
 -- 結果: "Hello \"World\""


CAST() / JSON_TYPE()

CAST() 関数により、他のデータ型からJSON型へのキャストが可能である。

構文を以下に示す。

CAST(value AS JSON)


使用例を以下に示す。

 SELECT CAST('[1, 2, 3]' AS JSON);
 -- 結果: [1, 2, 3]
 
 SELECT CAST('{"name": "Alice"}' AS JSON);
 -- 結果: {"name": "Alice"}


JSON_TYPE() 関数は、JSON値の型を返す。

構文を以下に示す。

JSON_TYPE(json_value)


下表に、返される型を示す。

JSON_TYPE関数の返される型
型 説明
OBJECT JSONオブジェクト
ARRAY JSON配列
STRING JSON文字列
INTEGER JSON整数
DOUBLE JSON浮動小数点数
BOOLEAN JSON真偽値 (true、false)
NULL JSON null


使用例を以下に示す。

 SELECT JSON_TYPE('{"name": "Alice"}');  -- OBJECT
 SELECT JSON_TYPE('[1, 2, 3]');          -- ARRAY
 SELECT JSON_TYPE('"text"');             -- STRING
 SELECT JSON_TYPE('123');                -- INTEGER
 SELECT JSON_TYPE('12.5');               -- DOUBLE
 SELECT JSON_TYPE('true');               -- BOOLEAN
 SELECT JSON_TYPE('null');               -- NULL



JSON値の参照

JSON_EXTRACT()

JSON_EXTRACT() 関数は、パス式を使用してJSON値から要素を抽出する。

構文を以下に示す。

JSON_EXTRACT(json_doc, path[, path] ...)


パス式は、抽出する要素の位置を指定する。
複数のパスを指定した場合、抽出された値は配列として返される。

使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30, "address": {"city": "Tokyo", "zip": "100-0001"}}';
 
 SELECT JSON_EXTRACT(@json, '$.name');
 -- 結果: "Alice"
 
 SELECT JSON_EXTRACT(@json, '$.age');
 -- 結果: 30
 
 SELECT JSON_EXTRACT(@json, '$.address.city');
 -- 結果: "Tokyo"
 
 SELECT JSON_EXTRACT(@json, '$.name', '$.age');
 -- 結果: ["Alice", 30]


配列要素の抽出例を以下に示す。

 SET @array = '[10, 20, 30, 40, 50]';
 
 SELECT JSON_EXTRACT(@array, '$[0]');  -- 10
 SELECT JSON_EXTRACT(@array, '$[2]');  -- 30
 SELECT JSON_EXTRACT(@array, '$[4]');  -- 50


存在しないパスを指定した場合、NULL が返される。

->演算子 と ->>演算子

MySQL 5.7.9以降では、パス式による要素参照のショートカット演算子が提供されている。

->演算子は、JSON_EXTRACT() 関数のショートカットである。
->>演算子は、JSON_UNQUOTE(JSON_EXTRACT()) 関数のショートカットである。

構文を以下に示す。

column -> path
column ->> path


->演算子 と ->>演算子の違いを以下に示す。

-> 演算子と->> 演算子の比較
演算子 等価な関数 返される値 用途
-> JSON_EXTRACT() JSON値 (クォート付き) JSON値として扱う場合
->> JSON_UNQUOTE(JSON_EXTRACT()) アンクォートされた文字列 文字列として扱う場合


使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30}';
 
 SELECT @json -> '$.name';
 -- 結果: "Alice" (ダブルクォート付き)
 
 SELECT @json ->> '$.name';
 -- 結果: Alice (ダブルクォートなし)


WHERE句での使用例を以下に示す。

 SELECT * FROM users WHERE data ->> '$.city' = 'Tokyo';


パス式の構文

パス式は、JSON値内の要素を指定するための構文である。

下表に、パス式の基本構文を示す。

パス式の構文
構文 説明 例
$ ルート要素 $ → JSONドキュメント全体
.key オブジェクトのキー指定 $.name → nameキーの値
."key" キーにスペースや特殊文字を含む場合 $."first name" → first nameキーの値
.* オブジェクトの全メンバー $.* → 全てのキーの値を配列で返す
[index] 配列の要素指定 (0-indexed) $[0] → 配列の最初の要素
[*] 配列の全要素 $[*] → 配列の全要素を配列で返す
** 再帰的検索 $**.name → 全ての階層のnameキーの値
[M to N] 配列の範囲指定 (M〜N番目) $[1 to 3] → 配列の2〜4番目の要素


  • パス式の使用例 
     SET @json = '{
    "name": "Alice",
    "scores": [85, 90, 78],
    "address": {
       "city": "Tokyo",
       "zip": "100-0001"
    }
 }';
 
 SELECT JSON_EXTRACT(@json, '$.scores[0]');
 -- 結果: 85
 
 SELECT JSON_EXTRACT(@json, '$.scores[*]');
 -- 結果: [85, 90, 78]
 
 SELECT JSON_EXTRACT(@json, '$.*');
 -- 結果: ["Alice", [85, 90, 78], {"city": "Tokyo", "zip": "100-0001"}]
 
 SELECT JSON_EXTRACT(@json, '$.scores[1 to 2]');
 -- 結果: [90, 78]

  • 再帰的検索 (**) の例 
     SET @json = '{
    "name": "Alice",
    "address": {
       "city": "Tokyo",
       "office": {
          "name": "Headquarters"
       }
    }
 }';
 
 SELECT JSON_EXTRACT(@json, '$**.name');
 -- 結果: ["Alice", "Headquarters"]


JSON_KEYS() / JSON_LENGTH()

JSON_KEYS() 関数は、JSONオブジェクトのキー一覧を配列として返す。

構文を以下に示す。

JSON_KEYS(json_doc[, path])


使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30, "city": "Tokyo"}';
 
 SELECT JSON_KEYS(@json);
 -- 結果: ["name", "age", "city"]
 
 SET @nested = '{"user": {"name": "Alice", "age": 30}}';
 
 SELECT JSON_KEYS(@nested, '$.user');
 -- 結果: ["name", "age"]


JSON_LENGTH() 関数は、JSON値の要素数を返す。

構文を以下に示す。

JSON_LENGTH(json_doc[, path])


要素数の計算方法を以下に示す。

  • 配列
    要素の数
  • オブジェクト
    キーの数
  • スカラー値
    1


使用例を以下に示す。

 SELECT JSON_LENGTH('[1, 2, 3, 4, 5]');
 -- 結果: 5
 
 SELECT JSON_LENGTH('{"name": "Alice", "age": 30}');
 -- 結果: 2
 
 SELECT JSON_LENGTH('"text"');
 -- 結果: 1


JSON_DEPTH() 関数は、JSON値の最大ネスト深さを返す。

構文を以下に示す。

JSON_DEPTH(json_doc)


使用例を以下に示す。

 SELECT JSON_DEPTH('{}');
 -- 結果: 1
 
 SELECT JSON_DEPTH('{"a": {"b": {"c": {}}}}');
 -- 結果: 4
 
 SELECT JSON_DEPTH('[1, [2, [3, 4]]]');
 -- 結果: 4



JSON値の変更

JSON_SET() / JSON_INSERT() / JSON_REPLACE()

MySQLは、JSON値を部分的に変更するための3つの関数を提供している。

構文を以下に示す。

JSON_SET(json_doc, path, value[, path, value] ...)
JSON_INSERT(json_doc, path, value[, path, value] ...)
JSON_REPLACE(json_doc, path, value[, path, value] ...)


各関数の動作の違いを以下に示す。

JSON_SET / INSERT / REPLACE の比較
関数 パスが存在する場合 パスが存在しない場合 用途
JSON_SET() 値を置換 値を挿入 常に値を設定
JSON_INSERT() 何もしない 値を挿入 新しい値のみを挿入
JSON_REPLACE() 値を置換 何もしない 既存の値のみを更新


使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30}';
 
 SELECT JSON_SET(@json, '$.age', 31, '$.city', 'Tokyo');
 -- 結果: {"name": "Alice", "age": 31, "city": "Tokyo"}
 -- age は置換、city は追加
 
 SELECT JSON_INSERT(@json, '$.age', 31, '$.city', 'Tokyo');
 -- 結果: {"name": "Alice", "age": 30, "city": "Tokyo"}
 -- age は既存のため変更されない、city は追加
 
 SELECT JSON_REPLACE(@json, '$.age', 31, '$.city', 'Tokyo');
 -- 結果: {"name": "Alice", "age": 31}
 -- age は置換、city は存在しないため追加されない


  • UPDATE文での使用例 
     UPDATE users
 SET data = JSON_SET(data, '$.age', 31)
 WHERE id = 1;

  • 複数のパスを同時に更新する例 
     UPDATE users
 SET data = JSON_SET(
    data,
    '$.age', 31,
    '$.city', 'Tokyo',
    '$.updated_at', NOW()
 )
 WHERE id = 1;


JSON_REMOVE()

JSON_REMOVE() 関数は、指定されたパスの要素を削除する。

構文を以下に示す。

JSON_REMOVE(json_doc, path[, path] ...)


使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30, "city": "Tokyo"}';
 
 SELECT JSON_REMOVE(@json, '$.age');
 -- 結果: {"name": "Alice", "city": "Tokyo"}
 
 SELECT JSON_REMOVE(@json, '$.age', '$.city');
 -- 結果: {"name": "Alice"}


  • 配列要素の削除例 
     SET @array = '[10, 20, 30, 40, 50]';
 
 SELECT JSON_REMOVE(@array, '$[2]');
 -- 結果: [10, 20, 40, 50]

  • UPDATE文での使用例 
     UPDATE users
 SET data = JSON_REMOVE(data, '$.age')
 WHERE id = 1;


JSON_ARRAY_APPEND() / JSON_ARRAY_INSERT()

JSON_ARRAY_APPEND() 関数は、配列の末尾に要素を追加する。
JSON_ARRAY_INSERT() 関数は、配列の指定位置に要素を挿入する。

構文を以下に示す。

JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)
JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)


  • JSON_ARRAY_APPEND() の使用例 
     SET @array = '[10, 20, 30]';
 
 SELECT JSON_ARRAY_APPEND(@array, '$', 40);
 -- 結果: [10, 20, 30, 40]

  • JSON_ARRAY_INSERT() の使用例 
     SET @array = '[10, 20, 30]';
 
 SELECT JSON_ARRAY_INSERT(@array, '$[1]', 15);
 -- 結果: [10, 15, 20, 30]

  • ネストした配列への操作例 
     SET @json = '{"scores": [85, 90, 78]}';
 
 SELECT JSON_ARRAY_APPEND(@json, '$.scores', 95);
 -- 結果: {"scores": [85, 90, 78, 95]}



JSON検索関数

JSON_CONTAINS()

JSON_CONTAINS() 関数は、JSON値が指定された候補値を含むかどうかを検証する。

構文を以下に示す。

JSON_CONTAINS(target, candidate[, path])


返り値を以下に示す。

  • 1
    候補値が含まれる
  • 0
    候補値が含まれない
  • NULL
    引数のいずれかがNULL


使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30, "city": "Tokyo"}';
 
 SELECT JSON_CONTAINS(@json, '"Alice"', '$.name');
 -- 結果: 1
 
 SELECT JSON_CONTAINS(@json, '30', '$.age');
 -- 結果: 1
 
 SELECT JSON_CONTAINS(@json, '"Osaka"', '$.city');
 -- 結果: 0


  • 配列での使用例 
     SET @array = '[1, 2, 3, 4, 5]';
 
 SELECT JSON_CONTAINS(@array, '3');
 -- 結果: 1
 
 SELECT JSON_CONTAINS(@array, '6');
 -- 結果: 0

  • WHERE句での使用例 
     SELECT * FROM users
 WHERE JSON_CONTAINS(data, '"Tokyo"', '$.city');


JSON_CONTAINS_PATH()

JSON_CONTAINS_PATH() 関数は、指定されたパスがJSON値内に存在するかどうかを検証する。

構文を以下に示す。

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)


one_or_allパラメータの動作を以下に示す。

  • 'one'
    少なくとも1つのパスが存在すれば1を返す
  • 'all'
    全てのパスが存在する場合のみ1を返す


使用例を以下に示す。

 SET @json = '{"name": "Alice", "age": 30}';
 
 SELECT JSON_CONTAINS_PATH(@json, 'one', '$.name', '$.city');
 -- 結果: 1 (name は存在する)
 
 SELECT JSON_CONTAINS_PATH(@json, 'all', '$.name', '$.city');
 -- 結果: 0 (city は存在しない)
 
 SELECT JSON_CONTAINS_PATH(@json, 'all', '$.name', '$.age');
 -- 結果: 1 (両方とも存在する)


JSON_SEARCH()

JSON_SEARCH() 関数は、JSON値内から文字列を検索し、そのパスを返す。

構文を以下に示す。

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path]])


one_or_allパラメータの動作を以下に示す。

  • 'one'
    最初に一致したパスを返す
  • 'all'
    一致した全てのパスを配列で返す


使用例を以下に示す。

 SET @json = '{"name": "Alice", "address": {"city": "Tokyo", "country": "Japan"}}';
 
 SELECT JSON_SEARCH(@json, 'one', 'Tokyo');
 -- 結果: "$.address.city"
 
 SET @json2 = '[{"name": "Alice"}, {"name": "Bob"}, {"name": "Alice"}]';
 
 SELECT JSON_SEARCH(@json2, 'all', 'Alice');
 -- 結果: ["$[0].name", "$[2].name"]


ワイルドカードを使用した検索例を以下に示す。

 SET @json = '{"email": "alice@example.com"}';
 
 SELECT JSON_SEARCH(@json, 'one', '%@example.com');
 -- 結果: "$.email"


MySQL 8.0.17以降では、JSON_OVERLAPS() 関数 と MEMBER OF() 演算子が追加された。

JSON_OVERLAPS() は、2つのJSON値に共通要素があるかを検証する。

構文を以下に示す。

JSON_OVERLAPS(json_doc1, json_doc2)


使用例を以下に示す。

 SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]');
 -- 結果: 1 (3が共通)
 
 SELECT JSON_OVERLAPS('[1, 2, 3]', '[4, 5, 6]');
 -- 結果: 0 (共通要素なし)


MEMBER OF() 演算子は、配列メンバーシップをチェックする。

構文を以下に示す。

value MEMBER OF(json_array)


使用例を以下に示す。

 SELECT 3 MEMBER OF('[1, 2, 3, 4]');
 -- 結果: 1
 
 SELECT 5 MEMBER OF('[1, 2, 3, 4]');
 -- 結果: 0



JSONテーブル関数

JSON_TABLE()

JSON_TABLE() 関数は、JSON値をリレーショナルテーブル形式に変換する。
MySQL 8.0.4以降で使用可能である。

構文を以下に示す。

JSON_TABLE(
   expr,
   path
   COLUMNS (column_definition [, column_definition] ...)
)


COLUMNS定義の構文を以下に示す。

  • column_name type PATH path_expr
    指定パスの値をカラムとして抽出
  • column_name type EXISTS PATH path_expr
    パスの存在有無を0/1で返す
  • column_name FOR ORDINALITY
    行番号を返す (1から開始)
  • NESTED PATH path_expr COLUMNS (...)
    ネストした配列をフラット化


基本的な使用例を以下に示す。

 SELECT *
 FROM JSON_TABLE(
    '[
       {"name": "Alice", "age": 30},
       {"name": "Bob", "age": 25}
    ]',
    '$[*]'
    COLUMNS(
       row_num FOR ORDINALITY,
       name VARCHAR(50) PATH '$.name',
       age INT PATH '$.age'
    )
 ) AS jt;
 
 -- 結果:
 -- row_num | name  | age
 -- --------|-------|----
 --    1    | Alice |  30
 --    2    | Bob   |  25


  • EXISTS PATH の使用例 
     SELECT *
 FROM JSON_TABLE(
    '[
       {"name": "Alice", "age": 30, "city": "Tokyo"},
       {"name": "Bob", "age": 25}
    ]',
    '$[*]'
    COLUMNS(
       name VARCHAR(50) PATH '$.name',
       has_city INT EXISTS PATH '$.city'
    )
 ) AS jt;
 
 -- 結果:
 -- name  | has_city
 -- ------|----------
 -- Alice |    1
 -- Bob   |    0

  • NESTED PATH の使用例 
     SELECT *
 FROM JSON_TABLE(
    '{
       "user": "Alice",
       "orders": [
          {"id": 101, "amount": 1500},
          {"id": 102, "amount": 2300}
       ]
    }',
    '$'
    COLUMNS(
       user VARCHAR(50) PATH '$.user',
       NESTED PATH '$.orders[*]' COLUMNS(
          order_id INT PATH '$.id',
          amount INT PATH '$.amount'
       )
    )
 ) AS jt;
 
 -- 結果:
 -- user  | order_id | amount
 -- ------|----------|--------
 -- Alice |   101    |  1500
 -- Alice |   102    |  2300


エラーハンドリングの構文を以下に示す。

  • NULL ON ERROR
    エラー時にNULLを返す。(デフォルト)
  • ERROR ON ERROR
    エラー時に例外を発生させる。
  • DEFAULT value ON ERROR
    エラー時にデフォルト値を返す。


エラーハンドリングの使用例を以下に示す。

 SELECT *
 FROM JSON_TABLE(
    '[
       {"age": 30},
       {"age": "invalid"}
    ]',
    '$[*]'
    COLUMNS(
       age INT PATH '$.age' DEFAULT 0 ON ERROR
    )
 ) AS jt;
 
 -- 結果:
 -- age
 -- ----
 --  30
 --   0



JSONマージ関数

JSON_MERGE_PATCH() / JSON_MERGE_PRESERVE()

MySQLは、2つのJSON値をマージするための関数を提供している。
MySQL 5.7.22以降で使用可能である。

構文を以下に示す。

JSON_MERGE_PATCH(json_doc1, json_doc2[, json_doc3, ...])
JSON_MERGE_PRESERVE(json_doc1, json_doc2[, json_doc3, ...])


2つの関数の動作の違いを以下に示す。

JSON_MERGE_PATCH と JSON_MERGE_PRESERVE の比較
シナリオ JSON_MERGE_PATCH() JSON_MERGE_PRESERVE()
オブジェクトのマージ 後者で置換 再帰的マージ (競合時は配列化)
配列のマージ 後者で置換 配列を連結
null値の扱い キーを削除 null値として保持
準拠規格 RFC 7396 独自実装
用途 パッチ適用、更新 データ結合、ログ集約


  • JSON_MERGE_PATCH() 関数の使用例 
     SELECT JSON_MERGE_PATCH(
    '{"name": "Alice", "age": 30}',
    '{"age": 31, "city": "Tokyo"}'
 );
 -- 結果: {"name": "Alice", "age": 31, "city": "Tokyo"}
 -- age は置換、city は追加
 
 SELECT JSON_MERGE_PATCH(
    '{"name": "Alice", "age": 30}',
    '{"age": null}'
 );
 -- 結果: {"name": "Alice"}
 -- age キーが削除される

  • JSON_MERGE_PRESERVE() 関数の使用例 
     SELECT JSON_MERGE_PRESERVE(
    '{"name": "Alice", "age": 30}',
    '{"age": 31, "city": "Tokyo"}'
 );
 -- 結果: {"name": "Alice", "age": [30, 31], "city": "Tokyo"}
 -- age は配列化される
 
 SELECT JSON_MERGE_PRESERVE('[1, 2]', '[3, 4]');
 -- 結果: [1, 2, 3, 4]
 -- 配列が連結される


JSON_MERGE()関数は非推奨である。
JSON_MERGE_PRESERVE() 関数の使用を推奨する。


JSON集約関数

JSON_ARRAYAGG() / JSON_OBJECTAGG()

JSON_ARRAYAGG() 関数 と JSON_OBJECTAGG() 関数は、GROUP BYと組み合わせてJSON値を集約する。
MySQL 5.7.22以降で使用可能である。

構文を以下に示す。

JSON_ARRAYAGG(expr)
JSON_OBJECTAGG(key, value)


JSON_ARRAYAGG() 関数は、値をJSON配列に集約する。

使用例を以下に示す。

 CREATE TABLE orders (
    customer_id INT,
    product VARCHAR(50),
    amount INT
 );
 
 INSERT INTO orders VALUES
   (1, 'Apple', 100),
   (1, 'Banana', 150),
   (2, 'Orange', 200),
   (2, 'Grape', 180);
 
 SELECT
    customer_id,
    JSON_ARRAYAGG(product) AS products
 FROM orders
 GROUP BY customer_id;
 
 -- 結果:
 -- customer_id | products
 -- ------------|---------------------
 --      1      | ["Apple", "Banana"]
 --      2      | ["Orange", "Grape"]


JSON_OBJECTAGG() 関数は、キーと値のペアをJSONオブジェクトに集約する。

使用例を以下に示す。

 SELECT
    customer_id,
    JSON_OBJECTAGG(product, amount) AS order_details
 FROM orders
 GROUP BY customer_id;
 
 -- 結果:
 -- customer_id | order_details
 -- ------------|-------------------------------
 --      1      | {"Apple": 100, "Banana": 150}
 --      2      | {"Orange": 200, "Grape": 180}


ネストしたJSON構造の生成例を以下に示す。

 SELECT
    customer_id,
    JSON_OBJECT(
       'products', JSON_ARRAYAGG(product),
       'total', SUM(amount)
    ) AS summary
 FROM orders
 GROUP BY customer_id;
 
 -- 結果:
 -- customer_id | summary
 -- ------------|-----------------------------------------------
 --      1      | {"products": ["Apple", "Banana"], "total": 250}
 --      2      | {"products": ["Orange", "Grape"], "total": 380}



JSONとインデックス

JSONカラムに直接インデックスを作成することはできない。
JSON値からインデックスを作成するには、Generated Columns (生成列) または Multi-Valued Index (複数値インデックス) を使用する。

Generated Columnsを使ったインデックス

Generated Columns (生成列) を使用することにより、JSON値の一部を抽出し、そこにインデックスを作成できる。

構文を以下に示す。

ALTER TABLE table_name
ADD COLUMN column_name type
   AS (JSON_EXTRACT(json_column, path)) STORED,
ADD INDEX(column_name);


使用例を以下に示す。

 CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
 );
 
 ALTER TABLE users
 ADD COLUMN city VARCHAR(50)
    AS (data ->> '$.city') STORED,
 ADD INDEX idx_city (city);


Generated Columnsによるインデックスを使用したクエリ例を以下に示す。

 SELECT * FROM users WHERE city = 'Tokyo';
 -- idx_city インデックスが使用される


VIRTUAL と STORED の違いを以下に示す。

  • VIRTUAL
    値はメモリ上でのみ計算される。(デフォルト)
    ストレージ容量を節約
    セカンダリインデックスを作成できない。
  • STORED
    値はテーブルに物理的に保存される。
    セカンダリインデックスを作成できる。
    ストレージ容量が増加する。


インデックス作成時には、STORED を使用する必要がある。

Multi-Valued Index

Multi-Valued Index (複数値インデックス) は、JSON配列の要素に対してインデックスを作成する機能である。
MySQL 8.0.17以降で使用可能である。

構文を以下に示す。

CREATE INDEX index_name
ON table_name ((CAST(json_column -> path AS type ARRAY)));


使用例を以下に示す。

 CREATE TABLE products (
    id INT PRIMARY KEY,
    data JSON
 );
 
 INSERT INTO products VALUES
   (1, '{"tags": ["electronics", "computer", "laptop"]}'),
   (2, '{"tags": ["clothing", "shirt"]}');
 
 CREATE INDEX idx_tags
 ON products ((CAST(data -> '$.tags' AS CHAR(50) ARRAY)));


Multi-Valued Indexを使用したクエリ例を以下に示す。

 SELECT * FROM products
 WHERE 'laptop' MEMBER OF(data -> '$.tags');
 -- idx_tags インデックスが使用される
 
 SELECT * FROM products
 WHERE JSON_OVERLAPS(data -> '$.tags', '["electronics", "clothing"]');
 -- idx_tags インデックスが使用される


Multi-Valued Indexの制約を以下に示す。

  • オンライン作成非対応
    ALGORITHM=COPY のみサポート (テーブルロックが必要)
  • ソート非対応
    ORDER BY では使用できない。
  • プライマリキー、外部キーとして使用不可
  • UNIQUE制約不可


Multi-Valued Indexは、MEMBER OF() 演算子 と JSON_OVERLAPS() 関数のクエリ最適化に使用される。


サンプルクエリ

  • 設定情報の管理例 
     CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    settings JSON
 );
 
 INSERT INTO user_settings VALUES
   (1, '{"theme": "dark", "language": "ja", "notifications": {"email": true, "sms": false}}');
 
 -- テーマを変更
 UPDATE user_settings
 SET settings = JSON_SET(settings, '$.theme', 'light')
 WHERE user_id = 1;
 
 -- 通知設定を追加
 UPDATE user_settings
 SET settings = JSON_SET(settings, '$.notifications.push', true)
 WHERE user_id = 1;

  • ログデータの集約例 
     CREATE TABLE access_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME,
    user_id INT,
    action VARCHAR(50),
    metadata JSON
 );
 
 -- ユーザごとのアクション集約
 SELECT
    user_id,
    JSON_OBJECTAGG(action, COUNT(*)) AS action_counts
 FROM access_logs
 WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
 GROUP BY user_id;

  • E-Commerceの商品属性管理例 
     CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
 );
 
 INSERT INTO products VALUES
   (1, 'Laptop', '{"brand": "Dell", "cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}'),
   (2, 'Smartphone', '{"brand": "Apple", "model": "iPhone 14", "color": "Blue", "storage": "128GB"}');
 
 -- 特定属性で検索
 SELECT name, attributes ->> '$.brand' AS brand
 FROM products
 WHERE attributes ->> '$.brand' = 'Apple';
 
 -- 属性の動的追加
 UPDATE products
 SET attributes = JSON_SET(attributes, '$.warranty', '2 years')
 WHERE id = 1;

  • JSONテーブル関数を使った分析例 
     -- 複雑なJSON構造をフラット化して分析
 SELECT
    jt.user,
    jt.order_id,
    jt.product,
    jt.quantity
 FROM order_data,
 JSON_TABLE(
    order_data.json_col,
    '$.orders[*]'
    COLUMNS(
       user VARCHAR(50) PATH '$.user',
       order_id INT PATH '$.id',
       NESTED PATH '$.items[*]' COLUMNS(
          product VARCHAR(100) PATH '$.product',
          quantity INT PATH '$.quantity'
       )
    )
 ) AS jt;



パフォーマンス

JSON関数のパフォーマンス最適化のポイントを以下に示す。

部分更新 (Partial Update)

JSON_SET()、JSON_REPLACE()、JSON_REMOVE() 関数は、部分更新の最適化をサポートしている。

部分更新により、JSON値全体を書き換えることなく、一部の要素のみを効率的に更新できる。

部分更新が適用される条件を以下に示す。

  • 更新対象のJSON値が大きい
  • 変更する要素が小さい
  • JSON値がバイナリ形式で保存されている


部分更新の効果を確認する例を以下に示す。

 SELECT JSON_STORAGE_SIZE(data) AS before_size,
        JSON_STORAGE_FREE(data) AS free_space
 FROM users WHERE id = 1;
 
 UPDATE users
 SET data = JSON_SET(data, '$.age', 31)
 WHERE id = 1;
 
 SELECT JSON_STORAGE_SIZE(data) AS after_size,
        JSON_STORAGE_FREE(data) AS free_space
 FROM users WHERE id = 1;


JSON_STORAGE_FREE() 関数は、部分更新後の空き領域を返す。
空き領域が多い場合は、テーブルの最適化を検討する。

インデックスの活用

JSON値に対する検索クエリを高速化するには、インデックスが必須である。

  • Generated Columnsによるインデックスを使用する例 
     ALTER TABLE users
 ADD COLUMN email VARCHAR(255)
    AS (data ->> '$.email') STORED,
 ADD INDEX idx_email (email);
 
 -- インデックスが使用される
 SELECT * FROM users WHERE email = 'alice@example.com';

  • Multi-Valued Indexを使用する例 
     CREATE INDEX idx_tags
 ON products ((CAST(data -> '$.tags' AS CHAR(50) ARRAY)));
 
 -- インデックスが使用される
 SELECT * FROM products
 WHERE 'electronics' MEMBER OF(data -> '$.tags');


バイナリログの最適化

MySQL 8.0以降では、binlog_row_value_options=PARTIAL_JSON 設定により、レプリケーション時のバイナリログを最適化できる。

設定例を以下に示す。

 SET GLOBAL binlog_row_value_options = 'PARTIAL_JSON';


この設定により、JSON値の部分更新がバイナリログに記録される際に、変更された部分のみが記録される。
レプリケーションのパフォーマンスが向上する。

JSON値のサイズ確認

JSON_STORAGE_SIZE() 関数により、JSON値のバイナリストレージサイズを確認できる。

構文を以下に示す。

JSON_STORAGE_SIZE(json_doc)


使用例を以下に示す。

 SELECT JSON_STORAGE_SIZE('{"name": "Alice", "age": 30}');
 -- 結果: 39 (バイト)


JSON_PRETTY() 関数により、JSON値を整形して可読性を向上できる。
ただし、ストレージサイズが増加するため、本番環境では使用を控える。

構文を以下に示す。

JSON_PRETTY(json_doc)


使用例を以下に示す。

 SELECT JSON_PRETTY('{"name":"Alice","age":30}');
 -- 結果:
 -- {
 --   "name": "Alice",
 --   "age": 30
 -- }



関連ページ



「https://mochiu.net/index.php?title=MySQL_-_JSON関数&oldid=14283」から取得