13

I'm trying to do this for days, but can't find any success

I'm using MongoDB, and I tried to do it with many pipeline steps but I couldn't find a way.

I have a players collection, each player contains an items array

{
    "_id": ObjectId("5fba17c1c4566e57fafdcd7e"),
    "username": "moshe",
    "items": [
        {
            "_id": ObjectId("5fbb5ac178045a985690b5fd"),
            "equipped": false,
            "itemId": "5fbb5ab778045a985690b5fc"
        }
    ]
}

I have an items collection where there is more information about each item in the player items array.

{
    "_id": ObjectId("5fbb5ab778045a985690b5fc"),
    "name": "Axe",
    "damage": 4,
    "defense": 6
}

My goal is to have a player document with all the information about the item inside his items array, so it will look like that:

{
    "_id": ObjectId("5fba17c1c4566e57fafdcd7e"),
    "username": "moshe",
    "items": [
        {
            "_id": ObjectId("5fbb5ac178045a985690b5fd"),
            "equipped": false,
            "itemId": "5fbb5ab778045a985690b5fc",
            "name": "Axe",
            "damage": 4,
            "defense": 6
        }
    ]
}
0

2 Answers 2

17
  • $unwind deconstruct items array
  • $lookup to join items collection, pass itemsId into let after converting it to object id using $toObjectId and pass items object,
    • $match itemId condition
    • $mergeObject merge items object and $$ROOT object and replace to root using $replaceRoot
  • $group reconstruct items array again, group by _id and get first username and construct items array
db.players.aggregate([
  { $unwind: "$items" },
  {
    $lookup: {
      from: "items",
      let: {
        itemId: { $toObjectId: "$items.itemId" },
        items: "$items"
      },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$itemId" ] } } },
        { $replaceRoot: { newRoot: { $mergeObjects: ["$$items", "$$ROOT"] } } }
      ],
      as: "items"
    }
  },
  {
    $group: {
      _id: "$_id",
      username: { $first: "$username" },
      items: { $push: { $first: "$items" } }
    }
  }
])

Playground


Second option using $map, and without $unwind,

  • $addFields for items convert itemId string to object type id using $toObjectId and $map
  • $lookup to join items collection
  • $project to show required fields, and merge items array and itemsCollection using $map to iterate loop of items array $filter to get matching itemId and $first to get first object from return result, $mergeObject to merge current object and returned object from $first
db.players.aggregate([
  {
    $addFields: {
      items: {
        $map: {
          input: "$items",
          in: {
            $mergeObjects: ["$$this", { itemId: { $toObjectId: "$$this.itemId" } }]
          }
        }
      }
    }
  },
  {
    $lookup: {
      from: "items",
      localField: "items.itemId",
      foreignField: "_id",
      as: "itemsCollection"
    }
  },
  {
    $project: {
      username: 1,
      items: {
        $map: {
          input: "$items",
          as: "i",
          in: {
            $mergeObjects: [
              "$$i",
              {
                $first: {
                  $filter: {
                    input: "$itemsCollection",
                    cond: { $eq: ["$$this._id", "$$i.itemId"] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

Sign up to request clarification or add additional context in comments.

Comments

8

First I'd strongly suggest that you should store the items.itemId as ObjectId, not strings.

Then another simple solution can be:

db.players.aggregate([
  {
    $lookup: {
      from: "items",
      localField: "items.itemId",
      foreignField: "_id",
      as: "itemsDocuments",
    },
  },
  {
    $addFields: {
      items: {
        $map: {
          input: { $zip: { inputs: ["$items", "$itemsDocuments"] } },
          in: { $mergeObjects: "$$this" },
        },
      },
    },
  },
  { $unset: "itemsDocuments" },
])

1 Comment

Wrong / incomplete, because itemsDocuments are in natural order of the documents in the items collection, not the order of the local items array. See jira.mongodb.org/browse/SERVER-32947.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.