このページは機械翻訳したものです。

11.5 JSON データ型

• JSON 値の作成

• JSON 値の正規化、マージおよび自動ラップ

• JSON 値の検索および変更

• JSON パス構文

• JSON 値の比較および順序付け

• JSON 値と非 JSON 値の間の変換

• JSON 値の集計

MySQL は、JSON (JavaScript Object Notation) ドキュメント内のデータへの効率的なアクセスを可能にする、RFC 7159 によって定義されたネイティブ JSON データ型をサポートしています。 JSON データ型には、JSON 形式の文字列を文字列カラムに格納するよりも、次の利点があります:

• JSON カラムに格納されている JSON ドキュメントの自動検証。 無効なドキュメントではエラーが発生します。

• 最適化された記憶域形式。 JSON カラムに格納された JSON ドキュメントは、ドキュメント要素へのクイック読取りアクセスを許可する内部形式に変換されます。 サーバーが後でこのバイナリ形式で格納された JSON 値を読み取る必要がある場合、テキスト表現から値を解析する必要はありません。 バイナリ形式は、サーバーがドキュメント内のサブオブジェクトまたはネストされた値の前後のすべての値を読み取ることなく、キーまたは配列インデックスによって直接サブオブジェクトまたはネストされた値を検索できるように構造化されています。

MySQL 8.0 では、JSON_MERGE_PATCH() 関数を使用して RFC 7396 で定義されたJSON マージパッチ形式もサポートされます。 例および詳細は、この関数の説明および JSON 値の正規化、マージおよび自動ラップ を参照してください。

注記

この説明では、monotype で JSON を使用して特に JSON データ型を示し、JSON データ全般を示す通常のフォントで 「JSON」 を使用します。

JSON ドキュメントの格納に必要な領域は、LONGBLOB または LONGTEXT の場合とほぼ同じです。詳細は、セクション11.7「データ型のストレージ要件」 を参照してください。 JSON カラムに格納される JSON ドキュメントのサイズは、max_allowed_packet システム変数の値に制限されることに注意してください。 (サーバーが JSON 値をメモリー内で内部的に操作している場合、これより大きくなることがあります。制限は、サーバーが JSON 値を格納するときに適用されます。) JSON ドキュメントの格納に必要な領域の量は、JSON_STORAGE_SIZE() 関数を使用して取得できます。JSON カラムの場合、記憶域サイズ、つまり、部分更新が実行される前にこの関数によって使用された値 (このセクションで後述する JSON 部分更新最適化の説明を参照)。

MySQL 8.0.13 より前は、JSON カラムに NULL 以外のデフォルト値を含めることはできません。

JSON データ型とともに、作成、操作、検索などの JSON 値に対する操作を可能にする一連の SQL 関数を使用できます。 次に、これらの操作の例を示します。 個々の関数の詳細は、セクション12.18「JSON 関数」 を参照してください。

GeoJSON 値を操作するための一連の空間関数も使用できます。 セクション12.17.11「空間 GeoJSON 関数」を参照してください。

他のバイナリ型のカラムと同様に、JSON カラムは直接インデックス付けされません。かわりに、JSON カラムからスカラー値を抽出するインデックスを生成されたカラムに作成できます。 詳細な例は、JSON カラムインデックスを提供するための生成されたカラムのインデックス付け を参照してください。

MySQL オプティマイザは、JSON 式に一致する仮想カラムの互換性のあるインデックスも検索します。

MySQL 8.0.17 以降では、InnoDB ストレージエンジンは JSON 配列で複数値インデックスをサポートします。 複数値インデックスを参照してください。

MySQL NDB Cluster 8.0 は、JSON カラムおよび MySQL JSON 関数をサポートしています。これには、JSON カラムにインデックスを作成できないための回避方法として、JSON カラムから生成されたカラムに対するインデックスの作成が含まれます。 NDB テーブルごとに最大 3 つの JSON カラムがサポートされます。

JSON 値の部分更新

MySQL 8.0 では、オプティマイザは、古いドキュメントを削除して新しいドキュメント全体をカラムに書き込むかわりに、JSON カラムの部分的なインプレース更新を実行できます。 この最適化は、次の条件を満たす更新に対して実行できます:

• 更新するカラムが JSON として宣言されました。

• UPDATE ステートメントでは、JSON_SET()、JSON_REPLACE() または JSON_REMOVE() のいずれかの関数を使用してカラムを更新します。 カラム値の直接割当て (UPDATE mytable SET jcol = '{"a": 10, "b": 25}'など) は、部分更新として実行できません。

  単一の UPDATE ステートメントでの複数の JSON カラムの更新は、この方法で最適化できます。MySQL では、前述の 3 つの関数を使用して値が更新されるカラムのみの部分更新を実行できます。

