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

debugcn 发表于 Dev

用户名

我想根据用户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

土生的

$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
        ]
      }
    }
  }
])

操场

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。