Bagaimana cara null memeriksa properti kolom MySQL JSON?

Question

Saya bekerja dengan MySQL 8.0.21. Saya perlu menulis kueri yang berfungsi dengan jenis kolom JSON. Beberapa data di dalam dokumen JSON memiliki nilai null dan saya ingin memfilter nilai null ini.

Contoh baris yang memungkinkan, sebagian besar properti di dokumen JSON telah dihapus agar lebih sederhana:

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

Inilah yang saya coba:

-- 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;

Beberapa hal menarik yang saya perhatikan ...

Membandingkan nilai-nilai lain berhasil. Sebagai contoh...

-- 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;

Hal menarik lainnya yang saya perhatikan, yang disebutkan dalam komentar untuk beberapa contoh di atas, adalah beberapa perilaku aneh untuk pemeriksaan null. Jika jsonColumn adalah null, pemeriksaan null akan menyaring catatan bahkan tahu saya mengakses jsonColumn -> '$. Value'.

Tidak yakin apakah ini jelas, jadi izinkan saya menjelaskan sedikit ...

-- 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.

Menurut posting ini , menggunakan - >> dan JSON_UNQUOTE & JSON_EXTRACT dengan perbandingan IS NOT NULL seharusnya berhasil. Saya berasumsi itu berhasil saat itu.

Sejujurnya perasaan seperti ini mungkin merupakan bug dengan pernyataan IS dan tipe kolom JSON. Sudah ada perilaku aneh yang membandingkannya dengan dokumen JSON daripada nilai dokumen JSON.

Terlepas dari itu, apakah ada cara untuk mencapai ini? Atau apakah cara yang saya coba telah dikonfirmasi adalah cara yang benar dan ini hanya bug?

Answer 1

Mengikuti komentar Barmar ...

Rupanya ini berubah beberapa saat sebelum 8.0.13. forums.mysql.com/read.php?176,670072,670072

Solusi di postingan forum tampaknya menggunakan JSON_TYPE. Sepertinya solusi yang buruk 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;

Posting forum mengatakan (mengenai kode yang diposting sebelum solusi) ...

C2 secara efektif disetel sebagai NULL, tetapi C1 dikembalikan sebagai string 4 karakter 'null'.

Jadi saya mulai bermain-main dengan perbandingan 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.