US1mnd9i

1DROP TABLE IF EXISTS json_temp_table;
2CREATE TEMPORARY TABLE json_temp_table (
3  item_id int NOT NULL PRIMARY KEY,
4  model_number varchar(100),
5  quantity int NULL
6)
7ENGINE = INNODB
8SELECT
9  json_tb.item_id,
10  json_tb.model_number,
11  json_tb.quantity
12FROM JSON_TABLE
13(
14'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
15, "$[*]"
16COLUMNS
17(
18item_id int PATH "$.item_id",
19model_number varchar(100) PATH "$.model_number",
20quantity int PATH "$.quantity"
21)
22) json_tb;
23SELECT
24  *
25FROM json_temp_table
26	
27DROP TABLE IF EXISTS json_temp_table;
28CREATE TEMPORARY TABLE json_temp_table (
29  item_id int NOT NULL PRIMARY KEY,
30  model_number varchar(100),
31  quantity int NULL
32)
33ENGINE = INNODB;
34
35INSERT INTO json_temp_table
36SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
37SELECT
38  json_tb.item_id,
39  json_tb.model_number,
40  json_tb.quantity,
41  if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
42FROM JSON_TABLE
43(
44'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
45, "$[*]"
46COLUMNS
47(
48item_id int PATH "$.item_id",
49model_number varchar(100) PATH "$.model_number",
50quantity int PATH "$.quantity"
51)
52) json_tb) x;
53	
54mysql> DROP TABLE IF EXISTS json_temp_table;
55Query OK, 0 rows affected, 1 warning (0.00 sec)
56
57mysql> CREATE TEMPORARY TABLE json_temp_table (
58    ->   item_id int NOT NULL PRIMARY KEY,
59    ->   model_number varchar(100),
60    ->   quantity int NULL
61    -> )
62    -> ENGINE = INNODB;
63Query OK, 0 rows affected (0.00 sec)
64
65mysql> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
66    -> SELECT
67    ->   json_tb.item_id,
68    ->   json_tb.model_number,
69    ->   json_tb.quantity,
70    ->   if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
71    -> FROM JSON_TABLE
72    -> (
73    -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
74    -> , "$[*]"
75    -> COLUMNS
76    -> (
77    -> item_id int PATH "$.item_id",
78    -> model_number varchar(100) PATH "$.model_number",
79    -> quantity int PATH "$.quantity"
80    -> )
81    -> ) json_tb) x;
82+---------+--------------+----------+
83| item_id | model_number | quantity |
84+---------+--------------+----------+
85|       1 | MFJA53       |        4 |
86|       2 | HSRHJN5      |     NULL |
87|       3 | FAFAF1       |      345 |
88+---------+--------------+----------+
893 rows in set, 1 warning (0.00 sec)
90
91mysql> INSERT INTO json_temp_table
92    -> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
93    -> SELECT
94    ->   json_tb.item_id,
95    ->   json_tb.model_number,
96    ->   json_tb.quantity,
97    ->   if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
98    -> FROM JSON_TABLE
99    -> (
100    -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
101    -> , "$[*]"
102    -> COLUMNS
103    -> (
104    -> item_id int PATH "$.item_id",
105    -> model_number varchar(100) PATH "$.model_number",
106    -> quantity int PATH "$.quantity"
107    -> )
108    -> ) json_tb) x;
109ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column quantity at row 1
110mysql>