• 入力カラムとターゲットカラムは同じカラムである必要があります。UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100) などのステートメントは部分更新として実行できません。

  更新では、入力カラムとターゲットカラムが同じであるかぎり、前の項目にリストされている関数へのネストされたコールを任意の組合せで使用できます。

• すべての変更により、既存の配列またはオブジェクト値が新しい配列またはオブジェクト値に置き換えられ、新しい要素は親オブジェクトまたは配列に追加されません。

• 置換する値は、少なくとも置換値と同じ大きさである必要があります。 つまり、新しい値を古い値より大きくすることはできません。

  この要件で発生する可能性がある例外は、以前の部分更新で大きい値のための十分な領域が残っている場合に発生します。 JSON_STORAGE_FREE() 関数を使用すると、JSON カラムの部分更新によって解放された領域の量を確認できます。

このような部分更新は、領域を節約するコンパクトな形式を使用してバイナリログに書き込むことができます。これは、binlog_row_value_options システム変数を PARTIAL_JSON に設定することで有効にできます。 詳細は、この変数の説明を参照してください。

次のいくつかのセクションでは、JSON 値の作成および操作に関する基本情報を示します。

JSON 値の作成

JSON 配列には、カンマで区切られ、[および]文字で囲まれた値のリストが含まれます:

["abc", 10, null, true, false]

JSON オブジェクトには、カンマで区切られ、{および} 文字で囲まれたキーと値のペアのセットが含まれます:

{"k1": "value", "k2": 10}

例に示すように、JSON 配列およびオブジェクトには、文字列または数値、JSON null リテラルまたは JSON ブール true または false リテラルであるスカラー値を含めることができます。 JSON オブジェクトのキーは文字列である必要があります。 時間的 (日付、時間または日時) スカラー値も使用できます:

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

ネストは、JSON 配列要素および JSON オブジェクトキー値内で許可されます:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

この目的 (セクション12.18.2「JSON 値を作成する関数」 を参照) のために MySQL によって提供される多数の関数から JSON 値を取得したり、CAST(value AS JSON) (JSON 値と非 JSON 値の間の変換 を参照) を使用して他のタイプの値を JSON タイプにキャストすることもできます。 次のいくつかの段落では、MySQL が入力として提供される JSON 値を処理する方法について説明します。

MySQL では、JSON 値は文字列として書き込まれます。 MySQL は、JSON 値を必要とするコンテキストで使用される文字列を解析し、JSON として有効でない場合はエラーを生成します。 次の例に示すように、これらのコンテキストには、JSON データ型を持つカラムへの値の挿入、および JSON 値を想定する関数への引数の受渡し (通常は MySQL JSON 関数のドキュメントで json_doc または json_val として示されています) が含まれます:

• 値を JSON カラムに挿入しようとすると、その値が有効な JSON 値である場合は成功しますが、そうでない場合は失敗します:

  mysql> CREATE TABLE t1 (jdoc JSON);
  Query OK, 0 rows affected (0.20 sec)
  
  mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
  Query OK, 1 row affected (0.01 sec)
  
  mysql> INSERT INTO t1 VALUES('[1, 2,');
  ERROR 3140 (22032) at line 2: Invalid JSON text:
  "Invalid value." at position 6 in value (or column) '[1, 2,'.

  このようなエラーメッセージ内の「「位置 N」」の位置は 0 ベースですが、値の問題が実際に発生する場所を大まかに示すものとみなす必要があります。

• JSON_TYPE() 関数は、JSON 引数を想定し、JSON 値に解析しようとします。 値 JSON 型が有効な場合はそれを返し、それ以外の場合はエラーを生成します:

  mysql> SELECT JSON_TYPE('["a", "b", 1]');
  +----------------------------+
  | JSON_TYPE('["a", "b", 1]') |
  +----------------------------+
  | ARRAY                      |
  +----------------------------+
  
  mysql> SELECT JSON_TYPE('"hello"');
  +----------------------+
  | JSON_TYPE('"hello"') |
  +----------------------+
  | STRING               |
  +----------------------+
  
  mysql> SELECT JSON_TYPE('hello');
  ERROR 3146 (22032): Invalid data type for JSON data in argument 1
  to function json_type; a JSON string or JSON type is required.

