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 | +------+