コロナ禍のあおりを受けて新機能が少なくなった 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)
さて、実際にやってみます。
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
句で指定するときに**「インデックス定義と一致しないと使われない」**という仕様なので、もしや…
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
**が邪魔をしていたようです。
ちなみに、
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)
大文字小文字の違いは大丈夫ですが、
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 のバージョン依存があると怖い気がする…)。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本(無料で配布中!)