How to lookup with deep level in mongoDB? deep >2

Question

I have three collections: ServiceCate store information about the services inside of HairSalon

ServiceCate:

{
    "_id" : ObjectId("5f64cb4d11eaeaf154047d30"),
    "serviceCode" : NumberLong(2),
    "serviceName" : "Take earwax"
}
{
    "_id" : ObjectId("5f64cb9511eaeaf154047deb"),
    "serviceCode" : NumberLong(3),
    "serviceName" : "Shampoo"
}
{
    "_id" : ObjectId("5f64cbec11eaeaf154047eb3"),
    "serviceCode" : NumberLong(4),
    "serviceName" : "Haircuts"
}

Payment collection store information of payment.

Payments:

{
    "_id" : ObjectId("5f64cd8611eaeaf15404825a"),
    "paymentCode" : NumberLong(10),
    "customerCode" : "2000000001",
    "orderNumber" : "TT20000001",
    "date" : ISODate("2020-06-01T00:07:00.000Z"),
    "staffCode" : NumberLong(3)
}
{
    "_id" : ObjectId("5f64cdc011eaeaf1540482ea"),
    "paymentCode" : NumberLong(11),
    "customerCode" : "2000000002",
    "orderNumber" : "TT20000002",
    "date" : ISODate("2020-06-01T00:07:00.000Z"),
    "staffCode" : NumberLong(3)
}
{
    "_id" : ObjectId("5f64ce0111eaeaf15404837c"),
    "paymentCode" : NumberLong(12),
    "customerCode" : "2000000003",
    "orderNumber" : "TT20000003",
    "date" : ISODate("2020-06-01T00:07:00.000Z"),
    "staffCode" : NumberLong(4)
}

And PaymentDetails store all information about detail payment for each service (Haircut, Shampoo…)

PaymentDetails

{
    "_id" : ObjectId("5f64cea611eaeaf154048508"),
    "paymentDetailCode" : NumberLong(1),
    "paymentCode" : NumberLong(10),
    "serviceCode" : NumberLong(2),
    "money" : "180000.0"
}

{
    "_id" : ObjectId("5f64cfed11eaeaf154048897"),
    "paymentDetailCode" : NumberLong(20),
    "paymentCode" : NumberLong(10),
    "serviceCode" : NumberLong(3),
    "money" : "180000.0"
}
{
    "_id" : ObjectId("5f64d2c311eaeaf154048fea"),
    "paymentDetailCode" : NumberLong(20),
    "paymentCode" : NumberLong(10),
    "serviceCode" : NumberLong(4),
    "money" : "180000.0"
}

I want to get payment for one order number (for one person) with detail payment for the services of hair salon (like HairCut, Sampo, Take earwax) I do like below.

db.Payments.aggregate([
{
    $match: {
       $and:[{"orderNumber": "TT20000001"}]
    }
},
{
  $lookup: {
    from: "PaymentDetails",
    localField: "paymentCode",
    foreignField: "paymentCode",
    as: "PaymentDetails"
  }
},
// {
//   $lookup: {
//     from: "ServiceCate",
//     localField: "PaymentDetails.serviceCode",
//     foreignField: "serviceCode",
//     as: "PaymentDetails.serviceCode",
//   }
// },
     
]);

Below is my result:

{
    "_id" : ObjectId("5f64cd8611eaeaf15404825a"),
    "paymentCode" : NumberLong(10),
    "customerCode" : "2000000001",
    "orderNumber" : "TT20000001",
    "date" : ISODate("2020-06-01T00:07:00.000Z"),
    "staffCode" : NumberLong(3),
    "PaymentDetails" : [ 
        {
            "_id" : ObjectId("5f64cea611eaeaf154048508"),
            "paymentDetailCode" : NumberLong(1),
            "paymentCode" : NumberLong(10),
            "serviceCode" : NumberLong(2), // I want to populate this field
            "money" : "180000.0"
        }, 
        {
            "_id" : ObjectId("5f64cfed11eaeaf154048897"),
            "paymentDetailCode" : NumberLong(20),
            "paymentCode" : NumberLong(10),
            "serviceCode" : NumberLong(3),// I want to populate this field
            "money" : "180000.0"
        }, 
        {
            "_id" : ObjectId("5f64d2c311eaeaf154048fea"),
            "paymentDetailCode" : NumberLong(20),
            "paymentCode" : NumberLong(10),
            "serviceCode" : NumberLong(4),// I want to populate this field
            "money" : "180000.0"
        }
    ]
}

