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.
Best Answer
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 actualSQL NULL
into aSIGNED
int. I demonstrate how you can select the values but that it's impossible to insert them for some reason.The code itself :
Executed on the CLI mysql client