MySQL - JSON関数のソースを表示

== 概要 ==
MySQLは、RFC 7159準拠のネイティブJSON型をサポートしている。<br>
JSON型は、MySQL 5.7.8で初めて導入され、MySQL 8.0で大幅に機能が拡張された。<br>
<br>
JSON値は、テキスト形式で入力されるが、内部ではバイナリ形式で保存される。<br>
バイナリ形式により、パース処理を省略し、効率的なパス指定による値の抽出と変更を実現している。<br>
<br>
MySQLのJSON型には、以下に示す特徴がある。<br>
* 自動検証
*: 無効なJSON値はエラーとして拒否される
* 効率的なストレージ
*: バイナリ形式での内部保存により、高速なアクセスを実現
* パス式による柔軟な参照
*: JSONドキュメント内の任意の要素を直接参照可能
* 部分更新のサポート
*: JSON値全体を書き換えることなく、一部の要素のみを更新可能
* インデックス対応
*: <u>Generated Columns</u> や <u>Multi-Valued Index</u> によるインデックス作成が可能
<br><br>

== JSON型 ==
==== JSON型の定義 ====
JSON型のカラムは、通常のデータ型と同様に定義できる。<br>
<br>
定義例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
 );
 </syntaxhighlight>
<br>
JSON型のカラムには、JSON形式の値を直接挿入できる。<br>
<br>
挿入例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT INTO users (id, data) VALUES
   (1, '{"name": "Alice", "age": 30, "city": "Tokyo"}'),
   (2, '{"name": "Bob", "age": 25, "city": "Osaka"}');
 </syntaxhighlight>
<br>
<u>JSON値は、挿入時に自動的に検証され、バイナリ形式に変換されて保存される。</u><br>
<u>無効なJSON形式の値を挿入しようとすると、エラーが返される。</u><br>
<br>
JSON値の最大サイズは、<code>max_allowed_packet</code> システム変数によって制限される。(デフォルト: 4[MB])<br>
<br>
==== JSON値の検証 ====
MySQLは、JSON値の挿入時に自動的に妥当性検証を実行する。<br>
<br>
以下の条件を満たさない値は、エラーとして拒否される。<br>
* JSON null、true、falseは小文字である必要がある。
*: <code>NULL</code>、<code>True</code>、<code>False</code> は不可
* 文字列はダブルクォートで囲まれている必要がある。
*: シングルクォートは不可
* オブジェクトのキーは文字列である必要がある。
*: 数値やnullはキーとして使用不可
<br>
<u>SQL NULL値とJSON null値は異なる値である。</u><br>
<u>SQL NULLは、ソート時にJSON nullよりも前に配置される。</u><br>
<br>
<code>JSON_VALID()</code> 関数により、値の妥当性を明示的に検証できる。<br>
<br>
検証例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_VALID('{"name": "Alice"}');   -- 1 (valid)
 
 SELECT JSON_VALID('{name: "Alice"}');     -- 0 (invalid: キーがクォートされていない)

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

== JSON値の作成 ==
==== JSON_ARRAY() / JSON_OBJECT() ====
<code>JSON_ARRAY()</code> 関数は、引数からJSON配列を作成する。<br>
<code>JSON_OBJECT()</code> 関数は、キーと値のペアからJSONオブジェクトを作成する。<br>
<br>
<code>JSON_ARRAY()</code> 関数の構文を以下に示す。<br>
 JSON_ARRAY([value[, value] ...])
<br>
<code>JSON_OBJECT()</code> 関数の構文を以下に示す。<br>
 JSON_OBJECT([key, value[, key, value] ...])
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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}
 </syntaxhighlight>
