N1QL : Find latest status from an array

Question

I have a document of type 'User' as-

{
"id":"User-1",
"Name": "Kevin",
"Gender":"M",
"Statuses":[
   {
     "Status":"ONLINE",
     "StatusChangedDate":"2017-11-01T17:12:00Z"
   },
   {
     "Status":"OFFLINE",
     "StatusChangedDate":"2017-11-02T13:24:00Z"
   },
   {
     "Status":"ONLINE",
     "StatusChangedDate":"2017-11-02T14:35:00Z"
   },
   {
     "Status":"OFFLINE",
     "StatusChangedDate":"2017-11-02T15:47:00Z"
   }.....
],

"type":"User"
}

I need user's information along with his latest status details based on a particular date (or date range). I am able to achieve this using subquery and Unnest clause.

Select U.Name, U.Gender, S.Status, S.StatusChangedDate
From (Select U1.id, max(U1.StatusChangedDate) as StatusChangedDate
      From UserInformation U1
      Unnest Statuses S1
      Where U1.type = 'User'
      And U1.StatusChangedDate between '2017-11-02T08:00:00Z' And '2017-11-02T11:00:00Z'
      And U1.Status = 'ONLINE'
      Group by U1.id 
      ) A
Join UserInformation U On Keys A.id
Unnest U.Statuses S
Where U.StatusChangedDate = A.StatusChangedDate;

But is there any other way of achieving this (like by using collection operators and array functions)?? If yes, please provide me a query or guide me through it. Thanks.

Answer 1

MAX, MIN argument allows array. 0th element of array can be field needs aggregate and 1st element is what you want to carry. Using this techinuqe you can project non aggregtae field for MIN/MAX like below.

SELECT U.Name, U.Gender, S.Status, S.StatusChangedDate
FROM UserInformation U1
UNNEST Statuses S1
WHERE U1.type = 'User'
AND S1.StatusChangedDate BETWEEN '2017-11-02T08:00:00Z' AND '2017-11-02T11:00:00Z'
AND S1.Status = 'ONLINE'
GROUP BY U1.id
LETTING S = MAX([S1.StatusChangedDate,S1])[1];

In 4.6.3+ you can also try this without UNNEST Using subquery expressions https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/subqueries.html . Array indexing query will be faster.

CREATE INDEX ix1 ON UserInformation(ARRAY v FOR v IN Statuses WHEN v.Status = 'ONLINE' END) WHERE type = "User";
SELECT U.Name, U.Gender, S.Status, S.StatusChangedDate
FROM UserInformation U1
LET S = (SELECT RAW MAX([S1.StatusChangedDate,S1])[1]
         FROM U1.Statuses AS S1
         WHERE S1.StatusChangedDate BETWEEN '2017-11-02T08:00:00Z' AND '2017-11-02T11:00:00Z' AND S1.Status = 'ONLINE')[0]
WHERE U1.type = 'User'
AND ANY v IN U1.Statuses SATISFIES
        v.StatusChangedDate BETWEEN '2017-11-02T08:00:00Z' AND '2017-11-02T11:00:00Z' AND v.Status = 'ONLINE' END;