MongoDB lookup only on certain values

Question

I have the following db data and query: https://mongoplayground.net/p/8jx-oejWQV7.

I am trying to perform a lookup only on the questions.value which are of type ObjectId. And would like a return from the query that looks like this:

[
  {
    "_id": ObjectId("60d9c9ff7fde704f28e89040"),
    "questions": [
      {
        "id": "607569f06df0a80197227d06",
        "value": "A String Value 1"
      },
      {
        "id": "607569f78f403f67dd1fefed",
        "value": "A String value 2"
      },
      {
        "id": "607569f78f403f67dd1fefef",
        "value": "A String value 3"
      },
      {
        "id": "607569e0701e55c65eae7554",
        "value": "C"
      },
      {
        "id": "607569e86b5b2a2175cce0f0",
        "value": "C"
      },
      {
        "id": "607569fff2597be5ff2444fd",
        "value": "B"
      },
      {
        "id": "607569fff2597be5ff2444fc",
        "value": "A"
      }
    ]
  }
]

How would I go about re-adding the non 'looked up' values?

Thank you!

Answer 1

You need to correct the things your query,

• $unwind - put as it is
• $lookup - put as it is but rename the as field to refValue
• $addFields to check condition if the result is an empty array then remain same value otherwise replace with lookup value using $first operator
• $unset to remove refValue it is not required now
• $group - put as it is but add status field

db.products.aggregate([
  { $unwind: "$questions" },
  {
    $lookup: {
      from: "ex_ref_data",
      let: { questionValue: "$questions.value" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$questionValue", "$_id"] } } },
        {
          $project: {
            displayLabel: 1,
            _id: 0
          }
        }
      ],
      as: "questions.refValue"
    }
  },
  {
    $addFields: {
      "questions.value": {
        $cond: [
          { $ne: ["$questions.refValue", []] },
          { $first: "$questions.refValue.displayLabel" },
          "$questions.value"
        ]
      }
    }
  },
  { $unset: "questions.refValue" },
  {
    $group: {
      _id: "$_id",
      status: { $first: "$status" },
      questions: { $push: "$questions" }
    }
  }
])

Playground

---

The second option, You can use the below approach without $unwind and $group stage,

• $project to show required fields
• $type to get datatype of the value
• $filter to iterate loop of questions and filter by type to select only values that having "objectId" elements
• $filter to iterate loop of questions and filter by type to select only none "obejctId" elements
• $lookup with the pipeline, let to pass reference values and check $in condition, and return _id and value fields
• $addFields to concat current questions with lookup result questions value using $map, $filter and $mergeObjects
• $unset to remove not required fields
• $concatArrays to concat both reference and nonreference arrays in one

db.products.aggregate([
  {
    $project: {
      status: 1,
      ref: {
        $filter: {
          input: "$questions",
          cond: { $eq: [{ $type: "$$this.value" }, "objectId"] }
        }
      },
      nonRef: {
        $filter: {
          input: "$questions",
          cond: { $ne: [{ $type: "$$this.value" }, "objectId"] }
        }
      }
    }
  },
  {
    $lookup: {
      from: "ex_ref_data",
      let: { questionValue: "$ref.value" },
      pipeline: [
        { $match: { $expr: { $in: ["$_id", "$$questionValue"] } } },
        { $project: { value: "$displayLabel" } }
      ],
      as: "ref"
    }
  },
  {
    $addFields: {
      ref: {
        $map: {
          input: "$ref",
          as: "r",
          in: {
            $mergeObjects: [
              "$$r",
              {
                $first: {
                  $filter: {
                    input: "$refResult",
                    cond: { $eq: ["$$this._id", "$$r.value"] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { $unset: "ref._id" },
  {
    $project: {
      status: 1,
      questions: { $concatArrays: ["$nonRef", "$ref"] }
    }
  }
])

Playground

Answer 2

In your query the issue is related to $unwind because of which you remove empty array values. You need to update you query a little with $ifNull to keep values from initial array, but in general answer is you need to use preserveNullAndEmptyArrays

...
  {
    $project: {
      "questions.id": 1,
      "questions.value": "$questions.value.displayLabel"
    }
  },
  {
    $unwind: {
      path: "$questions.value",
      preserveNullAndEmptyArrays: true
    }
  },