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
7b22746573745f6474223a2022323031392d30332d32382031353a34333a3539222c2022746573745f656e756d223a2022666f6f227d
foo
0
2019-03-28 15:43:59
0

2
7b22746573745f6474223a2022323031392d30332d32382031353a34343a3135222c2022746573745f656e756d223a206e756c6c7d
null
0
2019-03-28 15:44:15
0

3
7b22746573745f6474223a2022323031392d30332d32382031353a34343a3135222c2022746573745f656e756d223a20226e756c6c227d
null
0
2019-03-28 15:44:15
0

4
7b22746573745f6474223a206e756c6c2c2022746573745f656e756d223a2022626172227d
bar
0
null
1

id	meta	test_enum	test_dt	test_dt_null
1	7b22746573745f6474223a2022323031392d30332d32382031353a34333a3539222c2022746573745f656e756d223a2022666f6f227d	foo	2019-03-28 15:43:59	0
2	7b22746573745f6474223a2022323031392d30332d32382031353a34343a3135222c2022746573745f656e756d223a206e756c6c7d	null	2019-03-28 15:44:15	0
3	7b22746573745f6474223a2022323031392d30332d32382031353a34343a3135222c2022746573745f656e756d223a20226e756c6c227d	null	2019-03-28 15:44:15	0
4	7b22746573745f6474223a206e756c6c2c2022746573745f656e756d223a2022626172227d	bar	null	1