<br>
<code>JSON_ARRAY()</code> 関数 と <code>JSON_OBJECT()</code> 関数 の動作を以下に示す。<br>
* SQL NULL値は、JSON nullに自動変換される。
* <code>JSON_OBJECT()</code> 関数のキーが重複する場合、最後の値が採用される。
* 引数が空の場合、空の配列またはオブジェクトが返される。
<br>
<code>JSON_QUOTE()</code> 関数は、文字列をJSON文字列としてクォートする。<br>
<br>
構文を以下に示す。<br>
 JSON_QUOTE(string)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_QUOTE('Hello "World"');
 -- 結果: "Hello \"World\""
 </syntaxhighlight>
<br>
==== CAST() / JSON_TYPE() ====
<code>CAST()</code> 関数により、他のデータ型からJSON型へのキャストが可能である。<br>
<br>
構文を以下に示す。<br>
 CAST(value AS JSON)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT CAST('[1, 2, 3]' AS JSON);
 -- 結果: [1, 2, 3]
 
 SELECT CAST('{"name": "Alice"}' AS JSON);
 -- 結果: {"name": "Alice"}
 </syntaxhighlight>
<br>
<code>JSON_TYPE()</code> 関数は、JSON値の型を返す。<br>
<br>
構文を以下に示す。<br>
 JSON_TYPE(json_value)
<br>
下表に、返される型を示す。<br>
<br>
<center>
{| class="wikitable"
|+ JSON_TYPE関数の返される型
|-
! 型 !! 説明
|-
| <code>OBJECT</code> || JSONオブジェクト
|-
| <code>ARRAY</code> || JSON配列
|-
| <code>STRING</code> || JSON文字列
|-
| <code>INTEGER</code> || JSON整数
|-
| <code>DOUBLE</code> || JSON浮動小数点数
|-
| <code>BOOLEAN</code> || JSON真偽値 (true、false)
|-
| <code>NULL</code> || JSON null
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
<br><br>

== JSON値の参照 ==
==== JSON_EXTRACT() ====
<code>JSON_EXTRACT()</code> 関数は、パス式を使用してJSON値から要素を抽出する。<br>
<br>
構文を以下に示す。<br>
 JSON_EXTRACT(json_doc, path[, path] ...)
<br>
パス式は、抽出する要素の位置を指定する。<br>
複数のパスを指定した場合、抽出された値は配列として返される。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 </syntaxhighlight>
<br>
配列要素の抽出例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
<br>
存在しないパスを指定した場合、<code>NULL</code> が返される。<br>
<br>
==== ->演算子 と ->>演算子 ====
MySQL 5.7.9以降では、パス式による要素参照のショートカット演算子が提供されている。<br>
<br>
->演算子は、<code>JSON_EXTRACT()</code> 関数のショートカットである。<br>
->>演算子は、<code>JSON_UNQUOTE(JSON_EXTRACT())</code> 関数のショートカットである。<br>
<br>
構文を以下に示す。<br>
 column -> path
 column ->> path
<br>
->演算子 と ->>演算子の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ -> 演算子と->> 演算子の比較
! 演算子 !! 等価な関数 !! 返される値 !! 用途
|-
| -> || JSON_EXTRACT() || JSON値 (クォート付き) || JSON値として扱う場合
|-
| ->> || JSON_UNQUOTE(JSON_EXTRACT()) || アンクォートされた文字列 || 文字列として扱う場合
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET @json = '{"name": "Alice", "age": 30}';
 
 SELECT @json -> '$.name';
 -- 結果: "Alice" (ダブルクォート付き)
 
 SELECT @json ->> '$.name';
 -- 結果: Alice (ダブルクォートなし)
 </syntaxhighlight>
<br>
WHERE句での使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM users WHERE data ->> '$.city' = 'Tokyo';
 </syntaxhighlight>
<br>
==== パス式の構文 ====
パス式は、JSON値内の要素を指定するための構文である。<br>
<br>
下表に、パス式の基本構文を示す。<br>
<br>
<center>
{| class="wikitable"
|+ パス式の構文
! 構文 !! 説明 !! 例
|-
| $ || ルート要素 || $ → 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番目の要素
|}
</center>
<br>
* パス式の使用例
*: <syntaxhighlight lang="mysql">
 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]
 </syntaxhighlight>
