Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB $lookup and $map array of objects

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
        }
    ]
}
like image 765
Asaf Avatar asked Nov 28 '20 08:11

Asaf


2 Answers

  • $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

like image 70
turivishal Avatar answered Sep 28 '22 15:09

turivishal


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" },
])
like image 32
IcanDivideBy0 Avatar answered Sep 28 '22 16:09

IcanDivideBy0