結論
- json_extractで文字列値を取得する場合、ダブルクォート付きで取得される
- json_extractで取得したダブルクォート付き文字列を直接=で比較する場合は暗黙的な型変換が行われる
- ifnullでラップして=で比較すると型変換が行われなくなる
- json_extractとjson_valueを適切に使い分ける
- json_extractは結果を別のJSON関数に渡す場合に使う
- json_valueはWHEREやSELECTなどで値を取得する場合に使う
詰まった経緯
元の実装
メール受信設定をまとめたJSONカラムから、特定の設定がオン（1）のユーザーを取得するSQLがありました。
select
user_id
from
users
where
json_extract(mail_config_json, "$.receive_notice_flg") = 1
mail_config_jsonは以下のような構造をしています。
{
"receive_notice_flg": "1"
}
仕様追加
その後、JSON内に設定値がなければ、システム内のデフォルト値を使用するという仕様追加を実施しました。
JSON内に設定値がなければ、というのは、json_extractの結果がNULLだったらと読み替えられます。
そのため、WHERE句を以下のように修正しました。
ifnull(
json_extract(
mail_config_json,
"$.receive_notice_flg"
),
1 /** システムのデフォルト値 */
) = 1
異常が発生
上記の実装は、設定値がなければシステムのデフォルト値を使用するという仕様に対しては正しく動作しました。
しかし、設定がオン（1）なのにも関わらず、対象外となるようになってしまいました。
問題の切り分け
-
設定がオンの場合、ifnullが無いときと同じ挙動になるはず
-
json_extractを取り除き固定値で検証しても結果は同じだった
select ifnull('"1"', 1) = 1 /** 0（＝false） */ -
これまでは問題なかったのに、ifnullを掛けるとうまくいかなくなる
-
暗黙的な型変換が働かなくなる？
-
json_valueという類似の関数があるので、そちらではどうか確かめた
解決
json_extractの代わりにjson_valueを使ったところ、正常に動作するようになりました。
学び
根本的な問題は、json_extractとjson_valueの仕様やユースケースを理解せず、「動くから」とjson_extractを多用していたことです。
これまでjson_extractでうまく動いていたのは、MariaDBがたまたま暗黙的に型変換していたため意図どおりに動いていただけで、本来はjson_valueを使うべきでした。
| 関数名 | 出力 | ユースケース |
|---|---|---|
| json_extract | 指定したパスの値をそのまま切り取る。文字列の場合は、"1"や"hoge"のようにダブルクォートを含めて取得される。 |
取得した値を別のJSON関数で使用する時。 |
| json_value | 指定したパスのスカラー値（数値、真偽値、文字列）を取得する。それ以外の値は取得できない。 | 取得した値を直接使用する時。select句での取得、where句での比較。 |
| SQL | 結果 | 説明 |
|---|---|---|
| json_extract('{"flg":"1"}', "$.flg") | "1" | ダブルクォート付きの文字列を返す |
| json_extract('{"flg":"1"}', "$.flg") = 1 | 1 | 比較では暗黙的に型変換されてTrueになる |
| ifnull(json_extract('{}', "$.flg"), 1) = 1 | 1 | NULL時フォールバック値が使われるとTrue |
| ifnull(json_extract('{"flg":"1"}', "$.flg"), 1) = 1 | 0 | json_extract 結果をifnullで包むと型変換されずFalse |
| ifnull(json_value('{"flg":"1"}', "$.flg"), 1) = 1 | 1 | json_valueはスカラ値を返し直接比較されてTrue |
備考
副次的な問題として、ifnullを使うとjson_extractで得られたダブルクォート付き文字列に対する暗黙的な型変換が起きなくなることもわかりました。
json_extractで取得した値が変な変換のされ方をしているように見えますが…
| SQL | 結果 | 説明 |
|---|---|---|
| '"1"'=1 | 0 | そのままの文字列'"1"'は数値と比較してFalse。暗黙変換されない。 |
| json_extract('{"flg":"1"}',"$.flg")=1 | 1 | json_extractの結果もダブルクォート付き'"1"'だが、比較時に暗黙的に数値へ変換されてTrueになる。 |
| ifnull(json_extract('{"flg":"1"}', "$.flg"), 1) = 1 | 0 | json_extract結果をifnullで包むと型変換されずFalse |