*: <br>
* 再帰的検索 (**) の例
*: <syntaxhighlight lang="mysql">
 SET @json = '{
    "name": "Alice",
    "address": {
       "city": "Tokyo",
       "office": {
          "name": "Headquarters"
       }
    }
 }';
 
 SELECT JSON_EXTRACT(@json, '$**.name');
 -- 結果: ["Alice", "Headquarters"]
 </syntaxhighlight>
<br>
==== JSON_KEYS() / JSON_LENGTH() ====
<code>JSON_KEYS()</code> 関数は、JSONオブジェクトのキー一覧を配列として返す。<br>
<br>
構文を以下に示す。<br>
 JSON_KEYS(json_doc[, path])
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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"]
 </syntaxhighlight>
<br>
<code>JSON_LENGTH()</code> 関数は、JSON値の要素数を返す。<br>
<br>
構文を以下に示す。<br>
 JSON_LENGTH(json_doc[, path])
<br>
要素数の計算方法を以下に示す。<br>
* 配列
*: 要素の数
* オブジェクト
*: キーの数
* スカラー値
*: 1
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_LENGTH('[1, 2, 3, 4, 5]');
 -- 結果: 5
 
 SELECT JSON_LENGTH('{"name": "Alice", "age": 30}');
 -- 結果: 2
 
 SELECT JSON_LENGTH('"text"');
 -- 結果: 1
 </syntaxhighlight>
<br>
<code>JSON_DEPTH()</code> 関数は、JSON値の最大ネスト深さを返す。<br>
<br>
構文を以下に示す。<br>
 JSON_DEPTH(json_doc)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_DEPTH('{}');
 -- 結果: 1
 
 SELECT JSON_DEPTH('{"a": {"b": {"c": {}}}}');
 -- 結果: 4
 
 SELECT JSON_DEPTH('[1, [2, [3, 4]]]');
 -- 結果: 4
 </syntaxhighlight>
<br><br>

== JSON値の変更 ==
==== JSON_SET() / JSON_INSERT() / JSON_REPLACE() ====
MySQLは、JSON値を部分的に変更するための3つの関数を提供している。<br>
<br>
構文を以下に示す。<br>
 JSON_SET(json_doc, path, value[, path, value] ...)
 JSON_INSERT(json_doc, path, value[, path, value] ...)
 JSON_REPLACE(json_doc, path, value[, path, value] ...)
<br>
各関数の動作の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ JSON_SET / INSERT / REPLACE の比較
! 関数 !! パスが存在する場合 !! パスが存在しない場合 !! 用途
|-
| JSON_SET() || 値を置換 || 値を挿入 || 常に値を設定
|-
| JSON_INSERT() || 何もしない || 値を挿入 || 新しい値のみを挿入
|-
| JSON_REPLACE() || 値を置換 || 何もしない || 既存の値のみを更新
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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 は存在しないため追加されない
 </syntaxhighlight>
<br>
* UPDATE文での使用例
*: <syntaxhighlight lang="mysql">
 UPDATE users
 SET data = JSON_SET(data, '$.age', 31)
 WHERE id = 1;
 </syntaxhighlight>
*: <br>
* 複数のパスを同時に更新する例
*: <syntaxhighlight lang="mysql">
 UPDATE users
 SET data = JSON_SET(
    data,
    '$.age', 31,
    '$.city', 'Tokyo',
    '$.updated_at', NOW()
 )
 WHERE id = 1;
 </syntaxhighlight>
<br>
==== JSON_REMOVE() ====
<code>JSON_REMOVE()</code> 関数は、指定されたパスの要素を削除する。<br>
<br>
構文を以下に示す。<br>
 JSON_REMOVE(json_doc, path[, path] ...)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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"}
 </syntaxhighlight>
