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
}
]
}
$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
arraydb.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
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" },
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With