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

Euterpe

what is generating the JSON you are parsing with this? Is it possible for it to send a NULL value instead of null? – Priscella

@MarshallTigerus I don't think MySQL allows that. AFAIK only JSON values are allowed. Consider that this issue also manifests in this snippet: SELECT COALESCE(JSON_EXTRACT(JSON_OBJECT("hello", NULL), '$.hello'), 'world') – Euterpe

and why the down vote..? – Euterpe

Are you trying to filter on JSON null? – Ducharme

Answer 1

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

Rotherham

Answer 2

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;

Anibalanica

This should be marked the best answer (not all of us can get all the way to version 8.0.21). – Celik

This has a potential flaw, as I've described here. – Immix

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;

Coraliecoraline

The null in JSON doesn't equal null string in MySQL, at least for my 5.7.22 – Kneel

Answer 4

Intro

Let's assume you have a table with a column j of type JSON and for that column you have a nested property that is nullable. Example:

{"hello": "world"}
{"hello": null}
...

In MySQL 5.7.13+, the easiest way to select the hello values would be with something like this:

SELECT j->>'$.hello' FROM ...

However, the result of that query would have null string (LONGTEXT) values instead of the special SQL NULL values:

world   -- that's a string
null    -- that's also a string
...

One way to verify that would be to run something like:

SELECT COALESCE(j->>'$.hello', 'YouWillNeverSeeMe:(((')

What might look even stranger is that if we used -> instead of ->>, we'd get:

"world"   -- that's a string (note the quotation marks)
null      -- that's also a string (with no quotation marks)
...

Solution

So how do we get the JSON null values as SQL NULL values?

MySQL 8.0.21+

In MySQL 8.0.21+ we can use the JSON_VALUE function and do something like this:

SELECT JSON_VALUE(j->'$.hello', '$') FROM ...

The result would be:

world   -- that's a string
[NULL]  -- that's NOT a string but the special SQL NULL value
...

Note that if we used ->> instead of -> here, we would fetch:

[NULL]  -- that's NOT a string but the special SQL NULL value
[NULL]  -- that's NOT a string but the special SQL NULL value
...

Older MySQL (incl. 5.7)

I am not aware of an ideal solution. The answer provided by sergolius looks simple and nice, but it has a potential flaw one should consider. Let's say Alice and Bob were blessed with a kid and they decided to name it Null (hey, I'm not here to judge!). And let's say Null ended up in our DB's JSON column. So instead of fetching:

Alice
[NULL]
Bob
Null
John
...

, we might end up with:

Alice
[NULL]
Bob
[NULL]   -- Oops! Where did Null go?
John
...

Immix

Answer 5

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)

Dividers

Answer 6

SELECT COALESCE(IF(JSON_TYPE(JSON_EXTRACT('{"hello":null}', '$.hello')) = 'NULL', NULL, JSON_EXTRACT('{"hello":null}', '$.hello')), 'world');

Illuminism

Some words of explanation usually are appreciated on stack overflow. – Dib

Answer 7

You can create a custom defined function TO_NULL by running:

-- To check if JSON value is null, if so return SQL null, and if not return original JSON value.
-- There are two nulls, SQL null and JSON null, they are not equal.
-- More information: https://stackoverflow.com/questions/37795654
DROP FUNCTION IF EXISTS TO_NULL;
CREATE FUNCTION TO_NULL(a JSON) RETURNS JSON
BEGIN
    RETURN IF(JSON_TYPE(a) = 'NULL', NULL, a);
END;

And than use it like so:

SELECT TO_NULL(JSON_EXTRACT('null', '$')) IS NULL;

Garble

Answer 8

Following approach works for both undefined and null values in mysql 8

SELECT data,
       CAST(data->>'$.a' AS JSON) <=> CAST('null' AS JSON) 'null',
       data->>'$.b' IS NULL 'NULL' FROM t;

data	null	NULL
{"a": null}	1	1

So to check if field has no value you can use in your query something like this

SELECT CAST(data->>'$.a' AS JSON) <=> CAST('null' AS JSON) OR data->>'$.a' IS NULL FROM t;

Tia