How can i get an object in array stored in doc

I have for example a Doc where i store Userinfo like username and password etc. This doc also has 2 Arrays which i limit to a certain size in my code, one is called password history where i store a max of 5 previous passwords that are hashed and when user creates a new one it will be checked against list so he cant reuse. The other one is the login history where i keep track of all logins. Its an Array called login and the doc looks like the sample below. How can i get the
oldest or newest doc in login array back which success was false within the same query ?

{ "username" : "demo",
"name" : "Demo User",
"login_history" : [
 {
    "admin": true,
    "developer": true,
    "id": "user::8D6D24A5-D669-45DC-99AC-F257BDA133A4",
    "locked": false,
    "login_history": [
      {
        "clientIp": "192.168.1.34",
        "date": "2021-04-22T05:17:43.592Z",
        "success": true
      },
      {
        "clientIp": "192.168.1.34",
        "date": "2021-04-22T05:18:38.071Z",
        "success": false
      },
      {
        "clientIp": "192.168.1.34",
        "date": "2021-04-22T05:18:47.664Z",
        "success": true
      },
  {
        "clientIp": "192.168.1.34",
        "date": "2021-04-22T04:18:47.664Z",
        "success": false
      }}
}
SELECT  MIN([h.date, h])[1] AS oldest,  MAX([h.date, h])[1] AS newest
FROM default AS d
UNNEST d.login_history AS lh
UNNEST lh.login_history AS h
WHERE d.username =  "demo";

For some reason this does not work, If i run it it returns nothing, if i remove the UNNEST lh.login_history AS h and select lh i get some data.

SELECT MIN([lh.date, lh])[1] AS oldest,
       MAX([lh.date, lh])[1] AS newest
FROM Contacts a
UNNEST a.security.login_history AS lh
WHERE a._type = 'user'

So here a few things i am unclear about since this seems to be a group by kind of query i cant get other non grouped value, for example if i want the username and use a.username i get this. “msg”: “Expression (a.username) must depend only on group keys or aggregates.” So the question is to access a.username and some other stuff within this query.

Also since we use unnest if there is no data in array we dont get that record at all which does not work as i want all users and the dates.

SELECT a.username, MIN([lh.date, lh])[1] AS oldest,
       MAX([lh.date,  lh ])[1] AS newest
FROM Contacts a
LEFT UNNEST a.security.login_history AS lh
WHERE a._type = 'user'
GROUP BY a.username;

If you need another values project in MIN, MAX u can add them in second argument.
MIN([lh.date, {lh, a.name }])[1]

If each user has separate document

SELECT ...........
FROM Contacts a
LET login = (SELECT MIN([lh.date, lh])[1] AS oldest, MAX([lh.date, lh])[1] AS newest  FROM a.security.login_history AS lh  WHERE    .....)[0]
WHERE a._type = 'user'

Thanks, the last sample is the better one as i have a doc for each user and it makes it easy to return all main info as well as the history min and max is there s one. Below is the way i get now the min and max for success and failures. Is there any performance issue since it seems i am doing 3 selects ? Or is it one query and then it runs 2 sub query’s on the result ? Also i asume to get the min and max for success and failure i need to run 2 sub queries

SELECT META(a).id,
       a.username,
       a.name,
       loginSuccess,
       loginFailure
FROM Contacts a
LET loginSuccess = (
    SELECT MIN([lh.date, lh])[1] AS oldest,
           MAX([lh.date, lh])[1] AS newest
    FROM a.security.login_history AS lh
    WHERE lh.success = TRUE)[0],
loginFailure = (
    SELECT MIN([lh.date, lh])[1] AS oldest,
           MAX([lh.date, lh])[1] AS newest
    FROM a.security.login_history AS lh
    WHERE lh.success = FALSE)[0]
WHERE a._type = 'user'

Document fetch is done on main query. Other two iterating over Array.

If you like this format login is Array of 2 objects success (true, false)

SELECT META(a).id,
       a.username,
       a.name,
       login
FROM Contacts a
LET login = (
    SELECT lh.success, MIN([lh.date, lh])[1] AS oldest,
           MAX([lh.date, lh])[1] AS newest
    FROM a.security.login_history AS lh
   GROUP BY lh.success )
WHERE a._type = 'user'

Thanks that’s what i thought / hoped…