CODEWITHSUNDEEP
mongodb
theFreedomBanana
theFreedomBanana

Reputation: 2610

Mongo Aggregate Objects with $lookup using non matching values

I've got an Object Mission referring to another object Position with a key _p_position.

Mission objects look like:

{
  _id: "ijjn97678",
  _p_position: "Position$qwerty123",
  ...
}

Position objects look like:

{
  _id: "qwerty123",
  ...
}

I don't know if it is Mongo or Parse convention but as one can see a Position$ is added on relational position attribute in missions.

I'd like to aggregate both into a single Object to get a results similar to the following: 

{
  _id: "ijjn97678",
  _p_position: "Position$qwerty123",
  positions: [
    {
      _id: "qwerty123"
    }
  ]
}

using: 

missions.aggregate([
  {
    $lookup: {
      as: "position",
      from: "Position",
      foreignField: "_id",
      localField: "_p_position",
    },
  },
])

But I need to remove Position$ from _p_position. Is there a way I can compute "_p_position" before it is used to find a matching Position's id ? PS: I only have reading rights on DB

Upvotes: 1

Views: 273

Answers (1)

mickl
mickl

Reputation: 49945

You can use $addFields to add another field which will be then passed to $lookup stage. To get the part that's following the dollar sign you need: $indexOfBytes and $substr operators. Additionally dollar sign itself is a special character in Aggregation Framework (represents a field reference) so you need $literal to force it to be considered as regular field

db.missions.aggregate([
    {
        $addFields: {
            value: {
                $let: {
                    vars: { index: { $indexOfBytes: [ "$_p_position", { $literal: "$" } ]  } },
                    in: { $substr: [ "$_p_position", { $add: [ "$$index", 1 ] } , { $strLenBytes: "$_p_position" } ] }
                }
            }
        }
    },
    {
        $lookup: {
            from: "Position",
            localField: "value",
            foreignField: "_id",
            as: "position" 
        }
    }
])

Upvotes: 1

Related Questions