$lookup (joins)

// 1) Simple lookup — join orders to customers
db.orders.aggregate([
    { $lookup: {
        from:         'customers',
        localField:   'customerId',
        foreignField: '_id',
        as:           'customer',
    } },
    { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } },
    { $project: { _id: 1, total: 1, status: 1, customerName: '$customer.name' } },
]);

// 'as' creates an ARRAY of matches; unwind flattens.

// 2) Pipeline form — richer join
db.orders.aggregate([
    { $lookup: {
        from: 'customers',
        let:  { custId: '$customerId', country: '$shippingAddress.country' },
        pipeline: [
            { $match: { $expr: {
                $and: [
                    { $eq: ['$_id', '$$custId'] },
                    { $eq: ['$country', '$$country'] },
                ],
            } } },
            { $project: { _id: 0, name: 1, email: 1 } },
        ],
        as: 'customer',
    } },
]);

// Multiple conditions, projection, sub-pipeline filters — much more powerful than localField/foreignField.

// 3) Many-to-many via array field
// posts.tags = ['tagA', 'tagB']; tags collection has documents with _id matching tag name
db.posts.aggregate([
    { $lookup: {
        from:         'tags',
        localField:   'tags',
        foreignField: '_id',
        as:           'tagDocs',
    } },
]);
// tagDocs becomes an array of full tag documents.

// 4) Aggregation inside lookup — count related, find latest
db.users.aggregate([
    { $lookup: {
        from: 'orders',
        let:  { uid: '$_id' },
        pipeline: [
            { $match: { $expr: { $eq: ['$userId', '$$uid'] } } },
            { $group: { _id: null, total: { $sum: '$amount' }, count: { $sum: 1 } } },
        ],
        as: 'orderStats',
    } },
    { $addFields: {
        totalSpent: { $ifNull: [{ $arrayElemAt: ['$orderStats.total', 0] }, 0] },
        orderCount: { $ifNull: [{ $arrayElemAt: ['$orderStats.count', 0] }, 0] },
    } },
    { $project: { orderStats: 0 } },
]);

// 5) Lookup + sort + limit (top N per parent)
db.users.aggregate([
    { $lookup: {
        from: 'orders',
        let:  { uid: '$_id' },
        pipeline: [
            { $match: { $expr: { $eq: ['$userId', '$$uid'] } } },
            { $sort:  { createdAt: -1 } },
            { $limit: 5 },
        ],
        as: 'recentOrders',
    } },
]);

// 6) Nested lookups
db.orders.aggregate([
    { $lookup: { from: 'customers', localField: 'customerId', foreignField: '_id', as: 'customer' } },
    { $unwind: '$customer' },
    { $lookup: { from: 'addresses', localField: 'customer.addressId', foreignField: '_id', as: 'address' } },
    { $unwind: '$address' },
]);

// 7) $graphLookup — recursive joins (org charts, comments)
db.employees.aggregate([
    { $match: { name: 'CEO' } },
    { $graphLookup: {
        from:             'employees',
        startWith:        '$_id',
        connectFromField: '_id',
        connectToField:   'managerId',
        as:               'reports',
        maxDepth:         5,
        depthField:       'level',
    } },
]);

// 8) Performance considerations
// • Index the foreignField (or matched fields in pipeline form) — without it, FULL COLLECTION SCAN per parent
// • Restrict the foreign collection with a $match early in the pipeline
// • Project only fields you need — return less data
// • For very large fanouts, consider denormalising or computing in app code
// • Watch the result size — joined documents can blow past 16 MB document limit

// 9) Sharded collections
// $lookup against a sharded foreign collection works but may be slower; consider the routing implications
// $lookup from a sharded source — restrictions apply pre-MongoDB 5.0; check docs for your version

// 10) Lookup + project to flatten
db.orders.aggregate([
    { $lookup: { from: 'customers', localField: 'customerId', foreignField: '_id', as: 'customer' } },
    { $project: {
        _id: 1,
        total: 1,
        customerName:  { $arrayElemAt: ['$customer.name', 0] },
        customerEmail: { $arrayElemAt: ['$customer.email', 0] },
    } },
]);

// 11) When NOT to use $lookup
// • Tight read paths needing low latency — denormalise common fields
// • Frequently-joined data — duplicate critical fields onto the source
// • Cross-shard joins — consider data redesign
// • Real-time chat / streaming — use change streams + app logic

// 12) Common bugs
// • Forgot $unwind after lookup → field is array even if 0 or 1 match
// • Used localField with EMBEDDED fields — only top-level paths supported; project first
// • foreignField type mismatch (string vs ObjectId) → no matches
// • No index on foreignField → 10× slower at scale; create index explicitly
// • Joined collection > 16 MB → operation aborts; filter inside the pipeline
// • $lookup as the FIRST stage → very expensive; $match first, then $lookup
// • preserveNullAndEmptyArrays missing → unwind drops rows without matches
// • Trying to join across databases — $lookup only works within one DB
// • Stale results — joins are computed live; cache if needed

// SQL-style join
{ $lookup: {
    from: 'customers',
    localField: 'customer_id',
    foreignField: '_id',
    as: 'customer',
} }