<br>
* 配列要素の削除例
*: <syntaxhighlight lang="mysql">
 SET @array = '[10, 20, 30, 40, 50]';
 
 SELECT JSON_REMOVE(@array, '$[2]');
 -- 結果: [10, 20, 40, 50]
 </syntaxhighlight>
*: <br>
* UPDATE文での使用例
*: <syntaxhighlight lang="mysql">
 UPDATE users
 SET data = JSON_REMOVE(data, '$.age')
 WHERE id = 1;
 </syntaxhighlight>
<br>
==== JSON_ARRAY_APPEND() / JSON_ARRAY_INSERT() ====
<code>JSON_ARRAY_APPEND()</code> 関数は、配列の末尾に要素を追加する。<br>
<code>JSON_ARRAY_INSERT()</code> 関数は、配列の指定位置に要素を挿入する。<br>
<br>
構文を以下に示す。<br>
 JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)
 JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)
<br>
* <code>JSON_ARRAY_APPEND()</code> の使用例
*: <syntaxhighlight lang="mysql">
 SET @array = '[10, 20, 30]';
 
 SELECT JSON_ARRAY_APPEND(@array, '$', 40);
 -- 結果: [10, 20, 30, 40]
 </syntaxhighlight>
*: <br>
* <code>JSON_ARRAY_INSERT()</code> の使用例
*: <syntaxhighlight lang="mysql">
 SET @array = '[10, 20, 30]';
 
 SELECT JSON_ARRAY_INSERT(@array, '$[1]', 15);
 -- 結果: [10, 15, 20, 30]
 </syntaxhighlight>
*: <br>
* ネストした配列への操作例
*: <syntaxhighlight lang="mysql">
 SET @json = '{"scores": [85, 90, 78]}';
 
 SELECT JSON_ARRAY_APPEND(@json, '$.scores', 95);
 -- 結果: {"scores": [85, 90, 78, 95]}
 </syntaxhighlight>
<br><br>

== JSON検索関数 ==
==== JSON_CONTAINS() ====
<code>JSON_CONTAINS()</code> 関数は、JSON値が指定された候補値を含むかどうかを検証する。<br>
<br>
構文を以下に示す。<br>
 JSON_CONTAINS(target, candidate[, path])
<br>
返り値を以下に示す。<br>
* 1
*: 候補値が含まれる
* 0
*: 候補値が含まれない
* NULL
*: 引数のいずれかがNULL
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
<br>
* 配列での使用例
*: <syntaxhighlight lang="mysql">
 SET @array = '[1, 2, 3, 4, 5]';
 
 SELECT JSON_CONTAINS(@array, '3');
 -- 結果: 1
 
 SELECT JSON_CONTAINS(@array, '6');
 -- 結果: 0
 </syntaxhighlight>
*: <br>
* WHERE句での使用例
*: <syntaxhighlight lang="mysql">
 SELECT * FROM users
 WHERE JSON_CONTAINS(data, '"Tokyo"', '$.city');
 </syntaxhighlight>
<br>
==== JSON_CONTAINS_PATH() ====
<code>JSON_CONTAINS_PATH()</code> 関数は、指定されたパスがJSON値内に存在するかどうかを検証する。<br>
<br>
構文を以下に示す。<br>
 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
<br>
one_or_allパラメータの動作を以下に示す。<br>
* 'one'
*: 少なくとも1つのパスが存在すれば1を返す
* 'all'
*: 全てのパスが存在する場合のみ1を返す
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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 (両方とも存在する)
 </syntaxhighlight>
<br>
==== JSON_SEARCH() ====
<code>JSON_SEARCH()</code> 関数は、JSON値内から文字列を検索し、そのパスを返す。<br>
<br>
構文を以下に示す。<br>
 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path]])
<br>
one_or_allパラメータの動作を以下に示す。<br>
* 'one'
*: 最初に一致したパスを返す
* 'all'
*: 一致した全てのパスを配列で返す
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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"]
 </syntaxhighlight>
