时间戳

首页 > 解决方案 > MongoDB 如果其他集合中存在字段/引用，则从聚合中排除文档

mongodb - MongoDB 如果其他集合中存在字段/引用，则从聚合中排除文档

问题描述

const sellerSchema = Schema(
  {
    name: String,
    url:String
  }

const productSchema = Schema(
  {
    title: String,
    sellerUrl:String
  }

以下查询将返回sellerUrl所有产品的唯一性：

context.Product.aggregate([
        {
          $group: {
            _id: "$sellerUrl",
          }
        }
      ]);

但我也想从聚合中排除我已经保存的卖家。因此，如果url == sellerUrl聚合必须排除该卖方。请帮我

标签： mongodbmongooseaggregation-framework

解决方案

您可以尝试以下查询：

db.product.aggregate([
    {
      $group: {
        _id: "", /** group on no condition & push all unique `sellerUrl` to sellerUrls array */
        sellerUrls: { $addToSet: "$sellerUrl" }
      }
    },
    {
      $lookup: {
        from: "seller",
        let: { sellerUrls: "$sellerUrls" }, // creating local variable
        pipeline: [
          { $group: { _id: "", urls: { $addToSet: "$url" } } }, /** group on no condition & push all unique `url` to urls array */
          { $project: { _id: 0, uniqueAndNotInSellerColl: { $setDifference: [ "$$sellerUrls", "$urls" ] } } } // get difference between two arrays
        ],
        as: "data" // As we're grouping will always be one doc/element in an array
      }
    },
    /** Create a new root doc from getting first element(though it will have only one) from `data` array */
    {
      $replaceRoot: { newRoot: { $arrayElemAt: [ "$data", 0 ] } }
    }
  ])

测试： mongoplayground

更新 ：

由于您需要集合中的其他字段，product而不仅仅是sellerUrl字段，因此请尝试以下查询：

db.product.aggregate([
    {
      $group: {
        _id: "$sellerUrl",
        docs: { $push: { title: "$title" } } // We're only retrieving `title` field from `product` docs, if every field is needed use `$$ROOT`
      }
    },
    /** We've used basic `lookup` stage, use this if you've only few matching docs from `seller` collection
     *  If you've a lot of matching docs for each `_id` (sellerUrl), 
     *  then instead of getting entire `seller` doc (which is not needed) use `lookup` with aggregation pipeline & 
     *  just get `_id`'s of seller docs for better performace refer previous query
     */
    {
      $lookup: {
        from: "seller",
        localField: "_id",
        foreignField: "url",
        as: "sellerDocs"
      }
    },
    /** match will retain only docs which doesn't have a matching doc in seller collection */
    {
      $match: { sellerDocs: [] }
    },
    {
      $project: { sellerDocs: 0 }
    }
  ])

测试： mongoplayground

推荐阅读