时间戳

首页 > 解决方案 > 在聚合器中使用基于子文档值的过滤器

mongodb - 在聚合器中使用基于子文档值的过滤器

问题描述

我想根据我通过用户 ID 检索到的子文档中的日期字段messages从文档中过滤掉子文档discussiondeletion

在 JavaScript 中，它会给出如下内容（只是为了澄清）

discussion.messages.filter(message => message.createdAt < discussion.deletions[userID].updatedAt)

这是我现在所拥有的：

Discussion.aggregate([
  {
    $match: {
      _id: mongoose.Types.ObjectId(discussionID),
      participants: userID
    }
  },
  {
    $project: {
      messages: {
        $slice: [{
          $filter: {
            input: '$messages',
            as: 'message',
            cond: {
              $and: // the filtering condition should be there
            }
          }
        }, limit]
      }
    }
  }
])

任何的想法 ？

这是详细信息：

const DeletionSchema = Schema({
  userID: {
    type: Schema.Types.ObjectId,
    ref: 'User'
  }
},{
  timestamps: {
    createdAt: true
})

const DiscussionSchema = Schema({
  participants: [{
    type: Schema.Types.ObjectId,
    ref: 'User'
  }],
  messages: [MessageSchema],
  deletions: [DeletionSchema]
}, {
  timestamps: true
})

const MessageSchema = Schema({
  author: {
    type: Schema.Types.ObjectId,
    ref: 'User'
  },
  text: String
}, {
  timestamps: true
})

{
    "_id" : ObjectId("5fc09358355f682571644db5"),
    "participants" : [ 
        ObjectId("5fbdb3c0d1daa832cce7435f"), 
        ObjectId("5fbe92f63bcc731d3d677a26")
    ],
    "__v" : 0,
    "createdAt" : ISODate("2020-11-27T05:49:12.784Z"),
    "messages" : [ 
        {
            "deletedAt" : null,
            "_id" : ObjectId("5fc09ab7ad6276548b328a43"),
            "author" : ObjectId("5fbe92f63bcc731d3d677a26"),
            "text" : "This one must stay !",
            "updatedAt" : ISODate("2020-11-27T06:20:39.803Z"),
            "createdAt" : ISODate("2020-11-27T06:20:39.803Z")
        }, 
        {
            "deletedAt" : null,
            "_id" : ObjectId("5fc099a8b4415f528ceabc43"),
            "author" : ObjectId("5fbe92f63bcc731d3d677a26"),
            "text" : "This one must stay !",
            "updatedAt" : ISODate("2020-11-27T06:16:08.173Z"),
            "createdAt" : ISODate("2020-11-27T06:16:08.173Z")
        }, 
        {
            "deletedAt" : null,
            "_id" : ObjectId("5fc09358efb3374d4e2264be"),
            "author" : ObjectId("5fbe92f63bcc731d3d677a26"),
            "text" : "Hello world",
            "updatedAt" : ISODate("2020-11-27T05:49:12.784Z"),
            "createdAt" : ISODate("2020-11-27T05:49:12.784Z")
        }
    ],
    "updatedAt" : ISODate("2020-11-27T06:20:39.803Z"),
    "deletions" : [ 
        {
            "_id" : ObjectId("5fc095d220928a4eff942652"),
            "userID" : ObjectId("5fbe92f63bcc731d3d677a26"),
            "updatedAt" : ISODate("2020-11-27T05:59:46.731Z"),
            "createdAt" : ISODate("2020-11-27T05:59:46.731Z")
        }
    ]
}

所以，如果给定的userID匹配deletions子文档，我想过滤掉message删除后创建的人createdAt

标签： mongodbmongoose

解决方案

  • $match过滤条件
  • $addFields添加新字段deletion，$filter以从该结果中获取userID对象并使用该结果获取第一个对象deletions$first
  • $project显示必填字段，根据日期过滤messages使用和条件，并获取有限的消息$filter$slice
Discussion.aggregate([
  {
    $match: {
      _id: mongoose.Types.ObjectId(discussionID),
      participants: mongoose.Types.ObjectId(userID)
    }
  },
  {
    $addFields: {
      deletion: {
        $first: {
          $filter: {
            input: "$deletions",
            cond: { $eq: ["$$this.userID", mongoose.Types.ObjectId(userID)] }
          }
        }
      }
    }
  },
  {
    $project: {
      messages: {
        $slice: [
          {
            $filter: {
              input: "$messages",
              cond: { $lt: ["$$this.createdAt", "$deletion.updatedAt"] }
            }
          },
          limit
        ]
      }
    }
  }
])

操场

推荐阅读