Get the latest object from Array if certain criteria is meet

N1QL
1

Here is what i am trying to achive, i have a doc which stores a list of images in the image Array and i get those shortly after the record is created. After i upsert the Images Array with Image info, i also create a entry in the History Array which stores when the update happened and if it was an Initial load or update

History: [
    {
      "DateTime": "2022-03-20 11:14:17",
      "Action": "ImageInitialLoad",
      "Status": ""
    },
   {
      "DateTime": "2022-03-21 20:10:20",
      "Action": "ImageRefreshLoad",
      "Status": ""
    }]

So what i need is a way to get the newest / latest entry in the History array where Action is either
ImageInitialLoad or ImageRefreshLoad and compare it to the Record.Photos.PhotosChangeTimestamp
and its true if the Record.Photos.PhotosChangeTimestamp is > then the latest date with the above creteria in History Array

How can i achieve that

2

Use as sub query like this

SELECT lh.*
FROM default AS d
LET lh = (SELECT  RAW MAX([h.DateTime, h])[1] 
                 FROM d.History AS h  
                 WHERE h.Action IN ["ImageInitialLoad", "ImageRefreshLoad"])[0]
 WHERE d.Record.Photos.PhotosChangeTimestamp  > lh.DateTime;

OR

SELECT lh.*
FROM default AS d
LET lh = (SELECT  h.*
               FROM d.History AS h  
               WHERE h.Action IN ["ImageInitialLoad", "ImageRefreshLoad"]
               ORDER BY h.DateTime DESC 
               LIMIT 1)[0]
 WHERE d.Record.Photos.PhotosChangeTimestamp  > lh.DateTime;