By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (servername varchar(50), status json);
insert into mytable values ('abc.com',
'{
"STEP1": {
"state": "STARTED",
"startTime": "2020-08-05T04:40:45.021Z"
},
"STEP4": {
"state": "ENDED",
"startTime": "2020-08-05T05:08:36.286Z"
}
}');
select servername, state, starttime
from (
select
t.servername,
json_unquote(json_extract(t.status, concat('$.', j.k, '.startTime'))) starttime,
json_unquote(json_extract(t.status, concat('$.', j.k, '.state'))) state,
row_number() over(
partition by t.servername
order by json_unquote(json_extract(t.status, concat('$.', j.k, '.startTime'))) desc
) rn
from mytable t
cross join json_table(
json_keys(t.status),
'$[*]' columns (k varchar(50) path '$')
) j
) t
where rn = 1
| servername | state | starttime |
|---|---|---|
| abc.com | ENDED | 2020-08-05T05:08:36.286Z |