Operation timeout for a MongoDB aggregation pipeline

user_448662 0
Viewed 732

I have a MongodDB database on MongoDB Atlas.

It has an "orders", "products", "itemTypes" and "brands".

  • "orders" only keep track of product id ordered.
  • "products" only keep track of brand id and itemType id
  • "itemTypes" keep track of item type name
  • "brands" keep track of brand name.

If I aggregate orders + products + itemTypes it is ok:

[{
    $unwind: {
        path: '$orders'
    }
}, {
    $lookup: {
        from: 'products',
        localField: 'orders.productId',
        foreignField: 'productId',
        as: 'products'
    }
}, {
    $lookup: {
        from: 'itemTypes',
        localField: 'products.typeId',
        foreignField: 'typeId',
        as: 'itemTypes'
    }
}, {
    $set: {
        'orders.price': {
            $arrayElemAt: ['$products.price', 0]
        },
        'orders.brandId': {
            $arrayElemAt: ['$products.brandId', 0]
        },
        'orders.typeId': {
            $arrayElemAt: ['$products.typeId', 0]
        },
        'orders.typeName': {
            $arrayElemAt: ['$itemTypes.name', 0]
        }
    }
}, {
    $group: {
        _id: '$_id',
        createdAt: {
            $first: '$createdAt'
        },
        status: {
            $first: '$status'
        },
        retailerId: {
            $first: '$retailerId'
        },
        retailerName: {
            $first: '$retailerName'
        },
        orderId: {
            $first: '$orderId'
        },
        orders: {
            $push: '$orders'
        }
    }
}]

If I aggregate orders + products + itemTypes + brands, either Mongo Compass or the web UI of Mongo Atlas aggregation builder will give operation timeout error.

[{
    $unwind: {
        path: '$orders'
    }
}, {
    $lookup: {
        from: 'products',
        localField: 'orders.productId',
        foreignField: 'productId',
        as: 'products'
    }
}, {
    $lookup: {
        from: 'itemTypes',
        localField: 'products.typeId',
        foreignField: 'typeId',
        as: 'itemTypes'
    }
}, {
    $lookup: {
        from: 'brands',
        localField: 'products.brandId',
        foreignField: 'brandId',
        as: 'brands'
    }
}, {
    $set: {
        'orders.price': {
            $arrayElemAt: ['$products.price', 0]
        },
        'orders.brandId': {
            $arrayElemAt: ['$products.brandId', 0]
        },
        'orders.typeId': {
            $arrayElemAt: ['$products.typeId', 0]
        },
        'orders.typeName': {
            $arrayElemAt: ['$itemTypes.name', 0]
        },
        'orders.brandName': {
            $arrayElemAt: ['$brands.name', 0]
        }
    }
}, {
    $group: {
        _id: '$_id',
        createdAt: {
            $first: '$createdAt'
        },
        status: {
            $first: '$status'
        },
        retailerId: {
            $first: '$retailerId'
        },
        retailerName: {
            $first: '$retailerName'
        },
        orderId: {
            $first: '$orderId'
        },
        orders: {
            $push: '$orders'
        }
    }
}]

This is a demo of the aggregation that timed out:

https://mongoplayground.net/p/Jj6EhSl58MS

We have approximately 50k orders, 14k products, 200 brands, 89 item types.

Is there anyway to optimise this aggregation so that it won't timeout?

P/s: My ultimate goal is to visualise popular brands and item types ordered using beautiful chart in the Mongodb Charts function.

mongodb-atlas mongodb aggregation
2 Answers

Do you have index on the collections you $lookup from: products (productId) + itemTypes (typeId) + brands (brandId). Otherwise, the lookups can take a long time to complete.

user_19592540

If you are on Mongo Atlas, you can use Triggers to run the aggregation query in the background - either when the database is updated or as a scheduled trigger (https://docs.mongodb.com/realm/triggers/).

When the trigger runs, you can save the result of the aggregation pipeline in a new collection using the "$merge" operation.

exports = function() {
    const mongodb = context.services.get(CLUSTER_NAME);
    const orders = mongodb.db(DATABASE_NAME).collection("orders");
    const ordersSummary = mongodb.db(DATABASE_NAME).collection("orders.summary");
    
    const pipeline = [
        {
            YOUR_PIPELINE
        },
        { $merge: { into: "orders.summary", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }
    ];

    orders.aggregate(pipeline);
};

This way, your charts will be very fast, since they only have to do a simple query from the new collection.

user_19592540
Related