How to Join the subquery using JSON document within MySQL?

Question

The Mysql version is 8.0.18-commercial. The primary key of the table is id. I have written the following query which displays hostname and details columns

select hostname, details from table t1;

hostname: abc123
details:
[
  {
    "Msg": "Job Running",
    "currentTask": "IN_PROGRESS",
    "activityDate": "2020-07-20 16:25:15"
  },
  {
    "Msg": "Job failed",
    "currentTask": "IN_PROGRESS",
    "activityDate": "2020-07-20 16:35:24"
  }
]

I want the Msg value only from the element having most recent activityDate

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

hostname        Msg
abc123          Job failed

I have written following query and it is running successfully but not displaying anything at all. Morever, it is taking 17secs to execute.

select hostname,
(select Msg
from (
    select x.*, row_number() over(partition by t.id order by x.activityDate) rn
    from table1 t
    cross join json_table(
        t.audits,
        '$[*]' columns(
            Msg varchar(50) path '$.Msg',
            activityDate datetime path '$.activityDate'
        )
    ) x
) t
where rn = 1) AS Msg
from table1;

user_5841306 · Answer

You need to fix the JSON's format by removing the commas at the end of the lines starting with "activityDate" keys
A conversion function such as STR_TO_DATE() should be applied to the derived activityDate columns in order to get date ordered(not characterwise) results.
A subquery is not needed through putting ROW_NUMBER() analytic function next to the ORDER BY Clause( with descending order ), and adding a LIMIT 1 Clause at 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

user_11822079 · Answer

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 t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.hostname = t1.hostname AND t2.id > t1.id) ;

How to Join the subquery using JSON document within MySQL?

2 Answers