Question 116876 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to choose lookup/graphlookup to get records from another collection

I have the below dbData wherein in the contracts collection, there is an attribute called via which is an array that holds the mongoose ids of depot entries.

db={
  "contracts": [
    {
      "_id": ObjectId("630c9e23fb146c4b3b801b9e"),
      "detention_id": [],
      "route_exclude": false,
      "trip_count": null,
      "fill_first": false,
      "is_dedicated": false,
      "truck_count": 0,
      "running_trip_count": 0,
      "is_round_trip_eligible": false,
      "is_synced": false,
      "bdr_label": "",
      "bdr_charge": 0,
      "is_migrated": false,
      "current_trip_count": 0,
      "client_id": ObjectId("5e47c1f4a697c60195050059"),
      "updated_by": ObjectId("60a39adad079f9004d144cb8"),
      "user_id": ObjectId("60a39adad079f9004d144cb8"),
      "transporter_id": ObjectId("61d6d2227465c501d0915794"),
      "vehicle_type_id": ObjectId("61e1b3ad00516e004c893145"),
      "detention_group": "",
      "delivery_type": 2,
      "source": "amaravathi",
      "destination": "Kozhikode",
      "isDirect": false,
      "via": [
        "6304804a9f90b8018844fc20",
        "62fb93a711ea3f019ffe19e6"
      ],
      "distance": 100,
      "status": 1,
      "contract_type": 1,
      "current": {
        "minimum_rate": 0,
        "multiplying_factor": null,
        "min_slab": 0,
        "additional_charge_percentage": 0,
        "hilly_region_addition_percentage": 0,
        "return_freight_rate": 0,
        "surge_threshold": 0,
        "fixed_rate": null,
        "oda_rate": null,
        "oda_unit": 3,
        "minimum_oda": 0,
        "point_charges": 0,
        "pickup_point_charges": 0,
        "start": ISODate("2022-08-26T00:00:00.000+05:30"),
        "end": ISODate("2023-08-24T23:59:00.000+05:30"),
        "freight_unit": 1,
        "freight_rate": 5000,
        "transit_time": 172800000
      },
      "next": [],
      "previous": [],
      "lookup_key": "f93187d8364fca313ab92c83ac39eafa",
      "created_at": ISODate("2022-08-26T15:26:15.865+05:30"),
      "updated_at": ISODate("2022-08-29T13:20:39.058+05:30")
    }
  ],
  "depots": [
    {
      "_id": ObjectId("6304804a9f90b8018844fc20"),
      "consignee_ref_ids": [],
      "status": 1,
      "coordinates": [
        12.9039441,
        80.14567129999999
      ],
      "unloading_capacity": 0,
      "has_pit_stop": false,
      "type": 1,
      "plant_type": 1,
      "ports_associated": [],
      "overseas_customer": false,
      "sez": false,
      "timezone": "Asia/Kolkata",
      "fax_number": "",
      "is_optima_group_by_cluster": "",
      "country": "India",
      "name": "CTB",
      "reference_id": "CTB",
      "client_id": ObjectId("5e47c1f4a697c60195050059"),
      "address": "CTB",
      "city": "Chennai",
      "state": "Tamil Nadu",
      "region": "South",
      "pincode": "600126",
      "phone": "",
      "delivery_type": 1,
      "short_code": "CTB",
      "gstn_no": "HSKK309393k",
      "gates": [
        {
          "coordinates": [
            12.9039441,
            80.14567129999999
          ],
          "exclude_trucks": [],
          "_id": ObjectId("630480789f90b8018844fc21"),
          "country": "India",
          "name": "CTG1",
          "reference_id": "CTG1",
          "status": 1,
          "address": "CTB",
          "city": "Chennai",
          "state": "Tamil Nadu",
          "region": "South",
          "pincode": "600126",
          "short_code": "CTG1",
          "special_tax_rebate": false,
          "gstin": null,
          "is_virtual_gate": false,
          "category": "",
          "delivery_do_not_combine": false,
          "combine": false,
          "loading_time": {
            "start": null,
            "end": null
          },
          "unloading_time": {
            "start": null,
            "end": null
          },
          "legal_entity": null,
          "display_reference_name": "",
          "updated_at": ISODate("2022-08-23T12:53:46.233+05:30"),
          "created_at": ISODate("2022-08-23T12:53:36.046+05:30")
        }
      ],
      "docks": [],
      "updated_by": ObjectId("5eaba6438a4895004916adbe"),
      "display_reference_name": "",
      "receivers": [],
      "created_at": ISODate("2022-08-23T12:52:50.182+05:30"),
      "updated_at": ISODate("2022-08-23T13:09:18.468+05:30")
    },
    {
      "_id": ObjectId("62fb93a711ea3f019ffe19e6"),
      "consignee_ref_ids": [],
      "status": 1,
      "coordinates": [
        20.593684,
        78.96288
      ],
      "unloading_capacity": 0,
      "has_pit_stop": false,
      "type": 1,
      "plant_type": 1,
      "ports_associated": [],
      "overseas_customer": false,
      "sez": false,
      "timezone": "Asia/Kolkata",
      "fax_number": "",
      "is_optima_group_by_cluster": "",
      "country": "India",
      "name": "Test",
      "reference_id": "Test",
      "client_id": ObjectId("5e47c1f4a697c60195050059"),
      "address": "test",
      "city": "Test",
      "state": "Test",
      "region": "North",
      "pincode": "0100301",
      "phone": "",
      "delivery_type": 1,
      "short_code": "test",
      "gstn_no": "1111",
      "gates": [],
      "docks": [],
      "updated_by": ObjectId("62e7b04d91a9b50189b4319d"),
      "display_reference_name": "",
      "receivers": [],
      "created_at": ISODate("2022-08-16T18:25:03.453+05:30"),
      "updated_at": ISODate("2022-08-16T18:25:03.913+05:30")
    },
    {
      "_id": ObjectId("630cc2ba38f87401899c35f1"),
      "consignee_ref_ids": [],
      "status": 1,
      "coordinates": [
        20.593684,
        78.96288
      ],
      "unloading_capacity": 0,
      "has_pit_stop": false,
      "type": 1,
      "plant_type": 1,
      "ports_associated": [],
      "overseas_customer": false,
      "sez": false,
      "timezone": "Asia/Kolkata",
      "fax_number": "",
      "is_optima_group_by_cluster": "",
      "country": "India",
      "name": "B20220829191440",
      "reference_id": "B20220829191440",
      "client_id": ObjectId("62d8f30ce763689b942d5e12"),
      "address": "No 15,GST Street",
      "city": "Blr20220829191440",
      "state": "Karnataka",
      "region": "South",
      "pincode": "123456",
      "phone": "5588",
      "delivery_type": 1,
      "short_code": "B214945",
      "gstn_no": "123",
      "gates": [
        {
          "coordinates": [
            10.9290666,
            78.7438114
          ],
          "exclude_trucks": [],
          "_id": ObjectId("630cc2bb38f87401899c35f2"),
          "country": "India",
          "name": "Gate 2",
          "reference_id": "BG20220829191440",
          "status": 1,
          "address": "No 15 GST Road",
          "city": "Blr20220829191440",
          "state": "Karnataka",
          "region": "South",
          "pincode": "123456",
          "short_code": "G1214945",
          "special_tax_rebate": false,
          "is_virtual_gate": false,
          "category": "b",
          "combine": true,
          "updated_at": ISODate("2022-08-29T19:14:27.912+05:30"),
          "created_at": ISODate("2022-08-29T19:14:27.551+05:30")
        }
      ],
      "docks": [],
      "updated_by": ObjectId("62d8f30ce763689b942d5e15"),
      "display_reference_name": "",
      "receivers": [],
      "created_at": ISODate("2022-08-29T19:14:26.892+05:30"),
      "updated_at": ISODate("2022-08-29T19:14:27.912+05:30")
    }
  ]
}