MySQL は、utf8mb4 文字セットおよび utf8mb4_bin 照合順序を使用して、JSON コンテキストで使用される文字列を処理します。 他の文字セットの文字列は、必要に応じて utf8mb4 に変換されます。 (ascii および utf8 は utf8mb4 のサブセットであるため、ascii または utf8 文字セットの文字列の場合、変換は必要ありません。)

リテラル文字列を使用して JSON 値を記述するかわりに、コンポーネント要素から JSON 値を構成するための関数が存在します。 JSON_ARRAY() は、(空の可能性がある) 値リストを取得し、これらの値を含む JSON 配列を返します:

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() は、キーと値のペアの (空の可能性がある) リストを取得し、それらのペアを含む JSON オブジェクトを返します:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE_PRESERVE() は、複数の JSON ドキュメントを取得し、結合された結果を返します:

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

マージルールの詳細は、JSON 値の正規化、マージおよび自動ラップ を参照してください。

(MySQL 8.0.3 以降では、動作が多少異なる JSON_MERGE_PATCH() もサポートされます。 これらの関数の違いの詳細は、JSON_MERGE_PATCH() と JSON_MERGE_PRESERVE() の比較 を参照してください。)

JSON 値はユーザー定義変数に割り当てることができます:

mysql> SET @j = JSON_OBJECT('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

ただし、ユーザー定義変数は JSON データ型にできないため、前述の例の@j は JSON 値のように見え、JSON 値と同じ文字セットおよび照合順序を持ちますが、JSON データ型は持ちません。 かわりに、JSON_OBJECT() からの結果は、変数に割り当てられるときに文字列に変換されます。

JSON 値の変換によって生成される文字列には、utf8mb4 の文字セットと utf8mb4_bin の照合順序があります:

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+

utf8mb4_bin はバイナリ照合であるため、JSON 値の比較では大/小文字が区別されます。

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

大/小文字の区別は、JSON null、true および false リテラルにも適用され、常に小文字で記述する必要があります:

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
|                  1 |                  0 |                  0 |
+--------------------+--------------------+--------------------+

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.

JSON リテラルの大/小文字の区別は、任意の文字で記述できる SQL NULL、TRUE および FALSE リテラルの区別とは異なります:

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

JSON ドキュメントに引用符文字 ("または') を挿入する必要がある場合や望ましい場合があります。 この例では、次に示す SQL ステートメントを使用して作成されたテーブルに、それぞれ適切なキーワードとペアになっている MySQL に関するファクトを示すステートメントを表す文字列を含む JSON オブジェクトを挿入するとします:

mysql> CREATE TABLE facts (sentence JSON);

キーワードと文のペアは次のとおりです:

mascot: The MySQL mascot is a dolphin named "Sakila".

これを JSON オブジェクトとして facts テーブルに挿入する方法の 1 つは、MySQL JSON_OBJECT() 関数を使用することです。 この場合、次に示すように、バックスラッシュを使用して各引用符文字をエスケープする必要があります:

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

JSON オブジェクトリテラルとして値を挿入する場合、これは同じように機能しません。この場合、次のように二重のバックスラッシュエスケープシーケンスを使用する必要があります:

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

二重バックスラッシュを使用すると、MySQL はエスケープシーケンス処理を実行せず、代わりに文字列リテラルを処理のためにストレージエンジンに渡します。 前述のいずれかの方法で JSON オブジェクトを挿入した後、次のように単純な SELECT を実行することで、JSON カラム値にバックスラッシュが存在することを確認できます:

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

mascot をキーとして使用するこの特定の文を検索するには、次に示すように、カラムパス演算子 -> を使用できます:

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

これにより、バックスラッシュは引用符とともにそのまま残ります。 mascot をキーとして使用し、引用符やエスケープを含めずに目的の値を表示するには、次のようにインラインパス演算子 ->> を使用します:

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

注記

前述の例は、NO_BACKSLASH_ESCAPES サーバーの SQL モードが有効になっている場合は動作しません。 このモードが設定されている場合、ダブルバックスラッシュのかわりに単一のバックスラッシュを使用して JSON オブジェクトリテラルを挿入でき、バックスラッシュは保持されます。 挿入の実行時に JSON_OBJECT() 関数を使用し、このモードが設定されている場合は、次のように一重引用符および二重引用符を使用する必要があります:

mysql> INSERT INTO facts VALUES
     > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

JSON 値のエスケープ文字に対するこのモードの影響の詳細は、JSON_UNQUOTE() 関数の説明を参照してください。

JSON 値の正規化、マージおよび自動ラップ

文字列が解析され、有効な JSON ドキュメントであることが判明すると、文字列も正規化されます。 つまり、ドキュメントの後半で見つかったキーを複製するキーを持つメンバーは、左から右に読み取られて破棄されます。 次の JSON_OBJECT() コールによって生成されるオブジェクト値には、次に示すように、そのキー名が値の前に出現するため、2 番目の key1 要素のみが含まれます:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

正規化は、次に示すように、値が JSON カラムに挿入されるときにも実行されます:

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES
     >     ('{"x": 17, "x": "red"}'),
     >     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": "red"}     |
| {"x": [3, 5, 7]} |
+------------------+

この「「最後の重複キー優先」」の動作は、RFC 7159 によって推奨され、ほとんどの JavaScript パーサーによって実装されます。 (Bug #86866、Bug #26369555)

8.0.3 より前のバージョンの MySQL では、ドキュメント内で以前に見つかったキーを複製したキーを持つメンバーは破棄されました。 次の JSON_OBJECT() コールによって生成されたオブジェクト値には、2 番目の key1 要素は含まれません。これは、そのキー名が値の前にあるためです:

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"}                           |
+------------------------------------------------------+

MySQL 8.0.3 より前は、この「「最初の重複キー優先」」正規化は JSON カラムに値を挿入するときにも実行されていました。

mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES
     >     ('{"x": 17, "x": "red"}'),
     >     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+-----------+
| c1        |
+-----------+
| {"x": 17} |
| {"x": 17} |
+-----------+

MySQL では、元の JSON ドキュメント内のキー、値または要素間の余分な空白も破棄され、各カンマ (,) またはコロン (:) の後に単一の空白が表示されたままになります (または必要に応じて挿入されます)。 これは、読みやすさを高めるために行われます。

JSON 値を生成する MySQL 関数 (セクション12.18.2「JSON 値を作成する関数」 を参照) は、常に正規化された値を返します。

ルックアップをより効率的にするために、MySQL では JSON オブジェクトのキーもソートされます。 この順序付けの結果は変更される可能性があり、リリース間での一貫性が保証されないことに注意してください。

JSON 値のマージ

関数 JSON_MERGE_PRESERVE() および JSON_MERGE_PATCH() によって実装される MySQL 8.0.3 (以降) では、2 つのマージアルゴリズムがサポートされています。 重複キーの処理方法が異なります: JSON_MERGE_PRESERVE() では重複キーの値が保持されますが、JSON_MERGE_PATCH() では最後の値以外のすべての値が破棄されます。 次のいくつかの段落では、これら 2 つの関数のそれぞれが JSON ドキュメント (つまり、オブジェクトと配列) の様々な組合せのマージを処理する方法について説明します。

注記

JSON_MERGE_PRESERVE() は、以前のバージョンの MySQL (MySQL 8.0.3 で名前が変更された) で検出された JSON_MERGE() 関数と同じです。 JSON_MERGE() は、MySQL 8.0 で JSON_MERGE_PRESERVE() のエイリアスとして引き続きサポートされていますが、非推奨であり、将来のリリースで削除される可能性があります。

配列のマージ.  複数の配列を組み合せるコンテキストでは、配列は単一の配列にマージされます。 JSON_MERGE_PRESERVE() では、後で名前を付けた配列を最初の配列の最後に連結することで、これを行います。 JSON_MERGE_PATCH() は、各引数を単一の要素で構成される配列とみなし (したがって、インデックスとして 0 を持つ)、「「最後の重複キー優先」」ロジックを適用して最後の引数のみを選択します。 次のクエリーで表示される結果を比較できます:

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

マージ時に複数のオブジェクトを使用すると、単一のオブジェクトが生成されます。 JSON_MERGE_PRESERVE() は、配列内のそのキーの一意の値をすべて組み合せることで、同じキーを持つ複数のオブジェクトを処理します。この配列は、結果でそのキーの値として使用されます。 JSON_MERGE_PATCH() では、左から右に向かって重複キーが見つかった値が破棄されるため、結果にはそのキーの最後の値のみが含まれます。 次のクエリーは、重複キー a の結果の違いを示しています:

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
    ->   JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

配列値を必要とするコンテキストで使用される非配列値は、自動ラップされます: この値は、配列に変換するために[および]文字で囲まれています。 次のステートメントでは、各引数が配列 ([1]、[2]) として自動ラップされます。 これらはマージされて単一の結果配列が生成されます。前述の 2 つの場合と同様に、JSON_MERGE_PRESERVE() は同じキーを持つ値を結合し、JSON_MERGE_PATCH() は最後のキーを除くすべての重複キーの値を破棄します:

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('1', '2') AS Preserve,
	  ->   JSON_MERGE_PATCH('1', '2') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2]
   Patch: 2

配列およびオブジェクト値をマージするには、次の例に示すように、オブジェクトを配列として自動ラップし、マージ機能の選択に従って値を組み合せるか、「「最後の重複キー優先」」によって配列をマージします (それぞれ JSON_MERGE_PRESERVE() または JSON_MERGE_PATCH()):

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
	  ->   JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}

JSON 値の検索および変更

JSON パス式は、JSON ドキュメント内の値を選択します。

パス式は、JSON ドキュメントの一部を抽出または変更して、そのドキュメント内のどこで操作するかを指定する関数で役立ちます。 たとえば、次のクエリーは、name キーを持つメンバーの値を JSON ドキュメントから抽出します:

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

パス構文では、検討中の JSON ドキュメントを表すために先頭の $ 文字が使用され、オプションで、ドキュメントの連続して具体的な部分を示すセレクタが続きます:

• ピリオドの後にキー名を指定すると、オブジェクト内のメンバーに特定のキーが付けられます。 引用符のない名前がパス式内で有効でない場合 (たとえば、空白が含まれている場合)、キー名は二重引用符で囲む必要があります。

• 配列を選択する path に追加された[N]は、配列内の N の位置にある値に名前を付けます。 配列の位置はゼロで始まる整数です。 path が配列値を選択しない場合、path [0]は path と同じ値に評価されます:

  mysql> SELECT JSON_SET('"x"', '$[0]', 'a');
  +------------------------------+
  | JSON_SET('"x"', '$[0]', 'a') |
  +------------------------------+
  | "a"                          |
  +------------------------------+
  1 row in set (0.00 sec)

• [M to N]では、位置 M の値で始まり、位置 N の値で終わる配列値のサブセットまたは範囲を指定します。

  last は、右端の配列要素のインデックスのシノニムとしてサポートされています。 配列要素の相対アドレス指定もサポートされています。 path が配列値を選択しない場合、このセクションの後半に示すように、path[ last]は path と同じ値に評価されます (右端の配列要素 を参照)。

• パスには、* または ** ワイルドカードを含めることができます:

  • .[*]は、JSON オブジェクトのすべてのメンバーの値に評価されます。

  • [*]は、JSON 配列内のすべての要素の値に評価されます。

  • prefix**suffix は、名前付き接頭辞で始まり、名前付き接尾辞で終わるすべてのパスに評価されます。

• ドキュメントに存在しないパス (存在しないデータに評価される) は、NULL に評価されます。

$ では、次の 3 つの要素を使用してこの JSON 配列を参照します:

[3, {"a": [5, 6], "b": 10}, [99, 100]]

このとき、次のようになります。

• $[0]は、3 に評価されます。

• $[1]は、{"a": [5, 6], "b": 10} に評価されます。

• $[2]は、[99, 100]に評価されます。

• $[3]は NULL に評価されます (存在しない 4 番目の配列要素を参照します)。

$[1]および $[2]は非スカラー値と評価されるため、ネストされた値を選択するより具体的なパス式の基礎として使用できます。 例:

• $[1].a は、[5, 6]に評価されます。

• $[1].a[1]は、6 に評価されます。

• $[1].b は、10 に評価されます。

• $[2][0]は、99 に評価されます。

前述のように、引用符で囲まれていないキー名がパス式で有効でない場合は、キーに名前を付けるパスコンポーネントを引用符で囲む必要があります。 $ がこの値を参照するようにします:

{"a fish": "shark", "a bird": "sparrow"}

キーにはスペースが含まれているため、引用符で囲む必要があります:

• $."a fish"は、shark に評価されます。

• $."a bird"は、sparrow に評価されます。

ワイルドカードを使用するパスは、複数の値を含むことができる配列に評価されます:

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]]                                       |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5]                                                  |
+------------------------------------------------------------+

