MySQL でJSON型のnull/空配列のデータをフィルターする

環境は MySQL 5.7

値が null のレコードを検索する

SELECT data FROM test WHERE JSON_EXTRACT(data, "$.value") = CAST('null' AS JSON);

+-----------------+
| data            |
+-----------------+
| {"value": null} |
+-----------------+

値が 空配列 のレコードを検索する

SELECT data FROM test WHERE JSON_EXTRACT(data, "$.value") = JSON_ARRAY();

+---------------+
| data          |
+---------------+
| {"value": []} |
+---------------+

参考

• https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-object
• https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract
• https://dev.mysql.com/doc/refman/5.7/en/json.html