How to convert a MySQL 5.7 JSON NULL to native MySQL NULL?

Unfortunately CAST('{}' AS JSON) will not work is this case,but NULLIF works:

1. Full methods:

   SELECT NULLIF(JSON_UNQUOTE(JSON_EXTRACT('{"hello": null}', '$.hello')), 'null') IS NULL;

2. Shorted:

   SELECT NULLIF(helloColumn ->> '$.hello', 'null') IS NULL IS NULL;

Assume you have a table called 'mytable' that contains a json column called 'data'. I usually have to use a construct like this:

SELECT   CASE WHEN data->>'$.myfield' = 'null' THEN NULL ELSE data->>'$.myfield' END myfieldFROM mytable

Or create a virtual field as follows:

ALTER TABLE mytable ADD myfield VARCHAR(200) GENERATED ALWAYS AS (  CASE WHEN data->>'$.myfield' = 'null' THEN NULL ELSE data->>'$.myfield' END) VIRTUAL;

Looks official discussion is under progress but not much active.

Here is one more jury rigging:

mysql> SELECT CAST('null' AS JSON), JSON_TYPE(CAST('null' AS JSON)) = 'NULL';+----------------------+------------------------------------------+| CAST('null' AS JSON) | JSON_TYPE(CAST('null' AS JSON)) = 'NULL' |+----------------------+------------------------------------------+| null                 |                                        1 |+----------------------+------------------------------------------+1 row in set (0.00 sec)mysql> select @@version;+---------------+| @@version     |+---------------+| 5.7.21-20-log |+---------------+1 row in set (0.00 sec)