I want to populate “serviceCode” field to get details service, I was add this code but it’s not work.

 {
   $lookup: {
     from: "ServiceCate",
     localField: "PaymentDetails.serviceCode",
     foreignField: "serviceCode",
     as: "PaymentDetails.serviceCode",
   }
 }

I try many time with google search. I also find this link enter link description here may seem like my problem, I already try but not working. Please help me to check it out. Thanks you so much

cyrilantony · Accepted Answer · 2020-09-19T14:59:50.777

A 2 level lookup is needed here. This query will fetch ServiceCate details:

db.Payments.aggregate([
   {
      "$match": {
         orderNumber: "TT20000001"
      }
   },
   {
      "$lookup": {
         "from": "PaymentDetails",
         "let": {
            "pCode": "$paymentCode"
         },
         "pipeline": [
            {
               "$match": {
                  "$expr": {
                     "$eq": [
                        "$paymentCode",
                        "$$pCode"
                     ]
                  }
               }
            },
            {
               "$lookup": {
                  "from": "ServiceCate",
                  "let": {
                     "sCode": "$serviceCode"
                  },
                  "pipeline": [
                     {
                        "$match": {
                           "$expr": {
                              "$eq": [
                                 "$serviceCode",
                                 "$$sCode"
                              ]
                           }
                        }
                     }
                  ],
                  "as": "ServiceCate"
               }
            }
         ],
         "as": "PaymentDetails"
      }
   }
]).pretty();

This is the output :

{
    "_id" : ObjectId("5f64e9e92240b900afe16fc7"),
    "paymentCode" : NumberLong(10),
    "customerCode" : "2000000001",
    "orderNumber" : "TT20000001",
    "date" : ISODate("2020-06-01T00:07:00Z"),
    "staffCode" : NumberLong(3),
    "PaymentDetails" : [
        {
            "_id" : ObjectId("5f64eaed2240b900afe16fc8"),
            "paymentDetailCode" : NumberLong(1),
            "paymentCode" : NumberLong(10),
            "serviceCode" : NumberLong(2),
            "money" : "180000.0",
            "ServiceCate" : [
                {
                    "_id" : ObjectId("5f64e8f82240b900afe16fc2"),
                    "serviceCode" : NumberLong(2),
                    "serviceName" : "Take earwax"
                }
            ]
        },
        {
            "_id" : ObjectId("5f64eb431476f6ad01ecbb44"),
            "paymentDetailCode" : NumberLong(20),
            "paymentCode" : NumberLong(10),
            "serviceCode" : NumberLong(3),
            "money" : "180000.0",
            "ServiceCate" : [
                {
                    "_id" : ObjectId("5f64e94a1476f6ad01ecbb40"),
                    "serviceCode" : NumberLong(3),
                    "serviceName" : "Shampoo"
                }
            ]
        },
        {
            "_id" : ObjectId("5f64eb541476f6ad01ecbb45"),
            "paymentDetailCode" : NumberLong(20),
            "paymentCode" : NumberLong(10),
            "serviceCode" : NumberLong(4),
            "money" : "180000.0",
            "ServiceCate" : [
                {
                    "_id" : ObjectId("5f64e9581476f6ad01ecbb41"),
                    "serviceCode" : NumberLong(4),
                    "serviceName" : "Haircuts"
                }
            ]
        }
    ]
}

How to lookup with deep level in mongoDB? deep >2

1 Answers1