MySQL 8.0

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

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"}')
 

Records: 2  Duplicates: 0  Warnings: 0


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