次の例では、$**.b が複数のパス ($.a.b および $.c.b) に評価し、一致するパス値の配列を生成します:

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+

JSON 配列からの範囲.  範囲を to キーワードとともに使用して、JSON 配列のサブセットを指定できます。 たとえば、次に示すように、$[1 to 3]には配列の 2 番目、3 番目および 4 番目の要素が含まれます:

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

構文は M to N です。ここで、M と N はそれぞれ、JSON 配列の要素の範囲の最初と最後のインデックスです。 N は M より大きい必要があります。M は 0 以上である必要があります。 配列要素は 0 から始まるインデックス付けされます。

ワイルドカードがサポートされているコンテキストで範囲を使用できます。

右端の配列要素.  last キーワードは、配列の最後の要素のインデックスのシノニムとしてサポートされています。 last - N 形式の式は、次のように、相対アドレス指定および範囲定義内で使用できます:

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+
1 row in set (0.01 sec)

パスが配列ではない値に対して評価される場合、評価の結果は値が単一要素配列にラップされた場合と同じです:

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
+-----------------------------------------+
| JSON_REPLACE('"Sakila"', '$[last]', 10) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+
1 row in set (0.00 sec)

column->path は、JSON カラム識別子および JSON パス式とともに JSON_EXTRACT(column, path) のシノニムとして使用できます。 詳しくはセクション12.18.3「JSON 値を検索する関数」,をご覧ください。 JSON カラムインデックスを提供するための生成されたカラムのインデックス付けも参照してください。

