Como verificar a propriedade da coluna JSON do MySQL?

Question

Estou trabalhando com MySQL 8.0.21. Eu preciso escrever uma consulta que funcione com o tipo de coluna JSON. Alguns dos dados dentro dos documentos JSON têm valores nulos e quero filtrar esses valores nulos.

Exemplos de linhas possíveis, a maioria das propriedades no documento JSON foram removidas para simplificar:

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

Aqui está o que tentei:

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

Algumas coisas interessantes que notei ...

Comparar outros valores funcionou. Por exemplo...

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

Outra coisa interessante que notei, que foi mencionada nos comentários de alguns dos exemplos acima, foi um comportamento estranho para as verificações de nulos. Se jsonColumn fosse nulo, as verificações de nulo filtrariam o registro mesmo sabendo que eu estava acessando jsonColumn -> '$. Value'.

Não tenho certeza se isso está claro, então deixe-me elaborar um pouco ...

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

De acordo com esta postagem , usar - >> e JSON_UNQUOTE & JSON_EXTRACT com comparações IS NOT NULL deveria ter funcionado. Presumo que funcionou naquela época.

Sinceramente, sentir que isso pode ser um bug com a instrução IS e o tipo de coluna JSON. Já existe um comportamento estranho em que é comparado com o documento JSON ao invés dos valores do documento JSON.

Independentemente disso, existe alguma maneira de fazer isso? Ou as maneiras que tenho tentado estão confirmadas como corretas e isso é apenas um bug?

Answer 1

Seguindo o comentário de Barmar ...

Aparentemente, isso mudou em algum momento antes de 8.0.13. forums.mysql.com/read.php?176,670072,670072

Uma solução alternativa na postagem do fórum parece usar JSON_TYPE. Parece uma solução alternativa terrível 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;

A postagem do fórum diz (em relação ao código postado antes da solução alternativa) ...

C2 é efetivamente definido como NULL, mas C1 é retornado como a string 'nula' de 4 caracteres.

Então comecei a brincar com comparações de strings ...

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