CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Krisna
Krisna

Reputation: 3423

MongoDb aggregate query three collection

I am really new to MongoDB query and practicing mongoDb query. I am using this official mongoDb-package. I am following this doc .I have three collections of data. One is bags, one is sellers and last one is cloths. Our data architecture is When seller sends bags of cloths with his/her informations. We created seller collections like this:

{   "sellerId": 1234,
    "firstName": "John",
    "lastName": "doe",
    "fullName": "John Doe",
    "email": "[email protected]",
    "bagId": 2224
}

this is bag collection

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

After selection the cloths from bags which is suppose to be sell, we create cloth collection.

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

When the cloth get sold from Shopify. we get arrays of SKU-ID which is our cloth collections clothId.

My goal is when the cloth get sold, we match the clothId(SKU-ID FROM SHOPIFY) the find bagId, from that bagId we will get the seller information.

My expected outcome is

{

    "firstName": "John",
    "lastName": "doe",
    "fullName": "John Doe",
    "email": "[email protected]",
    "bagId": 2224,
    "clothId": 1244 // sold cloth id which I got from Shopify
    "sellerId": 1234
}

I successfully match the sold cloth id and shopify (SKU-ID) and get bags info but I could not able figure it out, how to get get sellers info from the bagId

This is what my code where I get sold-cloth info and bags details but it does not give me seller's info just got empty arrays

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()

Upvotes: 2

Views: 55

Answers (1)

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57095

Demo - https://mongoplayground.net/p/MjGG_8HLT1T

You have to use $unwind, you need to get seller id from bags.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
    }
  }
])

You can skip unwinding in case you know there will be the only matching element coming from lookup.

Demo - https://mongoplayground.net/p/IP0epMpQ6mE

Upvotes: 1

Related Questions