一部の関数では、既存の JSON ドキュメントを取得し、なんらかの方法で変更して、結果として変更されたドキュメントを戻します。 パス式は、変更を加えるドキュメント内の場所を示します。 たとえば、JSON_SET()、JSON_INSERT() および JSON_REPLACE() の各関数は、JSON ドキュメントに加えて、ドキュメントを変更する場所と使用する値を記述する 1 つ以上のパスと値のペアを取ります。 関数は、ドキュメント内の既存の値と存在しない値の処理方法が異なります。

このドキュメントについて考えてみます:

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET() は、存在するパスの値を置き換え、存在しないパスの値を追加します。

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

この場合、パス $[1].b[0]は既存の値 (true) を選択します。これは、パス引数 (1) の後の値に置き換えられます。 パス $[2][2]が存在しないため、$[2]によって選択された値に対応する値 (2) が追加されます。

JSON_INSERT() によって新しい値が追加されますが、既存の値は置換されません:

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

JSON_REPLACE() は既存の値を置換し、新しい値を無視します:

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

パスと値のペアは左から右に評価されます。 あるペアを評価して生成されたドキュメントは、次のペアが評価される新しい値になります。

JSON_REMOVE() は、JSON ドキュメントと、ドキュメントから削除する値を指定する 1 つ以上のパスを取ります。 戻り値は、元のドキュメントから、ドキュメント内に存在するパスによって選択された値を引いたものです:

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

