MongoDB - query ObjectId nested array

Question

I have a collection named Product on MongoDB with some documents, here is an example:

{
    _id: 'xxxxxx',
    name: 'cellphone',
    brands: [
        'aaaaaa',
        'bbbbbb'
    ]
}

The 'brands' key makes reference to another collection named Brand, example:

[
    {
        _id: 'aaaaaa',
        name: 'Apple',
        _deprecatedDate: null
    },
    {
        _id: 'bbbbbb',
        name: 'BlackBerry',
        _deprecatedDate: '2016-07-13T02:27:17.724Z'
    }
]

So, with a Product id, I want to get all it's non-deprecated brands. The only way I found to do that is with the following code:

let _product = await Product.findOne({ _id: 'xxxxxx' });
return Brand.find({ _id: { $in: _product.brands },  _deprecatedDate: null });

Is there a way to do that with one single query?

Answer 1

You can use .aggregate() and $lookup to fetch the data from multiple collections. You can either specify custom pipeline (MongoDB 3.6):

Product.aggregate([
    {
        $lookup: {
            from: "Brand",
            let: { brands: "$brands" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                             { $in: [ "$_id",  "$$brands" ] },
                             { $eq: [ "$_deprecatedDate", null ] }
                           ]
                        }
                    }
                }
            ],
            as: "brands"
        }
    }
])

or just use $lookup with $filter in next stage to filter out deprecated brands:

Product.aggregate([
    {
        $lookup: {
            from: "Brand",
            localField: "brands",
            foreignField: "_id",
            as: "brands"
        }
    },
    {
        $addFields: {
            brands: {
                $filter: {
                    input: "$brands",
                    as: "brand",
                    cond: {
                        $eq: [ "$$brand._deprecatedDate", null ]
                    }
                }
            }
        }
    }
])