<br>
ワイルドカードを使用した検索例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET @json = '{"email": "alice@example.com"}';
 
 SELECT JSON_SEARCH(@json, 'one', '%@example.com');
 -- 結果: "$.email"
 </syntaxhighlight>
<br>
MySQL 8.0.17以降では、<code>JSON_OVERLAPS()</code> 関数 と <code>MEMBER OF()</code> 演算子が追加された。<br>
<br>
<code>JSON_OVERLAPS()</code> は、2つのJSON値に共通要素があるかを検証する。<br>
<br>
構文を以下に示す。<br>
 JSON_OVERLAPS(json_doc1, json_doc2)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]');
 -- 結果: 1 (3が共通)
 
 SELECT JSON_OVERLAPS('[1, 2, 3]', '[4, 5, 6]');
 -- 結果: 0 (共通要素なし)
 </syntaxhighlight>
<br>
<code>MEMBER OF()</code> 演算子は、配列メンバーシップをチェックする。<br>
<br>
構文を以下に示す。<br>
 value MEMBER OF(json_array)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT 3 MEMBER OF('[1, 2, 3, 4]');
 -- 結果: 1
 
 SELECT 5 MEMBER OF('[1, 2, 3, 4]');
 -- 結果: 0
 </syntaxhighlight>
<br><br>

== JSONテーブル関数 ==
==== JSON_TABLE() ====
<code>JSON_TABLE()</code> 関数は、JSON値をリレーショナルテーブル形式に変換する。<br>
MySQL 8.0.4以降で使用可能である。<br>
<br>
構文を以下に示す。<br>
 JSON_TABLE(
    expr,
    path
    COLUMNS (column_definition [, column_definition] ...)
 )
<br>
COLUMNS定義の構文を以下に示す。<br>
* 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 (...)
*: ネストした配列をフラット化
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
<br>
* <code>EXISTS PATH</code> の使用例
*: <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
*: <br>
* NESTED PATH の使用例
*: <syntaxhighlight lang="mysql">
 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
 </syntaxhighlight>
<br>
エラーハンドリングの構文を以下に示す。<br>
* NULL ON ERROR
*: エラー時にNULLを返す。(デフォルト)
* ERROR ON ERROR
*: エラー時に例外を発生させる。
* DEFAULT value ON ERROR
*: エラー時にデフォルト値を返す。
<br>
エラーハンドリングの使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT *
 FROM JSON_TABLE(
    '[
       {"age": 30},
       {"age": "invalid"}
    ]',
    '$[*]'
    COLUMNS(
       age INT PATH '$.age' DEFAULT 0 ON ERROR
    )
 ) AS jt;
 
 -- 結果:
 -- age
 -- ----
 --  30
 --   0
 </syntaxhighlight>
<br><br>

== JSONマージ関数 ==
==== JSON_MERGE_PATCH() / JSON_MERGE_PRESERVE() ====
MySQLは、2つのJSON値をマージするための関数を提供している。<br>
MySQL 5.7.22以降で使用可能である。<br>
<br>
構文を以下に示す。<br>
 JSON_MERGE_PATCH(json_doc1, json_doc2[, json_doc3, ...])
 JSON_MERGE_PRESERVE(json_doc1, json_doc2[, json_doc3, ...])
<br>
2つの関数の動作の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ JSON_MERGE_PATCH と JSON_MERGE_PRESERVE の比較
! シナリオ !! JSON_MERGE_PATCH() !! JSON_MERGE_PRESERVE()
|-
| オブジェクトのマージ || 後者で置換 || 再帰的マージ (競合時は配列化)
|-
| 配列のマージ || 後者で置換 || 配列を連結
|-
| null値の扱い || キーを削除 || null値として保持
|-
| 準拠規格 || RFC 7396 || 独自実装
|-
| 用途 || パッチ適用、更新 || データ結合、ログ集約
|}
</center>
<br>
* <code>JSON_MERGE_PATCH()</code> 関数の使用例
*: <syntaxhighlight lang="mysql">
 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 キーが削除される
 </syntaxhighlight>