パスには次の効果があります:

• $[2]は、[10, 20]を照合して削除します。

• $[1].b[1]の最初のインスタンスは、b 要素内の false と一致し、削除されます。

• $[1].b[1]の 2 番目のインスタンスが一致しません: その要素はすでに削除されており、パスは存在せず、効果もありません。

JSON パス構文

MySQL でサポートされ、このマニュアルの他の場所で説明されている JSON 関数の多く (セクション12.18「JSON 関数」 を参照) では、JSON ドキュメント内の特定の要素を識別するためにパス式が必要です。 パスは、パススコープとそれに続く 1 つ以上のパスレッグで構成されます。 MySQL JSON 関数で使用されるパスの場合、有効範囲は常に検索または操作されるドキュメントで、先頭の $ 文字で表されます。 パスレグはピリオド文字 (.) で区切られます。 配列内のセルは[N]で表され、N は負でない整数です。 キーの名前は、二重引用符で囲まれた文字列または有効な ECMAScript 識別子である必要があります (「ECMAScript 言語仕様」の「識別子名および識別子」を参照)。 JSON テキストなどのパス式は、ascii、utf8 または utf8mb4 文字セットを使用してエンコードする必要があります。 その他の文字エンコーディングは、暗黙的に utf8mb4 に強制変換されます。 完全な構文は次のとおりです:

pathExpression:
    scope[(pathLeg)*]

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'

前述のように、MySQL では、パスのスコープは常に操作対象のドキュメントで、$ として表されます。 '$'を JSON パス式のドキュメントの構文として使用できます。

注記

一部の実装では、JSON パスのスコープのカラム参照がサポートされています。現在、MySQL ではこれらはサポートされていません。

ワイルドカード * および ** トークンは、次のように使用されます:

• .* は、オブジェクト内のすべてのメンバーの値を表します。

• [*]は、配列内のすべてのセルの値を表します。

• [prefix]**suffix は、prefix で始まり suffix で終わるすべてのパスを表します。prefix はオプションですが、suffix は必須です。つまり、パスが ** で終わることはできません。

  また、パスに順序 *** が含まれていない場合もあります。

