3

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.

3
  • 1
    What's that downvote for ? Am I doing something wrong here ?
    – A_V
    Oct 22, 2018 at 18:51
  • 1
    The question seems fine to me, have an upvote.
    – jynus
    Oct 22, 2018 at 19:38
  • 1
    Seems to have been reported 1 year ago, it was a known bug in mysql-5.7 bugs.mysql.com/bug.php?id=88033
    – A_V
    Oct 22, 2018 at 19:59

2 Answers 2

2

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.