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.

score 1 · Accepted Answer · answered Nov 24 '17 at 17:27

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;

Hi @vsr, Thanks a lot for your response. the Second query is exactly what I was looking for. Can you explain how the 'Let' clause is working here please? — Krishan Jangid, Nov 28 '17 at 13:11
If LET variable is used inside WHERE evaluated after FROM clause else AFTER WHERE clause. It is easy to read avoid execution of expression multiple times. Also LET expression FROM U1.Statuses AS S1 is from clause expression and using correlated parent document. It is taking array from parent document and treat it as another source apply complete SQL syntax. — vsr, Dec 03 '17 at 01:17

N1QL : Find latest status from an array

1 Answers1

Linked