パス構文の例は、JSON_CONTAINS_PATH()、JSON_SET()、JSON_REPLACE() などの引数としてパスを取る様々な JSON 関数の説明を参照してください。 * および ** ワイルドカードの使用を含む例は、JSON_SEARCH() 関数の説明を参照してください。

MySQL 8.0.2 以降では、to キーワード ($[2 to 10]など) を使用した JSON 配列のサブセットの範囲表記、および配列の右端の要素のシノニムとして last キーワードもサポートされています。 詳細および例については、JSON 値の検索および変更を参照してください。

JSON 値の比較および順序付け

JSON 値は、=, <, <=, >, >=, <>, != および <=> 演算子を使用して比較できます。

次の比較演算子および関数は、JSON 値ではまだサポートされていません:

• BETWEEN

• IN()

• GREATEST()

• LEAST()

リストされている比較演算子および関数の回避策は、JSON 値をネイティブの MySQL 数値または文字列データ型にキャストして、JSON 以外のスカラー型が一貫しているようにすることです。

JSON 値の比較は 2 つのレベルで行われます。 最初のレベルの比較は、比較された値の JSON 型に基づきます。 タイプが異なる場合、比較結果は優先順位の高いタイプによってのみ決定されます。 2 つの値が同じ JSON 型である場合、2 つ目のレベルの比較は型固有のルールを使用して行われます。

次のリストは、JSON 型の優先順位の高いものから低いものへの優先順位を示しています。 (型名は、JSON_TYPE() 関数によって戻される型名です。) 行にまとめて表示されるタイプの優先順位は同じです。 リストの前半にリストされている JSON 型を持つ値は、リストの後半にリストされている JSON 型を持つ値よりも大きく比較されます。

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

同じ優先順位の JSON 値の場合、比較ルールはタイプ固有です:

• BLOB

  2 つの値の最初の N バイトが比較されます。ここで、N は短い値のバイト数です。 2 つの値の最初の N バイトが同一の場合、短い方の値が長い方の値の前に順序付けされます。

• BIT

  BLOB と同じルール。

• OPAQUE

  BLOB と同じルール。 OPAQUE 値は、他のタイプのいずれにも分類されない値です。

• DATETIME

  以前のポイントインタイムを表す値は、後のポイントインタイムを表す値の前に順序付けられます。 最初に MySQL DATETIME 型と TIMESTAMP 型の値がそれぞれ同じ時点を表す場合、それらは等しくなります。

• TIME

  2 つの時間値のうち小さい方が大きい方の値の前に順序付けられます。

• DATE

  以前の日付は、より新しい日付より前にオーダーされます。

• ARRAY

  長さが同じで、配列内の対応する位置の値が等しい場合、2 つの JSON 配列は等しくなります。

  配列が等しくない場合、配列の順序は、違いがある最初の位置の要素によって決まります。 その位置の値が小さい配列が最初に順序付けられます。 短い配列のすべての値が長い配列の対応する値と等しい場合は、短い配列が最初に順序付けられます。

  例:

  [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]

• BOOLEAN

  JSON false リテラルが JSON true リテラルより小さい場合。

• OBJECT

  2 つの JSON オブジェクトは、同じキーセットを持ち、各キーが両方のオブジェクトで同じ値を持つ場合に等しくなります。

  例:

  {"a": 1, "b": 2} = {"b": 2, "a": 1}

  等しくない 2 つのオブジェクトの順序は指定されませんが、決定的です。

• STRING

  文字列は、比較される 2 つの文字列の utf8mb4 表現の最初の N バイトで字句的に順序付けされます (N は短い文字列の長さです)。 2 つの文字列の最初の N バイトが同一の場合、短い文字列は長い文字列より小さいとみなされます。

  例:

  "a" < "ab" < "b" < "bc"

  この順序付けは、照合 utf8mb4_bin を使用した SQL 文字列の順序付けと同等です。 utf8mb4_bin はバイナリ照合であるため、JSON 値の比較では大/小文字が区別されます:

  "A" < "a"

