title: How to Join the subquery using JSON document within MySQL?
body: <p>The Mysql version is <code>8.0.18-commercial</code>. The primary key of the table is <code>id</code>.
  I have written the following query which displays <code>hostname</code> and <code>details</code> columns</p>
  <pre><code>select hostname, details from table t1;
  
  hostname: abc123
  details:
  [
    {
      &quot;Msg&quot;: &quot;Job Running&quot;,
      &quot;currentTask&quot;: &quot;IN_PROGRESS&quot;,
      &quot;activityDate&quot;: &quot;2020-07-20 16:25:15&quot;
    },
    {
      &quot;Msg&quot;: &quot;Job failed&quot;,
      &quot;currentTask&quot;: &quot;IN_PROGRESS&quot;,
      &quot;activityDate&quot;: &quot;2020-07-20 16:35:24&quot;
    }
  ]
  </code></pre>
  <p>I want the <code>Msg</code> value only from the element having most recent <code>activityDate</code></p>
  <p>My desired output is displaying <code>hostname</code> alongwith <code>Msg</code> of the element with <code>latest date</code> :</p>
  <pre><code>hostname        Msg
  abc123          Job failed
  </code></pre>
  <p>I have written following query and it is running successfully but not displaying anything at all. Morever, it is taking <code>17secs</code> to execute.</p>
  <pre><code>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;
  </code></pre>