MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `meta` json DEFAULT NULL,
  PRIMARY KEY (`id`)
)
INSERT INTO `test_table` VALUES
(null, '{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}'),
(null, '{"test_dt": "2019-03-28 15:44:15", "test_enum": null}'),
(null, '{"test_dt": "2019-03-28 15:44:15", "test_enum": "null"}'),
(null, '{"test_dt": null, "test_enum": "bar"}')
select *, 
       `meta`->>"$.test_enum" test_enum, 
       `meta`->>"$.test_enum" IS NULL test_enum_null, 
       CAST(`meta`->>"$.test_dt" AS DATETIME) test_dt,
       CAST(`meta`->>"$.test_dt" AS DATETIME) IS NULL test_dt_null
from test_table


id
meta
test_enum
test_enum_null
test_dt
test_dt_null


1
{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}
foo
0
2019-03-28 15:43:59
0

2
{"test_dt": "2019-03-28 15:44:15", "test_enum": null}
null
0
2019-03-28 15:44:15
0

3
{"test_dt": "2019-03-28 15:44:15", "test_enum": "null"}
null
0
2019-03-28 15:44:15
0

4
{"test_dt": null, "test_enum": "bar"}
bar
0
null
1


truncate test_table
INSERT INTO `test_table` VALUES
(null, JSON_OBJECT('test_dt', '2019-03-28 15:43:59', 'test_enum', 'foo' )),
(null, JSON_OBJECT('test_dt', '2019-03-28 15:44:15', 'test_enum', null  )),
(null, JSON_OBJECT('test_dt', '2019-03-28 15:44:15', 'test_enum', 'null')),
(null, JSON_OBJECT('test_dt',                  null, 'test_enum', 'bar' ))
select *, 
       `meta`->>"$.test_enum" test_enum, 
       `meta`->>"$.test_enum" IS NULL test_enum_null, 
       CAST(`meta`->>"$.test_dt" AS DATETIME) test_dt,
       CAST(`meta`->>"$.test_dt" AS DATETIME) IS NULL test_dt_null
from test_table


id
meta
test_enum
test_enum_null
test_dt
test_dt_null


1
{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}
foo
0
2019-03-28 15:43:59
0

2
{"test_dt": "2019-03-28 15:44:15", "test_enum": null}
null
0
2019-03-28 15:44:15
0

3
{"test_dt": "2019-03-28 15:44:15", "test_enum": "null"}
null
0
2019-03-28 15:44:15
0

4
{"test_dt": null, "test_enum": "bar"}
bar
0
null
1

id	meta	test_enum	test_dt	test_dt_null
1	{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}	foo	2019-03-28 15:43:59	0
2	{"test_dt": "2019-03-28 15:44:15", "test_enum": null}	null	2019-03-28 15:44:15	0
3	{"test_dt": "2019-03-28 15:44:15", "test_enum": "null"}	null	2019-03-28 15:44:15	0
4	{"test_dt": null, "test_enum": "bar"}	bar	null	1