Some fields in an aggregation query in MongoDB do not return to me

Working with Data
,
1

I have a collection called of rewarded that have a foreign key from the hunters and rewards collection, I would like to make a query that does not return me hunter_id and reward_id, but return me reward_description (instead of reward_id) and name_hunter (instead of hunter_id). I am using MongoDB Shell in Visual Studio.

  • MongoDB query
      db.rewarded.aggregate([
      {
        $lookup: {
          from: "rewards",
          localField: "_id",
          foreignField: "reward_id",
          as: "reward"
        }
      },
      {
        $lookup: {
          from: "hunters",
          localField: "_id",
          foreignField: "hunter_id",
          as: "hunter"
        }
      },
      {
        $project: {
          _id: 1,
          reward_description: { $arrayElemAt: ["$reward.reward_description", 0] },
          hunter_name: { $arrayElemAt: ["$hunter.hunter_name", 0] },
          done: 1,
        }
      }
    ])
  • Returned result
    [
      { _id: ObjectId('65d20aa58d4389ed540a9f56'), done: true },
    }
  • That i wanted
    [
      { _id: ObjectId('65d20aa58d4389ed540a9f56'), reward_description: 'Capture Genei Ryodan members', name_hunter: 'Killua', done: true }
    ]

My collections are:

  • hunters
  {
    _id: ObjectId('65d20a6b8d4389ed540a9f54'),
    name_hunter: 'Killua'
  },
  • rewards
  {
    _id: ObjectId('65d20a208d4389ed540a9f52'),
    reward_description: 'Capture Genei Ryodan members'
  },
  • rewarded
  {
    _id: ObjectId('65d20aa58d4389ed540a9f56'),
    reward_id: '65d20a208d4389ed540a9f52',
    hunter_id: '65d20a6b8d4389ed540a9f54',
    done: true,
  },
2

You get no results because your $lookup are wrong.

The localField should refer to a field within the collection you aggregate. In your case you aggregate on rewarded, this means your localField are both wrong because they should be reward_id and hunter_id.

Even after fixing the above, it will not work. Because, reward_id and hunter_id within rewarded are string rather than ObjectId. You need to convert both to ObjectId for the $lookup to work. You have 2 choices:

  1. Permanently convert, once and for all, all documents of rewarded to object ids.
  2. Dynamically convert, every time you aggregate, before the $lookup.
3 
db.reward.aggregate([
  {
    $addFields: {
      reward_id: { $toObjectId: "$reward_id" },
      hunter_id: { $toObjectId: "$hunter_id" }
    }
  },
  {
    $lookup: {
      from: "rewards",
      localField: "reward_id",
      foreignField: "_id",
      as: "rewards"
    }
  },
  {
    $lookup: {
      from: "hunters",
      localField: "hunter_id",
      foreignField: "_id",
      as: "hunters"
    }
  },
  {
    $project: {
      _id: 1,
      reward_description: { $arrayElemAt: ["$rewards.reward_description", 0] },
      name_hunter: { $arrayElemAt: ["$hunters.name_hunter", 0] },
      done: 1
    }
  }
])
4

@steevej thank you, your explanation was useful.

1 Like
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.