Virtual column extracted from a json in Mysql does not receive NULL value

Question

I have a virtual column called '_Cpf' that is extracted from the column '_Document' in json. Below 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 make a 'select * from user Where _Cpf is null' it does not work.

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

Answer 1

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

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