如何選擇查找/圖形查找以從另一個集合中獲取記錄

我有下面的 dbData，其中在contracts集合中，有一個名為的屬性via，它是一個包含條目 mongoose id 的陣列depot。

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")
    }
  ]
}

我正在嘗試使用查找進行聚合，這樣我需要depots根據via.

以下是我嘗試過的查詢

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

為此，我希望獲得其中contracts包含一個新添加的屬性的條目，該屬性depots包含由via. 但是我得到的實際回應是depots[]。

[
  {
    "_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"
    ]
  }
]

問題 1： 能否幫助我了解我在$lookup實施中出了什么問題？ 問題 2： 同樣的查詢是否可以使用graphlookup？至少有可能的架構更改contracts？

編輯：當我將它作為 objectIds 保存在 via 而不是字串時，它按預期作業

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

當我保留viaas 字串時，在階段之前的管道中添加了一個附加階段$lookup以使其作業

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
    }
  }
])

uj5u.com熱心網友回復：

一種選擇是$map在 $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}}
])

看看它在操場上的例子是如何作業的

無需使用$graphlookup，因為您只有一個專案呼叫另一個專案，而不是遞回呼叫。這里的一個相關替代方案$lookup是populate. 你可以在這里閱讀一下這個選項

uj5u.com熱心網友回復：

$lookup也能夠$graphLookup通過以下查詢實作

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
    }
  }
])

標籤：mongodb 猫鼬 mongodb查询 总计的

上一篇：如何使用if陳述句執行用戶輸入驗證

下一篇：貓鼬更新多個用戶陣列