I have the following documents in a collection:
[{
id: 1,
name: 'My document 1',
allItems: [{
id: 'item1',
name: 'My item 1'
}, {
id: 'item2',
name: 'My item 2'
}, {
id: 'item3'
name: 'My item 3'
}],
containers: [{
id: 'container1',
selectedItems: [
'item3',
'item1'
]
}, {
id: 'container2',
selectedItems: [
'item3'
]
}]
},
...]
I now want to write a query to find the document with a specific container ID (i.e. container1) and return it, while transforming it's selectedItems array to hold the actual object values from the allItems array of the same document.
I want to get this document:
{
id: 1,
name: 'My document 1',
container: {
id: 'container1',
selectedItems: [{
id: 'item3'
name: 'My item 3'
}, {
id: 'item1',
name: 'My item 1'
}
}
}
Currently I have the following aggregation (Node.js):
db.collection.aggregate([{
$match: {'containers.id': 'container1'},
$addFields: {
container: {
$filter: {
input: '$containers',
as: 'container',
cond: {
$eq: ['$$container.id', 'container1']
}
}
}
},
$project: {containers: 0},
$unwind: {path: '$container'}
// I now have a new property "container" with the correct subdocument from the array
// and removed the original array with all containers.
// I now need a stage here to populate the container subdocuments in the `selectedItems` array
$project: {allItems: 0} // remove the list of all items when not needed anymore in a following stage
}]);
I know there is $lookup, but as I want to populate the matching array items from the same document (not a differect collection), I think I don't need it. Even when specifying the same collection in a $lookup, it would populate the array with the root document of my collection and it would get very complex to unwind & match the needed properties of the subdocument.
I thought about making an own items collection, but I would need slower lookups there and my data does not need to have these relations.
I hope my question is clear, I'm thankful for any help I can get!
I'm doing some additional transforms in my real data like copying some properties from the original root document and hiding others, but that should not matter.
Thank you again!
$match your conditions$filter to iterate loop of containers array and filter by id$arrayElemAt to select first element from above filtered result$let to declare a variable and store do above process and store result$filter to iterate loop of allItems array and filter items by above resultdb.collection.aggregate([
{ $match: { "containers.id": "container1" } },
{
$project: {
name: 1,
container: {
$let: {
vars: {
container: {
$arrayElemAt: [
{
$filter: {
input: "$containers",
cond: { $eq: ["$$this.id", "container1"] }
}
},
0
]
}
},
in: {
id: "$$container.id",
selectedItems: {
$filter: {
input: "$allItems",
cond: { $in: ["$$this.id", "$$container.selectedItems"] }
}
}
}
}
}
}
}
])
The second version, you can do process stage by stage,
$match your conditions$filter to iterate loop of containers array and filter by id$arrayElemAt to select first element from above filtered result$filter to iterate loop of allItems array and filter items by selectedItems$$REMOVE will remove fieldsdb.collection.aggregate([
{ $match: { "containers.id": "container1" } },
{
$addFields: {
containers: "$$REMOVE",
container: {
$arrayElemAt: [
{
$filter: {
input: "$containers",
cond: { $eq: ["$$this.id", "container1"] }
}
},
0
]
}
}
},
{
$addFields: {
allItems: "$$REMOVE",
"container.selectedItems": {
$filter: {
input: "$allItems",
cond: { $in: ["$$this.id", "$container.selectedItems"] }
}
}
}
}
])
You are correct by not needing to use $lookup, a series of $map and $filter expressions is all you need.
The approach is simple, we start by filtering the required container, then we iteration over the selected container's selectedItems using $map and match the relevant fields for each item from the allItems field.
{
$match: {
id: 1,
}
},
{
$project: {
name: 1,
container: {
"$arrayElemAt": [
{
$map: {
input: {
$filter: {
input: "$containers",
as: "container",
cond: {
$eq: [
"$$container.id",
"container1"
]
}
},
},
as: "container",
in: {
"$mergeObjects": [
{
id: "$$container.id"
},
{
selectedItems: {
$map: {
input: "$$container.selectedItems",
as: "item",
in: {
"$arrayElemAt": [
{
$filter: {
input: "$allItems",
as: "filter",
cond: {
$eq: [
"$$filter.id",
"$$item"
]
}
}
},
0
]
}
}
}
}
]
}
}
},
0
]
}
}
}
])