Elena Stepanova added a comment - 2019-06-03 15:23 That's because JSON null is not the NULL. That's how we can see it, for example: MySQL [test]> select json_extract(json_object( 'a' , NULL ), '$.a' ) is null ; + ---------------------------------------------------+ | json_extract(json_object( 'a' , NULL ), '$.a' ) is null | + ---------------------------------------------------+ | 0 | + ---------------------------------------------------+ 1 row in set (0.001 sec) Same with MySQL 5.7. I'll leave it to holyfoot to comment whether it's supposed to be so or not.

Kacper Cioch (Inactive) added a comment - 2019-11-28 08:55 - edited Hey. I am also looking for answer to this question. Please let us know, whether this behaves as designed or is it a bug. I can provide following example: MariaDB [test]> SELECT json_value('{"a": null}','$.a'); +---------------------------------+ | json_value('{"a": null}','$.a') | +---------------------------------+ | null | +---------------------------------+ 1 row in set (0.001 sec)   MariaDB [test]> SELECT json_extract('{"a": null}','$.a'); +-----------------------------------+ | json_extract('{"a": null}','$.a') | +-----------------------------------+ | null | +-----------------------------------+ 1 row in set (0.001 sec) It returns string 'null' instead of value NULL. I was expecting MariaDB [test]> SELECT json_value('{"a": null}','$.a'); +---------------------------------+ | json_value('{"a": null}','$.a') | +---------------------------------+ | NULL | +---------------------------------+ 1 row in set (0.001 sec) tested on 10.4.10 I think it is a bug because of this (different jsdoc, same result) MariaDB [test]> SELECT json_value('{"a": "null"}','$.a') = json_value('{"a": null}','$.a'); +---------------------------------------------------------------------+ | json_value('{"a": "null"}','$.a') = json_value('{"a": null}','$.a') | +---------------------------------------------------------------------+ | 1 | +---------------------------------------------------------------------+ 1 row in set (0.001 sec)

Jan Venekamp added a comment - 2020-01-06 00:44 I think not many people would expect that null equals to the string 'null', so I second Kacper Cioch that this should be considered as a bug. This behavior bit me in an other way. Inspired by this article https://mariadb.com/resources/blog/json-with-mariadb-10-2/ I started using virtual columns based on JSON values. Here is an example of how this behaves rather differently than expected. CREATE TABLE "opt_ref" ( "id" INT UNSIGNED PRIMARY KEY ) ENGINE = "InnoDB" ;   CREATE TABLE "foo" ( "data" JSON NULL , "opt_ref_id" INT UNSIGNED AS (JSON_VALUE( "data" , '$.opt_ref_id' )) PERSISTENT, FOREIGN KEY ( "opt_ref_id" ) REFERENCES "opt_ref" ( "id" ) ) ENGINE = "InnoDB" ;   INSERT INTO "foo" ( "data" ) VALUES ( '{"opt_ref_id":null}' ); SELECT * FROM "foo" ; Expected data opt_ref_id {"opt_ref_id":null} NULL Actual Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails ("test"."foo", CONSTRAINT "foo_ibfk_1" FOREIGN KEY ("opt_ref_id") REFERENCES "opt_ref" ("id")) Workaround For other people stumbling upon this, here is a workaround: CREATE TABLE "foo" ( "data" JSON NULL , "opt_ref_id" INT UNSIGNED AS ( IF (JSON_TYPE(JSON_EXTRACT( "data" , '$.opt_ref_id' )) = 'NULL' , NULL , JSON_VALUE( "data" , '$.opt_ref_id' ))) PERSISTENT, FOREIGN KEY ( "opt_ref_id" ) REFERENCES "opt_ref" ( "id" ) ) ENGINE = "InnoDB" ;

Kolbe Kegel added a comment - 2020-02-18 18:56 Maybe what's needed is something like a JSON_CAST function that combines JSON_TYPE/JSON_EXTRACT/JSON_VALUE functionality to return a json path cast to the most appropriate MariaDB type.

Timucin Bahsi added a comment - 2021-07-13 19:09 - edited Let's explore this a little: select json_value( '{"a":null}' , '$.a' ), json_value( '{\' a\':null}' , '$.a' ), json_value( '{"a":NULL}' , '$.a' ), json_value( '{"a":nullrandomletters}' , '$.a' ), json_value( '{"a":randomletters}' , '$.a' ), null ; Output: json_value(' {"a":null} ', '$.a') json_value(' {"a":NULL} ', '$.a') json_value(' {"a":nullrandomletters} ', '$.a') json_value(' {"a":randomletters} ', '$.a') NULL null NULL null NULL NULL Now, first we see that it returns a string with "null" in it. Ok, maybe it doesn't recognize null in json, but it does when we use "NULL" in capitals. Then we see the weirdest thing, if the expression starts with the leters "null", it ignores the rest. This is very different than treating it as a string. Then we actually see it's not how it treats strings. If you don't use quotes around strings it's just null. This doesn't seem like intended at all. It doesn't see it as null, it doesn't see it as a string either. There's certainly a special case for this specific sequence of characters and it doesn't any sense that i can think of. Note: tested under MariaDB [10.3.17-MariaDB-1:10.3.17+maria~bionic]

Assen Totin (Inactive) added a comment - 2021-12-02 20:01 Clearly a bug to me. JSON makes a very visible difference between "null" as a string and null as a value. See MDEV-27151 .