N1QL：从阵列中查找最新状态 [英] N1QL : Find latest status from an array

问题描述

我有一个类型为'User'的文档，即-

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"
}

我需要用户的信息以及基于特定日期（或日期范围）的最新状态详细信息。
我可以使用子查询和Unnest子句来实现。

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.

推荐答案

MAX，MIN参数允许使用数组。数组的第0个元素可以是字段需要集合，第1个元素是您要携带的元素。
使用此技术，您可以为MIN / MAX投影非聚集字段，如下所示。

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];

在4.6.3+中，您也可以不使用UNNEST尝试使用子查询表达式 https://developer.couchbase.com/documentation/server/current/ n1ql / n1ql-language-reference / subqueries.html 。数组索引查询将更快。

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;

这篇关于N1QL：从阵列中查找最新状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！