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

Question

This code:

SELECT JSON_EXTRACT('{"hello": null}', '$.hello')

Returns null.

However, this is not the MySQL native NULL. For example, this:

SELECT COALESCE(JSON_EXTRACT('{"hello": null}', '$.hello'), 'world')

also yields null.

How do I convert this JSON null to MySQL native NULL?

I suppose that I could use IF with some comparisons but it doesn't seem like the proper way to do it...

Answer 1

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;

Answer 2

This question is about MySQL 5.7, but I would like to add the solution for MySQL 8.0.

With MySQL 8.0.21 we have now the possibility to use the JSON_VALUE function (part of SQL Standard) to extract and optionally convert the value to a (MySQL) type.

mysql> SET @doc = '{"a": null}';

mysql> SELECT JSON_VALUE(@doc, '$.a' RETURNING SIGNED);
+------------------------------------------+
| JSON_VALUE(@doc, '$.a' RETURNING SIGNED) |
+------------------------------------------+
|                                     NULL |
+------------------------------------------+

mysql> SET @doc = '{"a": 2}';

mysql> SELECT JSON_VALUE(@doc, '$.a' RETURNING SIGNED);
+------------------------------------------+
| JSON_VALUE(@doc, '$.a' RETURNING SIGNED) |
+------------------------------------------+
|                                        2 |
+------------------------------------------+

Answer 3

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

Answer 4

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)