MySQL8.0のJSON型における正規化について - 主夫ときどきプログラマ

blockchain-kyoto.connpass.com

Blockchain勉強会 in Kyoto #05でMySQL8.0 使いたいからブロックチェーン実装してみたというタイトルで発表してきました。 MySQLを使いたいというモチベーションだったので、ブロックチェーンの話はこじつけではありましたが、発表できてよかったと思います。

スライドはこちら（後日、別の勉強会でも発表したのでアップデートされています）

JSON型のCanonicalizeってどうなんですか？という質問

発表の中でJSON型に保存したカラムの値からMD5のHash値を計算する部分があります。それについて勉強会後に質問されました。「Oracleが開発してるんできっとちゃんとしてますよねー。」と話していましたが、実際のところはわからなかったので調べることにしました。

Canonicalize とは

データの標準化や正規化を意味する言葉です。例えばJSONの場合同じデータ構造でも文字列にしたときに別のものになってしまうことがあります。

a = {
  "name": "zorori", "skill": "c++"
}

b = {
  "skill": "c++", "name": "zorori"
}

aとbは同じname, skill というキーを持ちそれぞれ同じ値を保持しています。そのためデータの表現としては同じものですが、定義されている順序が異なるため文字列化した時に異なった結果になってしまいます。

// on nodojs

// 文字列化しても同じ文字列にならない
> JSON.stringify(a)
'{"name":"zorori","skill":"c++"}'

> JSON.stringify(b)
'{"skill":"c++","name":"zorori"}'

// オブジェクトの比較も一致しない
> a == b
false
> a === b
false

厳密には JavaScript は JSON ではないので注意が必要ですが、JSONを文字列として表現する時、ルールに則って文字列化することで同じデータが異なる文字列にならないようにする必要があります。これをCanonicalizeといいます。

JavaScriptとJSONの関係についてはこちらを御覧ください。JSON: The JavaScript subset that isn't — Timeless

MySQL8.0 で動作確認する

JSON_OBJECT() 関数の挙動

動作を確認してみましょう。まずは JSON_OBJECT() 関数の動作をみてみます。ドキュメントはこちら。引数にKey, Value を交互に与えることでJSONオブジェクトを作成します。

mysql> select JSON_OBJECT('name', 'ishishi', 'skill', 'melon-pan');
+------------------------------------------------------+
| JSON_OBJECT('name', 'ishishi', 'skill', 'melon-pan') |
+------------------------------------------------------+
| {"name": "ishishi", "skill": "melon-pan"}            |
+------------------------------------------------------+

mysql> select JSON_OBJECT('skill', 'melon-pan', 'name', 'ishishi');
+------------------------------------------------------+
| JSON_OBJECT('skill', 'melon-pan', 'name', 'ishishi') |
+------------------------------------------------------+
| {"name": "ishishi", "skill": "melon-pan"}            |
+------------------------------------------------------+

mysql> select JSON_OBJECT('skill', 'melon-pan', 'name', 'ishishi')  = JSON_OBJECT('name', 'ishishi', 'skill', 'melon-pan') as result;
+--------+
| result |
+--------+
|      1 |
+--------+

MD5のハッシュ値も比較してみましょう。

mysql> select md5(JSON_OBJECT('skill', 'melon-pan', 'name', 'ishishi')) md5
    -> union all
    -> select md5(JSON_OBJECT('name', 'ishishi', 'skill', 'melon-pan')) md5;
+----------------------------------+
| md5                              |
+----------------------------------+
| 72e1a15793042e2a7b9f250c7b37f750 |
| 72e1a15793042e2a7b9f250c7b37f750 |
+----------------------------------+

JSON_OBJECT() の戻り値は入力の順序によらず同じ表現のデータが作成されています。

JSON型での挙動

まずJSON型のカラムをもつTableを用意します。

CREATE TABLE `jdoc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

データを挿入します。

mysql> insert into jdoc set doc = '{"name":"noshishi", "skill":"onigiri"}';
Query OK, 1 row affected (0.10 sec)

mysql> insert into jdoc set doc = '{"skill":"onigiri", "name":"noshishi"}';
Query OK, 1 row affected (0.02 sec)

mysql> insert into jdoc set doc = JSON_OBJECT('name', 'noshishi', 'skill', 'onigiri');
Query OK, 1 row affected (0.01 sec)

挿入したデータはそれぞれ違う入力方法です。Tableにはどのように保持されているかを確認します。

mysql> select * from jdoc;
+----+------------------------------------------+
| id | doc                                      |
+----+------------------------------------------+
|  1 | {"name": "noshishi", "skill": "onigiri"} |
|  2 | {"name": "noshishi", "skill": "onigiri"} |
|  3 | {"name": "noshishi", "skill": "onigiri"} |
+----+------------------------------------------+

どの行も同じ形式のデータのようです。 MD5のハッシュ値も比較してみましょう。

mysql> select id, md5(doc) from jdoc;
+----+----------------------------------+
| id | md5(doc)                         |
+----+----------------------------------+
|  1 | 275aceba7931ad3e345e31d48a949e9b |
|  2 | 275aceba7931ad3e345e31d48a949e9b |
|  3 | 275aceba7931ad3e345e31d48a949e9b |
+----+----------------------------------+

ハッシュ値も同じなのでTableに保持されているデータは同じものといえます。

公式ドキュメントに記述がある

Normalization, Merging, and Autowrapping of JSON Values
ドキュメントには以下のように記述されています。

When a string is parsed and found to be a valid JSON document, it is also normalized. https://dev.mysql.com/doc/refman/8.0/en/json.html#json-normalization

Normalize と表現されていますが、MySQLではJSONは正規化された値が利用されるという記述があります。また以下に記述されているようにJSON関数は正規化された値を返すようです。