Help us understand the problem. What is going on with this article?

小ネタ/MySQL 8.0.21 で JSON_VALUE()

コロナ禍のあおりを受けて新機能が少なくなった 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 EMPTYON 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のところに波線で警告(?)が表示されますが(赤枠)、無視して実行することができます(青枠)。

mysql8021_json_value.png


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

投入されたデータ
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)

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

余談

かつて、

で 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 のバージョン依存があると怖い気がする…)。


hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
https://hmatsu47.hatenablog.com/
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です
https://wp.infra-workshop.tech/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away