明滅するプログラマの思索

WEBエンジニアとして勤務している一介の男が、日々気づいたことをまとめるブログです

MySQL で既存テーブルに JSON 型カラムを追加すると、デフォルトで null が入る

MySQL5.7以降で追加された JSON 型ですが、仕様を把握していないとハマるポイントがあります。
たとえば、テーブルに NOT NULL でJSON型のカラムを追加をすると、既存レコードには null が入ります。
この null は MySQL の NULL ではなく(NOT NULL で追加しているのだから当然ですが)、JSON の null です。

null が入ったレコードを抽出するには

JSON 型カラムに条件を指定してレコードを抽出したい場合、JSON_CONTAINS() や JSON_SEARCH()、JSON_EXTRACT() を用いますが、これらはあくまで存在する JSON データの中身を検索するものであり、「空っぽ」であるものを対象とすることはできません。
そして、個人的にはこれが結構使いづらいと思っているところなのですが、JSON 型カラムに対し、通常の等式「=」を用いて、文字列と見なした完全一致の条件を作成することができません。

SELECT * FROM json_table WHERE json_column = '{"a" : 1, "b" : 2}';
// 上記で検索してもそれに合致する文字列を持つJSONレコードは抽出できない

そして、これは null が入った JSON レコードに対しても同様となります。

SELECT * FROM json_table WHERE json_column = 'null';
// 上記で null の入ったレコードは抽出できない
SELECT * FROM json_table WHERE json_column IS NULL;
// 当たり前だが上記ももちろんムリ

正解は以下となります。

SELECT * FROM json_table WHERE json_column = CAST('null' AS JSON);

JSON 型カラムに対しては、等式「=」を使用する場合でも JSON 型の値と比較しなければならないようで、CAST() を利用して null 文字列を JSON 型にキャストしてあげれば、条件値として指定することができるようになります。
ただ、この仕様は使いづらいですね。phpMyAdmin などの GUIツールを使っていると、JSON 型カラムに対しては検索機能がまるで使えません。
何らかのスイッチなりを用意して、文字列との比較も簡単にできるようにしてくれたほうが、利便性が高まると思います。