• INTEGER, DOUBLE

  JSON 値には、正確な値の数値および近似値の数値を含めることができます。 これらのタイプの数値の概要は、セクション9.1.2「数値リテラル」 を参照してください。

  ネイティブ MySQL 数値型を比較するためのルールは セクション12.3「式評価での型変換」 で説明されていますが、JSON 値内の数値を比較するためのルールは多少異なります:

  • ネイティブの MySQL INT 数値型と DOUBLE 数値型を使用する 2 つのカラムの比較では、すべての比較に整数と double が含まれることがわかっているため、すべての行で整数が double に変換されます。 つまり、正確な値の数値は近似値の数値に変換されます。

  • 一方、クエリーで数値を含む 2 つの JSON カラムを比較する場合、数値が整数であるか倍精度であるかは事前にわかりません。 すべての行で最も一貫性のある動作を提供するために、MySQL は近似値の数値を正確な値の数値に変換します。 結果の順序付けは一貫性があり、正確な値の数値の精度は失われません。 たとえば、スカラー 9223372036854775805、9223372036854775806、9223372036854775807 および 9.223372036854776 e18 の場合、順序は次のようになります:

    9223372036854775805 < 9223372036854775806 < 9223372036854775807
    < 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001

  JSON 以外の数値比較ルールを使用するための JSON 比較では、順序に一貫性がない可能性があります。 数値の通常の MySQL 比較ルールでは、次の順序付けが行われます:

  • 整数比較:

    9223372036854775805 < 9223372036854775806 < 9223372036854775807

    (9.223372036854776 e18 には定義されていません)

  • 二重比較:

    9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

JSON 値を SQL NULL と比較する場合、結果は UNKNOWN になります。

JSON 値と非 JSON 値を比較するために、非 JSON 値は次のテーブルのルールに従って JSON に変換されてから、前述のように比較されます。

JSON 値と非 JSON 値の間の変換

次のテーブルに、JSON 値と他のタイプの値の間のキャスト時に MySQL が従うルールのサマリーを示します:

表 11.3 JSON 変換ルール

その他のタイプ	CAST(other type AS JSON)	CAST(JSON AS other type)
JSON	変更なし	変更なし
utf8 文字 (utf8mb4, utf8, ascii)	文字列は JSON 値に解析されます。	JSON 値は utf8mb4 文字列にシリアライズされます。
その他の文字タイプ	その他の文字エンコーディングは、暗黙的に utf8mb4 に変換され、utf8 文字タイプで説明されているように扱われます。	JSON 値は utf8mb4 文字列にシリアライズされ、他の文字エンコーディングにキャストされます。 結果が意味を持たない場合があります。
NULL	JSON 型の NULL 値になります。	該当なし。
ジオメトリタイプ	ジオメトリ値は、ST_AsGeoJSON() をコールして JSON ドキュメントに変換されます。	不正な操作です。 回避策: CAST(json_val AS CHAR) の結果を ST_GeomFromGeoJSON() に渡します。
その他すべてのタイプ	単一のスカラー値で構成される JSON ドキュメントになります。	JSON ドキュメントがターゲット型の単一のスカラー値で構成され、そのスカラー値をターゲット型にキャストできる場合は成功します。 それ以外の場合は、NULL を返し、警告を生成します。

JSON 値の ORDER BY および GROUP BY は、次の原則に従って機能します:

• スカラー JSON 値の順序付けでは、前述のルールと同じルールが使用されます。

• 昇順ソートの場合、SQL NULL は JSON null リテラルを含むすべての JSON 値の前に順序付けられます。降順ソートの場合、SQL NULL は JSON null リテラルを含むすべての JSON 値の後に順序付けされます。

• JSON 値のソートキーは max_sort_length システム変数の値によってバインドされるため、最初の max_sort_length バイトの後にのみ異なるキーは等しいと比較されます。

• 非スカラー値のソートは現在サポートされておらず、警告が発生します。

ソートの場合、JSON スカラーを他のネイティブ MySQL 型にキャストすると便利です。 たとえば、jdoc という名前のカラムに、id キーと負でない値で構成されるメンバーを持つ JSON オブジェクトが含まれる場合、次の式を使用して id 値でソートします:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

生成されたカラムが ORDER BY と同じ式を使用するように定義されている場合、MySQL オプティマイザはそれを認識し、クエリー実行計画のインデックスの使用を検討します。 セクション8.3.11「生成されたカラムインデックスのオプティマイザによる使用」を参照してください。

JSON 値の集計

JSON 値の集計では、SQL NULL 値は他のデータ型と同様に無視されます。 NULL 以外の値は数値型に変換され、MIN()、MAX() および GROUP_CONCAT() を除いて集計されます。 数値への変換では、数値スカラーである JSON 値に対して意味のある結果が生成される必要がありますが、(値によっては) 精度の切捨ておよび損失が発生する可能性があります。 他の JSON 値の数に変換しても、意味のある結果が得られない場合があります。