Search code examples
mysqljsoncastingnull

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

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

Solution

  • 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 |
+------------------------------------------+