MySQL 5.7からはJSON型のカラムが使えるようになりました
このJSON型にはMySQLのいわゆるNULL
値とは別に値がないことを示すnull
という値があって混乱したのでメモしておきます
(この記事の内容はたまたま手元にあったMySQL 5.7.20で確かめました)
便宜上この記事ではNULL
をMySQL全般で使われている値がないことを示す値、null
をJSON型で値がないことを表す値のことを指しておきます(実際にはNULL
の方はケースインセンシティブで、null
の方は小文字のみです)
呼び方がないと困るのでnull
の方をJSON型のnull
と呼んでおきます
値の取得
JSON_EXTRACT()
や`カラム名`->"JSONのパス"
と書くとJSONの値を取得できます
mysql> SELECT JSON_EXTRACT('{"foo": "123"}', '$.foo');
+-----------------------------------------+
| JSON_EXTRACT('{"foo": "123"}', '$.foo') |
+-----------------------------------------+
| "123" |
+-----------------------------------------+
存在しないキーを取得するとNULL
が返ってきます
mysql> SELECT JSON_EXTRACT('{}', '$.foo');
+-----------------------------+
| JSON_EXTRACT('{}', '$.foo') |
+-----------------------------+
| NULL |
+-----------------------------+
JSONの値がnull
であるキーを取得するとJSON型のnull
が返ってきます
mysql> SELECT JSON_EXTRACT('{"foo": null}', '$.foo');
+----------------------------------------+
| JSON_EXTRACT('{"foo": null}', '$.foo') |
+----------------------------------------+
| null |
+----------------------------------------+
NULLの判定
NULL
の場合
NULL
はIS NULL
で判定したり、IFNULL()
でデフォルト値を設定したりできます
mysql> SELECT JSON_EXTRACT('{}', '$.foo') IS NULL;
+-------------------------------------+
| JSON_EXTRACT('{}', '$.foo') IS NULL |
+-------------------------------------+
| 1 |
+-------------------------------------+
mysql> SELECT IFNULL(JSON_EXTRACT('{}', '$.foo'), "This is NULL.");
+------------------------------------------------------+
| IFNULL(JSON_EXTRACT('{}', '$.foo'), "This is NULL.") |
+------------------------------------------------------+
| This is NULL. |
+------------------------------------------------------+
null
の場合
困ったのはJSON型のnull
のときです
通常のNULL
と同様の判定をしても、これはNULL
ではないのですり抜けてしまいます
mysql> SELECT JSON_EXTRACT('{"foo": null}', '$.foo') IS NULL;
+------------------------------------------------+
| JSON_EXTRACT('{"foo": null}', '$.foo') IS NULL |
+------------------------------------------------+
| 0 |
+------------------------------------------------+
mysql> SELECT IFNULL(JSON_EXTRACT('{"foo": null}', '$.foo'), "This is NULL.");
+-----------------------------------------------------------------+
| IFNULL(JSON_EXTRACT('{"foo": null}', '$.foo'), "This is NULL.") |
+-----------------------------------------------------------------+
| null |
+-----------------------------------------------------------------+
JSON_TYPE()
による判定
一つの方法としてはJSON_TYPE()
による判定があります
JSON型のnull
(や文字列の"null"
)に対してJSON_TYPE()
関数を使うと文字列の"NULL"
が返ってくるので判定できます
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{"foo": null}', '$.foo'));
+----------------------------------------+
| JSON_TYPE(JSON_EXTRACT('{}', '$.foo')) |
+----------------------------------------+
| NULL |
+----------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{"foo": null}', '$.foo')) = 'NULL';
+------------------------------------------------------------+
| JSON_TYPE(JSON_EXTRACT('{"foo": null}', '$.foo')) = 'NULL' |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
注意点として、NULL
に対してJSON_TYPE()
を実行すると文字列ではなく正真正銘のNULL
が返ってきて表示上同じで混乱しました
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{}', '$.foo'));
+----------------------------------------+
| JSON_TYPE(JSON_EXTRACT('{}', '$.foo')) |
+----------------------------------------+
| NULL |
+----------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{}', '$.foo')) = 'NULL';
+-------------------------------------------------+
| JSON_TYPE(JSON_EXTRACT('{}', '$.foo')) = 'NULL' |
+-------------------------------------------------+
| NULL |
+-------------------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT('{}', '$.foo')) IS NULL;
+------------------------------------------------+
| JSON_TYPE(JSON_EXTRACT('{}', '$.foo')) IS NULL |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
JSON型のnull
との比較
例えば文字列の'null'
をJSON型にキャストするとnull
になるので=
などで比較できるようです
mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null |
+----------------------+
mysql> SELECT JSON_EXTRACT('{"foo": null}', '$.foo') = CAST('null' AS JSON);
+---------------------------------------------------------------+
| JSON_EXTRACT('{"foo": null}', '$.foo') = CAST('null' AS JSON) |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
文字列にしてから判定
JSON_UNQUOTE()
関数や`カラム名`->>"JSONのパス"
またはキャストなどで一度文字列にしてから判定することもできます
ただしクォートなしにするとJSONの値として文字列の'null'
が入っていた場合とは区別できなくなります
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT('{"foo": null}', '$.foo')) = 'null';
+---------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT('{"foo": null}', '$.foo')) = 'null' |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
mysql> SELECT CAST(JSON_EXTRACT('{"foo": null}', '$.foo') AS CHAR) = 'null';
+---------------------------------------------------------------+
| CAST(JSON_EXTRACT('{"foo": null}', '$.foo') AS CHAR) = 'null' |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
NOT NULL
制約
これまで見てきたようにnull
はNULL
ではないのでテーブルのスキーマでカラムにNOT NULL
制約がついていても挿入できます
mysql> CREATE TABLE foo (bar JSON NOT NULL);
mysql> INSERT INTO foo VALUES ('null');
mysql> IINSERT INTO foo VALUES ('{"foo": 123}');
mysql> SELECT * FROM foo;
+--------------+
| bar |
+--------------+
| null |
| {"foo": 123} |
+--------------+
mysql> INSERT INTO foo VALUES (NULL);
ERROR 1048 (23000): Column 'bar' cannot be null
ちなみに実は空のJSON({}
)とnull
は別扱いで両方挿入できるみたいです
mysql> CREATE TABLE foo (bar JSON NOT NULL);
mysql> INSERT INTO foo VALUES ('null');
mysql> INSERT INTO foo VALUES ('{}');
mysql> SELECT * FROM foo;
+------+
| bar |
+------+
| null |
| {} |
+------+
どうでもいいことを最後に書いておくと、NOT NULL
制約をつけずにやるとnull
もNULL
も挿入できて見た目が楽しくなります(?)
mysql> CREATE TABLE foo (bar JSON DEFAULT NULL);
mysql> INSERT INTO foo VALUES (NULL);
mysql> INSERT INTO foo VALUES ('null');
mysql> SELECT * FROM foo;
+------+
| bar |
+------+
| NULL |
| null |
+------+