I am trying to aggregate using lookup such that I need to fetch the entries from the depots based on the ids that are present in via.

Below is the query I have tried

db.contracts.aggregate([
  {
    $match: {
      _id: ObjectId("630c9e23fb146c4b3b801b9e")
    }
  },
  {
    $lookup: {
      from: "depots",
      localField: "via",
      as: "depots",
      foreignField: "_id"
    }
  },
  {
    $sort: {
      count: -1
    }
  }
])

for which I expect to get the contracts entry with a newly added attribute in it called depots that contains the documents for the corressponding two ids held by via.
But the actual response that I got has depots as [].

[
  {
    "_id": ObjectId("630c9e23fb146c4b3b801b9e"),
    "bdr_charge": 0,
    "bdr_label": "",
    "client_id": ObjectId("5e47c1f4a697c60195050059"),
    "contract_type": 1,
    "created_at": ISODate("2022-08-26T09:56:15.865Z"),
    "current": {
      "additional_charge_percentage": 0,
      "end": ISODate("2023-08-24T18:29:00Z"),
      "fixed_rate": null,
      "freight_rate": 5000,
      "freight_unit": 1,
      "hilly_region_addition_percentage": 0,
      "min_slab": 0,
      "minimum_oda": 0,
      "minimum_rate": 0,
      "multiplying_factor": null,
      "oda_rate": null,
      "oda_unit": 3,
      "pickup_point_charges": 0,
      "point_charges": 0,
      "return_freight_rate": 0,
      "start": ISODate("2022-08-25T18:30:00Z"),
      "surge_threshold": 0,
      "transit_time": 1.728e+08
    },
    "current_trip_count": 0,
    "delivery_type": 2,
    "depots": [],
    "destination": "Kozhikode",
    "detention_group": "",
    "detention_id": [],
    "distance": 100,
    "fill_first": false,
    "isDirect": false,
    "is_dedicated": false,
    "is_migrated": false,
    "is_round_trip_eligible": false,
    "is_synced": false,
    "lookup_key": "f93187d8364fca313ab92c83ac39eafa",
    "next": [],
    "previous": [],
    "route_exclude": false,
    "running_trip_count": 0,
    "source": "amaravathi",
    "status": 1,
    "transporter_id": ObjectId("61d6d2227465c501d0915794"),
    "trip_count": null,
    "truck_count": 0,
    "updated_at": ISODate("2022-08-29T07:50:39.058Z"),
    "updated_by": ObjectId("60a39adad079f9004d144cb8"),
    "user_id": ObjectId("60a39adad079f9004d144cb8"),
    "vehicle_type_id": ObjectId("61e1b3ad00516e004c893145"),
    "via": [
      "6304804a9f90b8018844fc20",
      "62fb93a711ea3f019ffe19e6"
    ]
  }
]

