MongoDB中从prices集合关联查询含价格ID数组的product集合的正确实现方法

Got it, let's break down why you're hitting that Location40081 error. The core issue is that the $in operator needs its second argument to be an array, but MongoDB is encountering documents in your product collection where the prices field is either missing or not stored as an array. Plus, I spotted a tiny typo—you referenced db.getCollection('price') but your price collection is actually named prices (missing the trailing 's'), which could be causing unexpected behavior too.

Correct Aggregation Query (Strict Validation) #

This version ensures we only check product documents with a valid prices array, eliminating the error entirely:

db.getCollection('prices').aggregate([
  {
    "$lookup": {
      "from": "product",
      "let": { "prid": "$_id" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": ["$$prid", "$prices"]
            },
            // Guarantee prices exists and is an array
            "prices": { "$exists": true, "$type": "array" }
          }
        }
      ],
      "as": "product_tbl"
    }
  }
])

Alternative Query (Graceful Handling of Missing Fields) #

If you want to include product documents that don't have the prices field (without breaking the query), use $ifNull to replace missing prices fields with an empty array:

db.getCollection('prices').aggregate([
  {
    "$lookup": {
      "from": "product",
      "let": { "prid": "$_id" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$$prid",
                // Convert missing prices fields to empty arrays
                { "$ifNull": ["$prices", []] }
              ]
            }
          }
        }
      ],
      "as": "product_tbl"
    }
  }
])

Key Fixes Explained #

• Collection Name Typo: Corrected price to prices to match your actual collection setup.
• Array Validation: The first query adds $exists and $type checks to ensure we only process product documents with a valid prices array.
• Graceful Missing Field Handling: The second query uses $ifNull to convert missing prices fields into empty arrays, so $in always has a valid array to work with.

Either of these queries should resolve your error and correctly return each price record along with its associated product(s).

内容的提问来源于stack exchange，提问作者shuba.ivan