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!

user_8987128 · Answer

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

user_2895415 · Answer

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
    }
  },

MongoDB lookup only on certain values

2 Answers