コロナ禍のあおりを受けて新機能が少なくなった MySQL 8.0.21 の新機能の 1 つ、JSON_VALUE()をちょっとだけ試します。

（まあ、本来ならマイナーバージョンで新機能の追加をすること自体が普通じゃないんですけどね。マイナーバージョンアップの都度新機能が加わる状況でも、Oracle さんは「メンテナンスリリース」と言い張っている謎。あ、これは dis りなどではなくて、お約束のネタです。）

`JSON_VALUE()`とは？

公式リファレンスマニュアルの説明にあるとおり、従来の

CAST() AS
JSON_UNQUOTE()
JSON_EXTRACT()

の組み合わせを 1 つの関数として、

JSON_VALUE(json_doc, path RETURNING type)

の形式で記述することができるようになったものです。

CAST() AS が不要な場合は、

JSON_VALUE(json_doc, path)

です（その他、ON EMPTY・ON ERRORの指定もできます。詳しくは公式リファレンスマニュアルをご覧ください）。

関数インデックスとして使う

公式リファレンスマニュアルにも例示がありますが、これは関数インデックスとして使うと便利です。

マニュアルの例示と同じ設計のテーブルで試してみます。

テーブル作成

mysql> CREATE DATABASE jsontest;
Query OK, 1 row affected (0.01 sec)

mysql> USE jsontest;
Database changed
mysql> CREATE TABLE t1(
    ->   j JSON,
    ->   INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
    -> );
1 row in set (0.00 sec)

ちなみに、MySQL Workbench 8.0.21 ではなぜかUNSIGNEDのところに波線で警告（？）が表示されますが（赤枠）、無視して実行することができます（青枠）。

データを入れてみました。

投入されたデータ

mysql> SELECT * FROM t1;
+----------------------------------+
| j                                |
+----------------------------------+
| {"id": 100, "val": [1, 2, 3]}    |
| {"id": 101, "val": [4, 5, 6, 7]} |
| {"id": 110, "val": [8, 9, 0]}    |
| {"id": 120, "val": [1, 2]}       |
| {"id": 122, "val": 3}            |
| {"id": 130, "val": [4, 5]}       |
| {"id": 140, "val": [6, 7, 8]}    |
| {"id": 150, "val": [9, 0, 1, 2]} |
| {"id": 200, "val": [3, 4, 5]}    |
| {"id": 220, "val": [6, 7]}       |
+----------------------------------+
10 rows in set (0.00 sec)

さて、実際にやってみます。

試してみる(1)

mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 150\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

おや？関数インデックスが効いていませんね？

念のためSHOW CREATE TABLEしてみます。

テーブル定義を見てみる

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `j` json DEFAULT NULL,
  KEY `i1` ((json_value(`j`, _utf8mb4'$.id' returning unsigned)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

関数インデックスはWHERE句で指定するときに「インデックス定義と一致しないと使われない」という仕様なので、もしや…

試してみる(2)

mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id' returning unsigned) = 150\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

…あ、やっぱり。勝手に付いた_utf8mb4が邪魔をしていたようです。

ちなみに、

試してみる(3)

mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, _UTF8MB4'$.id' RETURNING UNSIGNED) = 150\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

大文字小文字の違いは大丈夫ですが、

試してみる(4)

mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, _UTF8MB4'$.id') = 150\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

一部分でも省略すると、関数インデックスは使われません。

余談

かつて、

Node.js から X DevAPI を使って MySQL 8.0.17 のドキュメントデータベースと RDB テーブルにアクセスする

で X DevAPI を使って、

コレクションの作成

            // Create Collection
            const session = await mysqlx.getSession(connectParam);
            const collection = await session.getSchema(schemaName).createCollection(collectionName);
            const flag = await collection.createIndex('labels', 
                {fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});

コレクションとインデックスを作成したところ、実際に出来上がったテーブルは、

作成されたテーブル

CREATE TABLE `image_labeling` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `labels` ((cast(json_extract(`doc`,_utf8mb4'$.labels') as char(100) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

でした。

バージョンアップに伴ってこのあたりのテーブル定義も変わっていくんですかね？（Server vs Connector のバージョン依存があると怖い気がする…）。

Qiitaに投稿したMySQL 8.0関連記事
MySQL 8.0 の薄い本（無料で配布中！）

小ネタ／MySQL 8.0.21 で JSON_VALUE()

JSON_VALUE()とは？

関数インデックスとして使う

余談

`JSON_VALUE()`とは？