Aggregate: Replacing an array of IDs with the corresponding objects from an array in the same document

[phe (Dennis Wissel)]

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 items 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!

[giulianocelani]

Hi @phe,

You are definitely on the right track!

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 items array of the same document.

As you already complete a similar process with your initial $addFields stage to find the container, all that needs to be done is to deconstruct the embedded array of container.selectedItems in order to match each individual item with its corresponding id found in container.selectedItems.

So with saying that, I’ve broken the additional stages into two parts.

1. First we need to deconstruct the container.selectedItems so that we can evaluate each selectedItem in container with the allItems array at the root of the document.

{
    $unwind: {
        path: "$container.selectedItems",
    }
},
{
    $addFields: {
        "container.selectedItems": {
            $arrayElemAt: [
                {
                    $filter: {
                        input: "$allItems",
                        as: "item",
                        cond: { $eq: ["$$item.id", "$container.selectedItems"] },
                    },
                },
                0,
            ],
        },
    }
}

2. Next we have to group all the respective items together which we deconstructed so its back to its original structure. To conclude the aggregation with your expected output, we can tidy that up with the $project stage.

{
    $group: {
        _id: {
            doc: "$_id",
            container: "$container.id",
        },
        name: {
            $first: "$name",
        },
        selectedItems: {
            $push: "$container.selectedItems",
        },
    },
},
{
    $project: {
        _id: "$_id.doc",
        name: "$name",
        container: {
            id: "$_id.container",
            selectedItems: "$selectedItems",
        },
    },
}

Please note that the projection stage I’ve added removes the need for the two $project stages you have in your existing pipeline.

I hope this helps

Cheers,
Giuliano