时间戳

首页 > 解决方案 > MongoDb聚合查询三合集

mongodb - MongoDb聚合查询三合集

问题描述

我对 MongoDB 查询和练习 mongoDb 查询真的很陌生。我正在使用这个官方mongoDb-package。我正在关注这个文档。我有三个数据集合。一个是包包，一个是卖家，最后一个是布料。我们的数据架构是当卖家发送带有他/她信息的布袋时。我们创建了这样的卖家集合：

{   "sellerId": 1234,
    "firstName": "John",
    "lastName": "doe",
    "fullName": "John Doe",
    "email": "john@yahoo.com",
    "bagId": 2224
}

这是包集合

 { 
   "sellerId": 1234
    "bagId": 2224,
    "source" : "fedex"
}

在从袋子中挑选出打算出售的布料后，我们创建了布料系列。

[
{
    "bagId": 2224,
    "brandName": "Denim",
    "size": "32",
    "clothId": 1244,
    "color": "green",
    "price": 20
},
{
    "bagId": 2224,
    "brandName": "Zara",
    "size": "31",
    "clothId": 1243,
    "color": "red",
    "price": 90
}
]

当布料从 Shopify 出售时。我们得到SKU-ID数组，这是我们的布料集合clothId。

我的目标是当布料售出时，我们将clothId(SKU-ID FROM SHOPIFY)与find bagId匹配，从那个bagId我们将获得卖家信息。

我的预期结果是

{

    "firstName": "John",
    "lastName": "doe",
    "fullName": "John Doe",
    "email": "john@yahoo.com",
    "bagId": 2224,
    "clothId": 1244 // sold cloth id which I got from Shopify
    "sellerId": 1234
}

我成功匹配了售出的布料 id 和 shopify (SKU-ID) 并获取了包包信息，但我无法弄清楚如何从 bagId 获取卖家信息

这就是我获取已售布料信息和包包详细信息的代码，但它没有给我卖家信息，只是得到了空数组

const sllerInfo = await client
    .db()
    .collection('clothes')
    .aggregate(
      [
        { $match: { clothId: { '$in': convertInto } } }, // convertInto is arrays of sold clothId which I got from Shopify
        {
          $lookup:
          {
            from: "bags",
            localField: "bagId",
            foreignField: "bagId",
            as: "bags"
          }
        },
        {
          $lookup:
          {
            from: "sellers",
            localField: "sellerId",
            foreignField: "sellerId",
            as: "sellers"
          },
        },
        {
          "$project": {
            "bagId": 1.0,
            "bags.source": 1.0,
            "sellers.firstName": 1.0, // dont get anything
            "sellers.lastName": 1.0,  // dont get anything
            "brand": 1.0
          }
        },
      ]
    ).toArray()

标签： mongodbmongodb-queryaggregation-framework

解决方案

演示 - https://mongoplayground.net/p/MjGG_8HLT1T

您必须使用$unwind，您需要从中获取卖家 IDbags.sellerId

db.clothes.aggregate([
  {
    $match: {
      clothId: {
        "$in": [
          1244,
          1243
        ]
      }
    }
  },
  {
    $lookup: {
      from: "bags",
      localField: "bagId",
      foreignField: "bagId",
      as: "bags"
    }
  },
  {
    $unwind: "$bags" // break into individual documents
  },
  {
    $lookup: {
      from: "sellers",
      localField: "bags.sellerId", // you need to get seller id from bags
      foreignField: "sellerId",
      as: "sellers"
    },
    
  },
  {
    $unwind: "$sellers"  // break into individual documents
  },
  {
    "$project": {
      _id: 0,
      "clothId": 1,
      "sellerId": "$sellers.sellerId",
      "bagId": 1,
      "bags.source": 1,
      "sellers.firstName": 1,
      "sellers.lastName": 1,
      "brand": 1
    }
  }
])

如果您知道将有唯一匹配的元素来自查找，则可以跳过展开。

演示 - https://mongoplayground.net/p/IP0epMpQ6mE

推荐阅读