他の場所で説明したように、JSON カラムは直接インデックス付けできません。 このようなカラムを間接的に参照するインデックスを作成するには、次の例に示すように、インデックス付けする必要がある情報を抽出する生成カラムを定義し、生成されたカラムにインデックスを作成します:

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

(この例では、表示領域に合うように最後のステートメントの出力をラップしています。)

EXPLAIN を SELECT または -> または ->> 演算子を使用する 1 つ以上の式を含む他の SQL ステートメントで EXPLAIN を使用する場合、JSON_EXTRACT() および (必要に応じて) JSON_UNQUOTE() を使用して、この EXPLAIN ステートメントに続いてすぐに SHOW WARNINGS から出力の通り、これらの式はそれらの相当に変換されます:

mysql> EXPLAIN SELECT c->>"$.name"
     > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
`c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
json_extract(`test`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

追加情報および例については、-> および ->> の演算子と、JSON_EXTRACT() および JSON_UNQUOTE() の関数の説明を参照してください。

この手法を使用して、GEOMETRY カラムなど、直接インデックス付けできない他のタイプのカラムを間接的に参照するインデックスを提供することもできます。

MySQL 8.0.21 以降では、式を使用するクエリーの最適化に使用できる式を指定した JSON_VALUE() 関数を使用して、JSON カラムにインデックスを作成することもできます。 詳細および例については、その関数の説明を参照してください。

NDB Cluster での JSON カラムと間接インデックス

MySQL NDB Cluster では、次の条件に従って JSON カラムの間接インデックスを使用することもできます:

次に示す jempn テーブルの作成に使用される CREATE TABLE ステートメントは、NDB と互換性があるように変更された、前述の jemp テーブルのバージョンです:

CREATE TABLE jempn (
  a BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  c JSON DEFAULT NULL,
  g INT GENERATED ALWAYS AS (c->"$.name") STORED,
  INDEX i (g)
) ENGINE=NDB;

次の INSERT ステートメントを使用して、このテーブルに移入できます:

INSERT INTO jempn (a, c) VALUES
  (NULL, '{"id": "1", "name": "Fred"}'),
  (NULL, '{"id": "2", "name": "Wilma"}'),
  (NULL, '{"id": "3", "name": "Barney"}'),
  (NULL, '{"id": "4", "name": "Betty"}');

次に示すように、NDB でインデックス i を使用できるようになりました:

mysql> EXPLAIN SELECT c->>"$.name" AS name
          FROM jempn WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jempn
   partitions: p0,p1
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2)
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select
json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
`test`.`jempn` where (`test`.`jempn`.`g` > 2)
1 row in set (0.00 sec)

格納された生成カラムは DataMemory を使用し、このようなカラムのインデックスは IndexMemory を使用することに注意してください。