Mysql temp table insert from json can't handle null values

Question

The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table.

I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug.

When a null value is inserted into an INT NULL column, the following error is thrown : Invalid JSON value for CAST to INTEGER from column quantity at row 1

Here is the offending code

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
SELECT
  json_tb.item_id,
  json_tb.model_number,
  json_tb.quantity
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;
SELECT
  *
FROM json_temp_table

Is there a way to make json-generated tables insert nulls into temp tables ?

The weird part is the select statement works fine without the insert. Also, temp tables by themselves can definitely handle null int values. I don't understand why those two don't mix.

Answer 1

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>

Answer 2

I believe this one of the ways

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
  json_tb.item_id,
  json_tb.model_number,
  CAST(
        CASE true 
            WHEN json_tb.quantity = 'null'  -- handle string null
            THEN NULL 
            ELSE json_tb.quantity 
            END as signed) as quantity
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 varchar(100) PATH "$.quantity" -- changed to varchar, converting to signed/unsigned above
)
) json_tb;