Virtual column extracted from a json in MySql does not get NULL

0

I have a virtual column named '_cpf' which is extracted from the '_document' column in json. Here is the following query:

CREATE TABLE 'user' (
 '_document' json DEFAULT NULL,
 '_id' varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract('_document','$.id'))) STORED NOT NULL,
 '_cpf' varchar(11) GENERATED ALWAYS AS (json_unquote(json_extract('_document','$.cpf'))) VIRTUAL,
 '_externalId' int(11) GENERATED ALWAYS AS (json_unquote(json_extract('_document','$.externalId'))) VIRTUAL NOT NULL,
 '_created_at' datetime NOT NULL,
 '_updated_at' datetime NOT NULL,
 '_status_id' int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY ('_id'),
 UNIQUE KEY ('_empresaId', '_cpf'),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The problem is when I insert a line with cpf equal to null, the virtual column saves as varchar and not NULL, as it should. That is, if I do a 'select * from user where _cpf is null' it does not work.

There is also the problem in inserting another line with _cpf null and with the same value in the _Identium column, resulting in the error of duplicate values of the UNIQUE KEY command.

    
asked by anonymous 05.06.2018 / 23:33

1 answer

0

The solution was to include in the create table query the following command

GENERATED ALWAYS AS (CASE WHEN _document->>'$.cpf' = 'null' THEN NULL ELSE _document->>'$.cpf' END) STORED,
    
06.06.2018 / 00:48