从MongoDB中返回同一数组中的两个字段的文档

Question

鉴于这样的文档

{
  _id: 'abcd',
  userId: '12345',
  activities: [
    { status: 'login', timestamp: '10000001' },
    { status: 'logout', timestamp: '10000002' },
    { status: 'login', timestamp: '10000003' },
    { status: 'logout', timestamp: '10000004' },
  ]
}

我尝试创建一个流程，使得在两个时间戳之间记录有最新登录/注销活动的所有用户都会被返回。例如，如果两个时间戳值位于10000002和10000003之间，则预期文档应为：

{
  _id: 'abcd',
  userId: '12345',
  login: '10000003',
  logout: '10000002'
}

如果两个时间戳值介于-1和10000001之间，则预期文档应为:

{
  _id: 'abcd',
  userId: '12345',
  login: '10000001',
  logout: null
}

等等。

我知道这与聚合有关，我需要使用$unwind等方法，但是我不确定其余部分，即如何评估同一文档数组中的两个字段。

- Yanick Rochon

Answer 1

您可以尝试以下聚合：

db.col.aggregate([
    { 
        $unwind: "$activities" 
    },
    {
        $match: {
            $and: [
                { "activities.timestamp": { $gte: "10000001" } },
                { "activities.timestamp": { $lte: "10000002" } }
            ]
        }
    },
    {
        $sort: {
            "activities.timestamp": -1
        }
    },
    {
        $group: {
            _id: "$_id",
            userId: { $first: "$userId" },
            activities: { $push: "$activities" }
        }
    },
    {
        $addFields: {
            login: { $arrayElemAt: [ { $filter: { input: "$activities", as: "a", cond: { $eq: [ "$$a.status", "login" ] } } } , 0 ] },
            logout: { $arrayElemAt: [ { $filter: { input: "$activities", as: "a", cond: { $eq: [ "$$a.status", "logout" ] } } } , 0 ] }
        }
    },
    {
        $project: {
            _id: 1,
            userId: 1,
            login: { $ifNull: [ "$login.timestamp", null ] },
            logout: { $ifNull: [ "$logout.timestamp", null ] }
        }
    }
])

我们需要使用 $unwind + $sort + $group 确保我们的活动按时间戳排序。在 $unwind 之后，您可以使用 $match 应用筛选条件。然后，您可以使用 $filter 和 $arrayElemAt 获取筛选数组的第一个（最新）值。在最后的 $project 中，您可以明确使用 $ifNull（否则如果没有值，JSON键将被跳过）。

- mickl

---

我在这个查询中遇到了问题；在同一字段上的 $gte 和 $lte 只返回集合中的一个文档。 - Yanick Rochon

@YanickRochon，你能把带有你数据的mongoplayground（https://mongoplayground.net/）发给我吗？ - mickl

我已经在mongoplayground上尝试了我的查询，并返回了预期的结果。然而，我使用的mongo驱动程序却没有返回相同的结果。我使用的版本是3.6.4，而mongoplaygroud使用的是4.0.3。 - Yanick Rochon

好的，我明白了问题；一些时间戳被存储为字符串，而另一些则被存储为ISODate对象。 - Yanick Rochon

Answer 2

您可以使用以下聚合方法

使用$lte和$gte替代$unwind，并与$fitler聚合一起使用。

db.collection.aggregate([
  { "$project": {
    "userId": 1,
    "login": {
      "$max": {
        "$filter": {
          "input": "$activities",
          "cond": {
            "$and": [
              { "$gte": ["$$this.timestamp", "10000001"] },
              { "$lte": ["$$this.timestamp", "10000004"] },
              { "$lte": ["$$this.status", "login"] }
            ]
          }
        }
      }
    },
    "logout": {
      "$max": {
        "$filter": {
          "input": "$activities",
          "cond": {
            "$and": [
              { "$gte": ["$$this.timestamp", "10000001"] },
              { "$lte": ["$$this.timestamp", "10000004"] },
              { "$lte": ["$$this.status", "logout"] }
            ]
          }
        }
      }
    }
  }}
])

- Ashh

---

在获取 $arrayElemAt 之前，您不需要对 activities 进行排序吗？因为如果时间戳范围很大，并且有多个“logout”活动，那么什么保证最新的活动将被返回呢？（对于“login”也是如此。） - Yanick Rochon

@YanickRochon 更新了我的回答。你可以使用$max运算符和时间戳来获取数组中最新的记录。现在，即使只有一个聚合阶段，也可以实现这一点。 - Ashh