我正在使用MySQL 8.0.21。我需要编写一个使用JSON列类型的查询。JSON文档中的一些数据有空值，我想过滤掉这些空值。
可能的行示例，JSON文档中的大多数属性已被删除以简化：

jsonColumn
'{"value":96.0}'
'{"value":null}' -- This is the row I am trying to filter out
NULL

以下是我的尝试：

-- Removed columns where jsonColumn was NULL but, NOT columns where jsonColumn->'$.value' was null. 
SELECT * 
FROM <table>
WHERE jsonColumn->'$.value' IS NOT NULL;

-- Note the unquote syntax, ->>. The code above uses ->.
-- Produced the same result as the code above.
SELECT * 
FROM <table>
WHERE jsonColumn->>'$.value' IS NOT NULL;

-- Produced same result as the two above. Not surprised because -> is an alias of JSON_EXTRACT
SELECT * 
FROM <table>
WHERE JSON_EXTRACT(jsonColumn, '$.value') IS NOT NULL;

-- Produced same result as the three above. Not surprised because ->> is an alias of JSON_EXTRACT
SELECT * 
FROM <table>
WHERE JSON_UNQUOTE(JSON_EXTRACT(jsonColumn, '$.value')) IS NOT NULL;

-- Didn't really expect this to work. It didn't work. For some reason it filters out all records from the select.
SELECT *
FROM <table>
WHERE jsonColumn->'$.value' != NULL;

-- Unquote syntax again. Produced the same result as the code above.
SELECT *
FROM <table>
WHERE jsonColumn->>'$.value' != NULL;

-- Didn't expect this to work. Filters out all records from the select.
SELECT *
FROM <table>
WHERE JSON_EXTRACT(jsonColumn, '$.value') != NULL;

-- Didn't expect this to work. Filters out all records from the select.
SELECT *
FROM <table>
WHERE JSON_UNQUOTE(JSON_EXTRACT(jsonColumn, '$.value')) != NULL;

-- I also tried adding a boolean value to one of the JSON documents, '{"test":true}'. These queries did not select the record with this JSON document.
SELECT * 
FROM <table>
WHERE jsonColumn->'$.test' IS TRUE;
SELECT * 
FROM <table>
WHERE jsonColumn->>'$.test' IS TRUE;

我注意到一些有趣的事情...
比较其他值起作用。例如...

-- This query seems to work fine. It filters out all records except those where jsonColumn.value is 96.
SELECT *
FROM <table>
WHERE jsonColumn->'$.value' = 96;

我注意到的另一个有趣的事情，在上面一些例子的注解中提到过，是空值检查的一些奇怪行为，如果jsonColumn是空值，空值检查会过滤掉记录，即使我知道我正在访问jsonColumn-〉'$. value'。
不确定这是否清楚，所以让我详细说明一下...

-- WHERE jsonColumn->>'$.value' IS NOT NULL
jsonColumn
'{"value":96.0}'
'{"value":null}' -- This is the row I am trying to filter out. It does NOT get filtered out.
NULL -- This row does get filtered out.

根据this post，使用-〉〉和JSON_UNQUOTE & JSON_EXTRACT与IS NOT NULL比较应该是有效的。
老实说，这可能是IS语句和JSON列类型的一个bug。已经有一些奇怪的行为，它与JSON文档而不是JSON文档的值进行比较。
不管怎样，有没有什么方法可以做到这一点？或者我一直在尝试的方法被证实是正确的，而这只是一个bug？