如何查询MySQL JSON列中字段的空值与非空值？

问题场景 #

在MySQL的JSON类型列extra_data中存储了如下结构的JSON数据：

{"tracking_number": "", "payment_amount": null, "payment_address": null}
{"tracking_number": "", "payment_amount": null, "payment_address": "testaddress"}

需要筛选出payment_address不为JSON null的记录，但之前尝试的几种查询都未得到正确结果。

错误查询分析 #

你尝试的三个查询都存在问题：

• SELECT * FROM orders WHERE extra_data->"$.payment_address" != NULL;：SQL中判断空值必须使用IS NOT NULL，!= NULL是无效语法，永远不会匹配到任何记录。
• SELECT * FROM orders WHERE extra_data->"$.payment_address" IS NOT NULL;：这个条件判断的是SQL层面的NULL（比如extra_data字段本身为SQL NULL，或JSON路径不存在该字段），但如果JSON结构里的payment_address是JSON null，该表达式返回的是JSON类型的null，并非SQL NULL，因此会错误地包含这类记录。
• SELECT * FROM orders WHERE extra_data->"$.payment_address" != "null";：将JSON null与字符串"null"进行比较，二者类型完全不同，无法正确匹配。

正确查询方法 #

1. 筛选payment_address不为JSON null的记录

可以通过将JSON null转为JSON类型后进行比较：

SELECT * FROM orders 
WHERE extra_data->'$.payment_address' <> CAST('null' AS JSON);

或者使用JSON_TYPE函数判断字段的JSON类型：

SELECT * FROM orders 
WHERE JSON_TYPE(extra_data->'$.payment_address') != 'NULL';

2. 筛选payment_address为JSON null的记录

对应地，查询JSON字段为null的记录可以用：

SELECT * FROM orders 
WHERE extra_data->'$.payment_address' = CAST('null' AS JSON);

或：

SELECT * FROM orders 
WHERE JSON_TYPE(extra_data->'$.payment_address') = 'NULL';

3. 额外：同时排除空字符串的情况

如果需要同时排除payment_address为空字符串（""）的记录，可以结合JSON_VALUE函数：

SELECT * FROM orders 
WHERE extra_data->'$.payment_address' <> CAST('null' AS JSON)
AND JSON_VALUE(extra_data, '$.payment_address') != '';

内容的提问来源于stack exchange，提问作者she hates me