The solution is to ommit the property which you want nulled.
After searching for a while I found this bug report from over a year ago that explains how a json string that contains the value NULL is actually a value and not a null.
{"item":1,"quantity":4}
--> item = 1, quantity = 4
{"item":1,"quantity":null}
--> item = 1, quantity = 'null' ( Some kind of unfriendly json null )
{"item":1}
--> item = 1, quantity = NULL ( Your good old sql null ! )
After seeing this, I thought "there's gotta be a way to cast this unfriendly json null into a nice null". I tried many things which worked, but none of them were insertable into a temporary table.
This was my final attempt which should work, based on what I stated above. I manage to detect if the value is null using an in-line IF
statement, then I try to cast an actual SQL NULL
into a SIGNED
int. I demonstrate how you can select the values but that it's impossible to insert them for some reason.
The code itself :
DROP TABLE IF EXISTS json_temp_table;
CREATE TEMPORARY TABLE json_temp_table (
item_id int NOT NULL PRIMARY KEY,
model_number varchar(100),
quantity int NULL
)
ENGINE = INNODB;
INSERT INTO json_temp_table
SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
SELECT
json_tb.item_id,
json_tb.model_number,
json_tb.quantity,
if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]"
COLUMNS
(
item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number",
quantity int PATH "$.quantity"
)
) json_tb) x;
Executed on the CLI mysql client
mysql> DROP TABLE IF EXISTS json_temp_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TEMPORARY TABLE json_temp_table (
-> item_id int NOT NULL PRIMARY KEY,
-> model_number varchar(100),
-> quantity int NULL
-> )
-> ENGINE = INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
-> SELECT
-> json_tb.item_id,
-> json_tb.model_number,
-> json_tb.quantity,
-> if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
-> FROM JSON_TABLE
-> (
-> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
-> , "$[*]"
-> COLUMNS
-> (
-> item_id int PATH "$.item_id",
-> model_number varchar(100) PATH "$.model_number",
-> quantity int PATH "$.quantity"
-> )
-> ) json_tb) x;
+---------+--------------+----------+
| item_id | model_number | quantity |
+---------+--------------+----------+
| 1 | MFJA53 | 4 |
| 2 | HSRHJN5 | NULL |
| 3 | FAFAF1 | 345 |
+---------+--------------+----------+
3 rows in set, 1 warning (0.00 sec)
mysql> INSERT INTO json_temp_table
-> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
-> SELECT
-> json_tb.item_id,
-> json_tb.model_number,
-> json_tb.quantity,
-> if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
-> FROM JSON_TABLE
-> (
-> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
-> , "$[*]"
-> COLUMNS
-> (
-> item_id int PATH "$.item_id",
-> model_number varchar(100) PATH "$.model_number",
-> quantity int PATH "$.quantity"
-> )
-> ) json_tb) x;
ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column quantity at row 1
mysql>