mysql - 如何空检查 MySQL JSON 列属性？

Question

我正在使用 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;

我注意到的另一件有趣的事情是 null 检查的一些奇怪行为，这在上面一些示例的评论中提到过。如果 jsonColumn 为 null，即使知道我正在访问 jsonColumn->'$.value'，null 检查也会过滤掉记录。

不确定这是否清楚，所以让我详细说明一下......

-- 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 文档的值进行比较时，已经存在奇怪的行为。

无论如何，有什么办法可以做到这一点？还是我一直在尝试的方法被确认为正确的方法而这只是一个错误？

Answer 1

根据 Barmar 的评论...

Apparently this changed sometime before 8.0.13. forums.mysql.com/read.php?176,670072,670072

论坛帖子中的解决方法似乎是使用 JSON_TYPE。看起来是一个糟糕的解决方法。

SET @doc = JSON_OBJECT('a', NULL);
SELECT JSON_UNQUOTE(IF(JSON_TYPE(JSON_EXTRACT(@doc,'$.a')) = 'NULL', NULL, JSON_EXTRACT(@doc,'$.a'))) as C1,
JSON_UNQUOTE(JSON_EXTRACT(@doc,'$.b')) as C2;

论坛帖子说(关于解决方法之前发布的代码)...

C2 is effectively set as NULL, but C1 is returned as the 4 char 'null' string.

所以我开始搞乱字符串比较...

// This filtered out NULL jsonColumn but, NOT NULL jsonColumn->'$.value'
SELECT *
FROM <table>
WHERE jsonColumn->'$.value' != 'null';

jsonColumn
'{"value":96.0}'
'{"value":"null"}' -- Not originally apart of my dataset but, this does get filtered out. Which is very interesting...
'{"value":null}' -- This does NOT get filtered out.
NULL -- This row does get filtered out.

// This filtered out both NULL jsonColumn AND NULL jsonColumn->'$.value'
SELECT *
FROM <table>
WHERE jsonColumn->>'$.value' != 'null';

jsonColumn
'{"value":96.0}'
'{"value":"null"}' -- Not originally apart of my dataset but, this does get filtered out.
'{"value":null}' -- This does get filtered out.
NULL -- This row does get filtered out.