Question 1: Could some help me on where Iam going wrong with the $lookup implementation?
Question 2: Also could the same query, be achievable using graphlookup? Atleast with a possible schema change in contracts?

Edit:
when I kept it as objectIds in via instead of strings it works as expected

"via": [
        ObjectId("6304804a9f90b8018844fc20"),
        ObjectId("62fb93a711ea3f019ffe19e6")
      ]

When I kept the via as strings, added an additional stage in the pipeline before the $lookup stage to make it work

db.contracts.aggregate([
  {
    $match: {
      _id: ObjectId("630c9e23fb146c4b3b801b9e")
    }
  },
  {
    $addFields: {
      via: {
        $map: {
          input: "$via",
          in: {
            "$toObjectId": "$$this"
          }
        }
      }
    }
  },
  {
    $lookup: {
      from: "depots",
      localField: "via",
      as: "depots",
      foreignField: "_id"
    }
  },
  {
    $sort: {
      count: -1
    }
  }
])
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Was able to implement $lookup as well $graphLookup with the below query

    db.contracts.aggregate([
  {
    $match: {
      _id: ObjectId("630c9e23fb146c4b3b801b9e")
    }
  },
  {
    $addFields: {
      via: {
        $map: {
          input: "$via",
          in: {
            "$toObjectId": "$$this"
          }
        }
      }
    }
  },
  {
    $lookup: {
      from: "depots",
      localField: "via",
      as: "depots",
      foreignField: "_id"
    }
  },
  // GraphLookup stage
  /*  {
    "$graphLookup": {
      "from": "depots",
      "startWith": "$via",
      "connectFromField": "via",
      "connectToField": "_id",
      "as": "depots",
      "maxDepth": 2
    }
  }, */
  {
    $sort: {
      count: -1
    }
  }
])

  2. 0

    One option is to use a $map stage before the $lookup:

    db.contracts.aggregate([
  {$match: {_id: ObjectId("630c9e23fb146c4b3b801b9e")}},
  {$set: {via: {
     $map: {
          input: "$via",
          in: {"$toObjectId": "$$this"}
        }
      }
    }
  },
  {$lookup: {
      from: "depots",
      localField: "via",
      as: "depots",
      foreignField: "_id"
    }
  },
  {$sort: {count: -1}}
])

    See how it works on the playground example

    No need to use $graphlookup as you only have one item calling the other and not a recursive call. A relevant alternative to $lookup here will be populate. You can read about this option a bit here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search