How to get the latest value from the JSON document inside MySQL column?

Question

The Mysql version is 8.0.18-commercial I have written the following query which displays results from details column

select details from table t1;

Output:
[
  {
    "Msg": "Job Running",
    "Task": "IN_PROGRESS",
    "Date": "2020-07-20 16:25:15",
  },
  {
    "Msg": "Job failed",
    "Task": "IN_PROGRESS",
    "Date": "2020-07-20 16:35:24",
  }
]

I want the Msg value only from the last element which has latest Date

My desired output is displaying Msg of the element with latest date :

ColumnName      ColumnValue
Msg             Job failed

I have written following query but it is giving null output

select details->>"$.Msg" from table t1;

So https://stackoverflow.com/questions/39906435/convert-json-array-in-mysql-to-rows for how to convert a JSON array to rows. Then you can use https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 to get the row with the latest value. — Barmar, Jul 20 '20 at 22:02
Do you want the message of the last array element, or of the element with latest date? — GMB, Jul 20 '20 at 22:06
Out of curiosity, why did you store this as JSON? It would have been easier if you had stored this in a normal manner, with each array member on a row, and each object field in a normal column. In most cases, using JSON for structure data like this makes it _harder_ to query data in an SQL database. — Bill Karwin, Jul 20 '20 at 22:29
I am migrating data from mongo collection and it can be migrated in the form of JSON documents — meallhour, Jul 20 '20 at 22:39

score 1 · Accepted Answer · answered Jul 20 '20 at 22:18

If you are running MySQ. 8.0, you can use json_table() to unnest the json array to rows, then row_number() to keep the latest record per original row.

Assuming that the primary key of your table is id, you would phrase this as:

select msg, activity_date
from (
    select x.*, row_number() over(partition by t.id order by x.activity_date desc) rn
    from mytable t
    cross join json_table(
        t.details,
        '$[*]' columns(
            msg varchar(50) path '$.Msg',
            activity_date datetime path '$.activityDate'
        )
    ) x
) t
where rn = 1

Demo on DB Fiddle:

msg        | activity_date      
:--------- | :------------------
Job failed | 2020-07-20 16:35:24

How to get the latest value from the JSON document inside MySQL column?

1 Answers1