*: <br>
* <code>JSON_MERGE_PRESERVE()</code> 関数の使用例
*: <syntaxhighlight lang="mysql">
 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]
 -- 配列が連結される
 </syntaxhighlight>
<br>
<u>JSON_MERGE()関数は非推奨である。</u><br>
<u><code>JSON_MERGE_PRESERVE()</code> 関数の使用を推奨する。</u><br>
<br><br>

== JSON集約関数 ==
==== JSON_ARRAYAGG() / JSON_OBJECTAGG() ====
<code>JSON_ARRAYAGG()</code> 関数 と <code>JSON_OBJECTAGG()</code> 関数は、GROUP BYと組み合わせてJSON値を集約する。<br>
MySQL 5.7.22以降で使用可能である。<br>
<br>
構文を以下に示す。<br>
 JSON_ARRAYAGG(expr)
 JSON_OBJECTAGG(key, value)
<br>
<code>JSON_ARRAYAGG()</code> 関数は、値をJSON配列に集約する。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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"]
 </syntaxhighlight>
<br>
<code>JSON_OBJECTAGG()</code> 関数は、キーと値のペアをJSONオブジェクトに集約する。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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}
 </syntaxhighlight>
<br>
ネストしたJSON構造の生成例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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}
 </syntaxhighlight>
<br><br>

== JSONとインデックス ==
JSONカラムに直接インデックスを作成することはできない。<br>
JSON値からインデックスを作成するには、<u>Generated Columns (生成列)</u> または <u>Multi-Valued Index (複数値インデックス)</u> を使用する。<br>
<br>
==== Generated Columnsを使ったインデックス ====
Generated Columns (生成列) を使用することにより、JSON値の一部を抽出し、そこにインデックスを作成できる。<br>
<br>
構文を以下に示す。<br>
 ALTER TABLE table_name
 ADD COLUMN column_name type
    AS (JSON_EXTRACT(json_column, path)) STORED,
 ADD INDEX(column_name);
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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);
 </syntaxhighlight>
<br>
Generated Columnsによるインデックスを使用したクエリ例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM users WHERE city = 'Tokyo';
 -- idx_city インデックスが使用される
 </syntaxhighlight>
<br>
VIRTUAL と STORED の違いを以下に示す。<br>
* VIRTUAL
*: 値はメモリ上でのみ計算される。(デフォルト)
*: ストレージ容量を節約
*: セカンダリインデックスを作成できない。
* STORED
*: 値はテーブルに物理的に保存される。
*: セカンダリインデックスを作成できる。
*: ストレージ容量が増加する。
<br>
インデックス作成時には、STORED を使用する必要がある。<br>
<br>
==== Multi-Valued Index ====
Multi-Valued Index (複数値インデックス) は、JSON配列の要素に対してインデックスを作成する機能である。<br>
MySQL 8.0.17以降で使用可能である。<br>
<br>
構文を以下に示す。<br>
 CREATE INDEX index_name
 ON table_name ((CAST(json_column -> path AS type ARRAY)));
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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)));
 </syntaxhighlight>
<br>
Multi-Valued Indexを使用したクエリ例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM products
 WHERE 'laptop' MEMBER OF(data -> '$.tags');
 -- idx_tags インデックスが使用される
 
 SELECT * FROM products
 WHERE JSON_OVERLAPS(data -> '$.tags', '["electronics", "clothing"]');
 -- idx_tags インデックスが使用される
 </syntaxhighlight>
<br>
Multi-Valued Indexの制約を以下に示す。<br>
* オンライン作成非対応
*: ALGORITHM=COPY のみサポート (テーブルロックが必要)
* ソート非対応
*: ORDER BY では使用できない。
* プライマリキー、外部キーとして使用不可
* UNIQUE制約不可
<br>
Multi-Valued Indexは、<code>MEMBER OF()</code> 演算子 と <code>JSON_OVERLAPS()</code> 関数のクエリ最適化に使用される。<br>
<br><br>

