Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE `change` ( changeData VARCHAR(200) ); > > > <pre> > ✓ > </pre> <!-- --> > 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"}') > > > <pre> > ✓ > </pre> <!-- --> > 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` > > > <pre> > value | action > :---- | :-------------- > 0 | 07:28:13.000000 > 1 | N > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dbb21f797419cea13682e313af785f6e)*
back to fiddle