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

mysql json extract
mysql json search array of objects
mysql json path
mysql json_extract in where clause
json extract sql
json not empty mysql
drawback of json column mysql
mysql json default empty array

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

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;

MySQL 5.7 Reference Manual :: 11.5 The JSON Data Type, Converting between JSON and non-JSON values · Aggregation of JSON Values. As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 A JSON column cannot have a non- NULL default value. MySQL 8.0 also supports the JSON Merge Patch format defined in RFC 7396, using the JSON_MERGE_PATCH() function. See the description of this function, as well as Normalization, Merging, and Autowrapping of JSON Values, for examples and further information.

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;

MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type, Using the MySQL Yum Repository · MySQL NDB Cluster 8.0. version 8.0. 5.7 MySQL supports a native JSON data type defined by RFC 7159 that enables efficient Prior to MySQL 8.0.13, a JSON column cannot have a non- NULL default value. value AS JSON) (see Converting between JSON and non-JSON values). If present, it can be a JSON null value to close the read sequence, or a JSON hash. Within a hash argument to audit_log_read() , items are optional and control aspects of the read operation such as the position at which to begin reading or how many events to read.

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

MySQL Bugs: #85755: JSON containing null value is extracted as a , Version: 5.7.17, OS: CentOS (centos 7) to wrap things in a 'conversion' function that turns 'null' or 'false' into NULL and FALSE. If JSON null/boolean and MySQL NULL/BOOLEAN (lets not even talk about the absence NULL); Should not result in '{"a": null}' But the fact that native MySQL types TRUE,  Using only the native JSON fuctions (no PHP, etc) in MySQL version 5.7.12 (section 13.16 in the manual) I am trying to write a query to generate a JSON document from relational tables that contains a sub object. Given the following example:

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)

Working with JSON in MySQL ― Scotch.io, MySQL version 5.7.8 introduces a JSON data type that allows you to accomplish that. UNSIGNED NOT NULL , `attributes` JSON NOT NULL , PRIMARY to be JSON which is the native data type now available in MySQL. MySQL NDB Cluster 7.5 (7.5.2 and later) supports JSON columns and MySQL JSON functions, including creation of an index on a column generated from a JSON column as a workaround for being unable to index a JSON column. A maximum of 3 JSON columns per NDB table is supported.

Working with JSON Data in MySQL 7, As of MySQL 5.7.8, MySQL includes a new JavaScript Object Whereas you could always store JSON data as a varchar or text, native support provides some distinct a JSON value stored in this binary format without having to convert it id int(10) NOT NULL AUTO_INCREMENT, widget json NOT NULL,  MySQL 5.7 Reference Manual / Functions and Operators / Type Conversion in Expression Evaluation 12.2 Type Conversion in Expression Evaluation When an operator is used with operands of different types, type conversion occurs to make the operands compatible.

Using JSON with MySQL 5.7–compatible Amazon Aurora, What's the big deal about JSON support in MySQL 5.7? change and also update the SQL statements to accommodate the change. having JSON as a native type in MySQL means that the database can CREATE TABLE brands ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  I am trying to change a MySQL column from varchar(9000) NULL to the new JSON data type in MySQL 5.7. The column holds valid JSON strings but some values are null. When I try the following: alter table log modify request json it fails with the following error: Invalid JSON text: "The document is empty."

Taking the new MySQL 5.7 JSON features for a test drive, MySQL 5.7 introduces both a new native JSON datatype, and a set of SQL TABLE features CHANGE feature feature LONGTEXT NOT NULL;. You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON); see Converting between JSON and non-JSON values, for more information. Two aggregate functions generating JSON values are available (MySQL 5.7.22 and later).

Comments
  • what is generating the JSON you are parsing with this? Is it possible for it to send a NULL value instead of null?
  • @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')
  • and why the down vote..?
  • Are you trying to filter on JSON null?
  • The null in JSON doesn't equal null string in MySQL, at least for my 5.7.22
  • Some words of explanation usually are appreciated on stack overflow.