MySQL JSON列のプロパティをnullチェックする方法は？
私はMySQL8.0.21を使用しています。JSON列タイプで機能するクエリを作成する必要があります。JSONドキュメント内の一部のデータにnull値があり、これらのnull値を除外したいと思います。
可能な行の例、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;
上記のいくつかの例のコメントで言及されている、私が気付いたもう1つの興味深い点は、ヌルチェックの奇妙な動作でした。jsonColumnがnullの場合、nullチェックは、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.
この投稿によると、->>とJSON_UNQUOTE＆JSON_EXTRACTをIS NOTNULLで使用すると比較が機能するはずです。当時はうまくいったと思います。
正直なところ、このように感じるのは、ISステートメントとJSON列タイプのバグである可能性があります。JSONドキュメントの値ではなく、JSONドキュメントと比較するという奇妙な動作がすでにあります。
とにかく、これを達成する方法はありますか？それとも、私が試した方法が正しい方法であることが確認されていますが、これは単なるバグですか？
回答
バーマーのコメントに続いて...
どうやらこれは8.0.13より前のいつか変更されました。forums.mysql.com/read.php?176,670072,670072
フォーラム投稿の回避策はJSON_TYPEを使用しているようです。ひどい回避策tbhのように見えます。
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は事実上NULLとして設定されますが、C1は4文字の「null」文字列として返されます。
だから私は文字列の比較をいじり始めました...
// 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.