对MongoDB中的子副文档进行连接

Question

我正在尝试使用mongoDB进行左JON，以获取每个用户在每个商店中购买的每个产品的数量。我是汇总和nosql的新手，并且看到如何加入用户遇到问题。产品。

我想打印以下JSON：

{"userId": "user1", "StoreName": "Store 1", "ProductName": "Crest Toothpaste", "# of Purchases": 200}

我正在使用MongoDB的3.6版

• 存储
  • id
  • 名称
  • 城市
  • 状态
  • Zipcode
• 用户
  • id
  • 用户ID
  • 电子邮件address
  • 产品：[{aprodipid，商店：[{storeid}]}]
• 产品
  • id
  • sku
  • 价格
  • productrewards：[{pointsned，dreveramount}]

这是模式的样本：

UserSchema: Schema = new Schema({
        userName: {type: Schema.Types.String, minlength: 6, maxlength: 10, required: true},
        emailAddress: {type: Schema.Types.String, minlength: 8, maxlength: 55, required: true}
    products: [{productId: {type: Schema.Types.ObjectId, required: true, ref: 'product’},
              stores: [{storeId: {type: Schema.Types.ObjectId, required: true, ref: ‘store’}]]
})
ProductSchema: Schema = new Schema({
    name: {type: Schema.Types.String, minlength: 1, maxlength: 30, required: true},
    price: {type: Schema.Types.Number, required: true},
    productRewards: [{
        pointsNeeded: {type: Schema.Types.Number, required: true},
        rewardAmount: {type: Schema.Types.Number, required: true}
    }]
}
StoreSchema: Schema = new Schema({
        name: {type: Schema.Types.String, minlength: 10, maxlength: 30, required: true},
        city: {type: Schema.Types.String, minlength: 1, maxlength: 35, required: true},
    state: {type: Schema.Types.String, minlength: 1, maxlength: 35, required: true},
    zipCode: {type: Schema.Types.String, minlength: 5, maxlength: 13, required: true},
}

我试图做这样的事情，但我并没有将其放在加入的左侧和右侧的成员田字段相等的地方拉动它，似乎更像是一个完整的加入：

db.user.aggregate(
    { $unwind : "$products" },
    { $unwind : "$products.stores" },
    { "$lookup": {
        from: "stores",
        localField: "storeId",
        foreignField: "_id",
        as: "stores"
    }
})

考虑到一个子件，我该如何执行必要的加入？我对每个加入的外观感到困惑，尤其是子订户。

Answer 1

您可以使用以下集合。

以下查询对每种产品和商店信息执行多个$lookup，然后在商店，用户和产品上进行$group，以计算购买的否。

$unwind嵌入式阵列的产品，然后是$lookup以获取产品信息。

$unwind产品内嵌入的阵列，然后是$lookup获取商店信息。

db.users.aggregate([
  {"$unwind":"$products"},
  {"$lookup":{
    "from":"products",
    "localField":"products.productId",
    "foreignField":"_id",
    "as":"products.product"
  }},
  {"$unwind":"$products.stores"},
  {"$lookup":{
    "from":"stores",
    "localField":"products.stores.storeId",
    "foreignField":"_id",
    "as":"products.stores"
  }},
  {"$group":{
    "_id":{
      "userId":"$userId",
      "storeId":"$products.stores.storeId",
      "productId":"$products.productId"
    },
    "StoreName":{"$first":{"$arrayElemAt":["$products.stores.name", 0]}},
    "ProductName":{"$first":{"$arrayElemAt":["$products.product.name", 0]}},
    "# of Purchases":{"$sum":1}
  }},
  {"$project":{
    "_id":0,
    "userId":"$_id.userId",
    "StoreName":1,
    "ProductName":1,
    "# of Purchases":1
  }}
])