How to Join the subquery using JSON document within MySQL?

• You need to fix the JSON's format by removing the commas at the endof the lines starting with "activityDate" keys
• A conversion function such as STR_TO_DATE() should be applied tothe derived activityDate columns in order to get date ordered(notcharacterwise) results.
• A subquery is not needed through putting ROW_NUMBER() analyticfunction next to the ORDER BY Clause( with descending order ), and adding a LIMIT 1 Clauseat the end of the query

So, you can rewrite the query as

SELECT t1.hostname,       j.Msg  FROM t1 CROSS JOIN     JSON_TABLE(details, '$[*]'        COLUMNS (                Msg VARCHAR(100)  PATH '$.Msg',                activityDate VARCHAR(100)  PATH '$.activityDate'                                       )     ) j  ORDER BY ROW_NUMBER()           OVER ( -- PARTITION BY id                 ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC)     LIMIT 1   

Demo

Update :

For the case of having several id values, you may consider using the ROW_NUMBER() function within a subquery and filter out the values returning equal to 1 in the main query :

SELECT id, Msg  FROM  (   SELECT t1.*, j.Msg,          ROW_NUMBER()           OVER (PARTITION BY id                 ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC) AS rn        FROM t1    CROSS JOIN          JSON_TABLE(details, '$[*]'           COLUMNS (                       Msg VARCHAR(100)  PATH '$.Msg',                   activityDate VARCHAR(100)  PATH '$.activityDate'                                          )     ) j    ) q WHERE rn= 1

Demo

One another method uses ROW_NUMBER() function together with LIMIT clause contains Correlated Subquery, and works for records with multiple id values :

SELECT t.id,  ( SELECT j.Msg     FROM t1    CROSS JOIN        JSON_TABLE(details, '$[*]'         COLUMNS (                Msg VARCHAR(100)  PATH '$.Msg',                activityDate VARCHAR(100)  PATH '$.activityDate'                                        )        ) j     WHERE t1.id = t.id       ORDER BY ROW_NUMBER()               OVER (ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC)         LIMIT 1 ) AS Msg  FROM t1 AS t

Demo

Maybe I'm old school, but the date field should be stored as a separate field either in additional to the JSON, to allow for easy queries.

Is the ID auto increment, and is the data inserted in timestamp order? If yes, then you can run a query like this to give you the last row for each hostname:

SELECT id, hostname, details FROM table t1WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.hostname = t1.hostname AND t2.id > t1.id) ;