CREATE TABLE `change` ( changeData VARCHAR(200) );

INSERT INTO `change` VALUES ('{"value": 0, "action": "UPDATED", "effectiveFrom": "2019-01-31T07:28:13.000Z"}'), ('{"value": 1, "action": "DELETED", "effectiveFrom": "2019-01-31T07:27:13.000Z"}')

SELECT JSON_EXTRACT(changeData, '$.value') AS value, CASE WHEN JSON_EXTRACT(changeData, '$.action') = 'UPDATED' THEN TIME( REPLACE(REPLACE(REPLACE( JSON_EXTRACT(changeData, '$.effectiveFrom') ,'"',''),"Z",''),"T",' ') ) ELSE 'N' END AS action FROM `change`