== サンプルクエリ ==
* 設定情報の管理例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* ログデータの集約例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* E-Commerceの商品属性管理例
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* JSONテーブル関数を使った分析例
*: <syntaxhighlight lang="mysql">
 -- 複雑な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;
 </syntaxhighlight>
<br><br>

== パフォーマンス ==
JSON関数のパフォーマンス最適化のポイントを以下に示す。<br>
<br>
==== 部分更新 (Partial Update) ====
<code>JSON_SET()</code>、<code>JSON_REPLACE()</code>、<code>JSON_REMOVE()</code> 関数は、部分更新の最適化をサポートしている。<br>
<br>
部分更新により、JSON値全体を書き換えることなく、一部の要素のみを効率的に更新できる。<br>
<br>
部分更新が適用される条件を以下に示す。<br>
* 更新対象のJSON値が大きい
* 変更する要素が小さい
* JSON値がバイナリ形式で保存されている
<br>
部分更新の効果を確認する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
<code>JSON_STORAGE_FREE()</code> 関数は、部分更新後の空き領域を返す。<br>
空き領域が多い場合は、テーブルの最適化を検討する。<br>
<br>
==== インデックスの活用 ====
JSON値に対する検索クエリを高速化するには、インデックスが必須である。<br>
<br>
* Generated Columnsによるインデックスを使用する例
*: <syntaxhighlight lang="mysql">
 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';
 </syntaxhighlight>
*: <br>
* Multi-Valued Indexを使用する例
*: <syntaxhighlight lang="mysql">
 CREATE INDEX idx_tags
 ON products ((CAST(data -> '$.tags' AS CHAR(50) ARRAY)));
 
 -- インデックスが使用される
 SELECT * FROM products
 WHERE 'electronics' MEMBER OF(data -> '$.tags');
 </syntaxhighlight>
<br>
==== バイナリログの最適化 ====
MySQL 8.0以降では、<code>binlog_row_value_options=PARTIAL_JSON</code> 設定により、レプリケーション時のバイナリログを最適化できる。<br>
<br>
設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET GLOBAL binlog_row_value_options = 'PARTIAL_JSON';
 </syntaxhighlight>
<br>
この設定により、JSON値の部分更新がバイナリログに記録される際に、変更された部分のみが記録される。<br>
レプリケーションのパフォーマンスが向上する。<br>
<br>
==== JSON値のサイズ確認 ====
<code>JSON_STORAGE_SIZE()</code> 関数により、JSON値のバイナリストレージサイズを確認できる。<br>
<br>
構文を以下に示す。<br>
 JSON_STORAGE_SIZE(json_doc)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_STORAGE_SIZE('{"name": "Alice", "age": 30}');
 -- 結果: 39 (バイト)
 </syntaxhighlight>
<br>
<code>JSON_PRETTY()</code> 関数により、JSON値を整形して可読性を向上できる。<br>
ただし、ストレージサイズが増加するため、本番環境では使用を控える。<br>
<br>
構文を以下に示す。<br>
 JSON_PRETTY(json_doc)
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT JSON_PRETTY('{"name":"Alice","age":30}');
 -- 結果:
 -- {
 --   "name": "Alice",
 --   "age": 30
 -- }
 </syntaxhighlight>
<br><br>

== 関連ページ ==
* [[MySQL - Generated Columns]]
*: JSON値からのインデックス作成方法
* [[MySQL - インデックス]]
*: Multi-Valued Indexの詳細
* [https://dev.mysql.com/doc/refman/8.0/en/json.html MySQL 8.0 JSON Functions Reference]
*: MySQL公式ドキュメント (JSON関数リファレンス)
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,MySQL,JSON,JSON関数,JSON_EXTRACT,JSON_TABLE,JSON_SET,Multi-Valued Index,Generated Columns,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLのJSON型とJSON関数に関する詳細ガイド | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]