clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601395 fiddles created (48004 in the last week).

CREATE TABLE `change` ( changeData VARCHAR(200) );
 hidden batch(es)

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"}')
 hidden batch(es)

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`
value action
0 07:28:13.000000
1 N
 hidden batch(es)