How to fetch a value from the last element inside MySQL JSON document?

Question

I am using Mysql version 8.0.18-commercial

My MySQL query is returning JSON Document for one of the column values.

ServerName      Status
abc.com         JSON Document(as shown below)

The Status column is similar to as below:

{
  "STEP1": {
    "state": "STARTED",
    "startTime": "2020-08-05T04:40:45.021Z"
  },
  ....
  ....
  "STEP4": {
    "state": "ENDED",
    "startTime": "2020-08-05T05:08:36.286Z"
  }
}

Desired Output:

ServerName      Status
abc.com         ENDED

I want to find the last STEP in my JSON Document and then get the state value for it.

I have written the following query but it is not displaying last state value:

SELECT ServerName,
  (SELECT j.state
   FROM table t1
   CROSS JOIN json_table(Status, '$[*]' columns (state varchar(1000) PATH '$.state', startTime varchar(100) PATH '$.startTime')) j
   WHERE t1.id = t.id
   ORDER BY row_number() OVER (
                               ORDER BY j.startTime) DESC
   LIMIT 1) AS Status
FROM table AS t 

Answer 1

json_table() does not do what you think here: it is meant to operate on a JSON array, while your column contains a JSON object.

An alternative approach is to use json_table() with json_keys() to extract the object keys as rows: you can then extract the corresponding values, rank rows having the same servername, and keep the top row per group only:

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

Demo on DB Fiddle:

servername | state | starttime               
:--------- | :---- | :-----------------------
abc.com    | ENDED | 2020-08-05T05:08:36.286Z