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 stackoverflow.com/questions/39906435/… for how to convert a JSON array to rows. Then you can use stackoverflow.com/questions/7745609/… to get the row with the latest value. — Barmar
– Barmar, Commented Jul 20, 2020 at 22:02
Do you want the message of the last array element, or of the element with latest date? — GMB
– GMB, Commented Jul 20, 2020 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
– Bill Karwin, Commented Jul 20, 2020 at 22:29
I am migrating data from mongo collection and it can be migrated in the form of JSON documents — meallhour
– meallhour, Commented Jul 20, 2020 at 22:39

GMB · Accepted Answer · 2020-07-20 22:18:03Z

1

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

answered Jul 20, 2020 at 22:18

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related