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
}
}
])
CodePudding user response：
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
CodePudding user response：
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
}
}
])