Full Outer join in MongoDB

Question

I want to do a Full Outer Join in MongoDB by lookup mongoDB query. Is this possible? Is a Full Outer Join supported by MongoDB by any other alternative?

[Update:]

I want to achieve result from Collection1 & Collection2 as following attachment:

Example: Result Required

In above result column there may be different arithmetic operations and will be further used in calculations.

Not yet. Wait for MongoDB 3.6 where you can do an "non-correlated" `$lookup`. But rather than say "you need it" you should instead be justifying "why". Because even with the facility to "join" you will get far better application performance by designing in such a way where no such thing is required. — Neil Lunn, Aug 22 '17 at 05:54
Hello Neil, I have attached sample collections and required result. How can I achieve that? — Arp Nayee, Aug 22 '17 at 08:34

score 4 · Answer 1 · answered Mar 26 '21 at 15:03

You can use $unionWith (starting 4.4) Something like this:

db.c1.aggregate([
{$set: {
  mark1: "$marks"
}}, 
{$unionWith: {
  coll: 'c2',
  pipeline: [{$set: {mark2: "$marks"}}]
}}, 
{$group: {
  _id: "$name",
  result: {
    $sum: "$marks"
  },
  mark1: {$first: {$ifNull: ["$mark1", 0]}},
  mark2: {$first: {$ifNull: ["$mark2", 0]}}
}}])

score 2 · Answer 2 · answered Jan 30 '20 at 15:38

I have named the collections as coll1 and coll2 then just use this query it will give you the required output.

db.getCollection('coll1').aggregate([
    {
        $facet: {
            commonRecords: [{
                    $lookup: {
                        from: "coll2",
                        localField: 'name',
                        foreignField: 'name',
                        as: "coll2"
                    }
                },
                {
                    $unwind: {
                        path: '$coll2',
                        preserveNullAndEmptyArrays: true
                    }
                }
            ]
        }
    },
    {
        $lookup: {
            from: "coll2",
            let: {
                names: {
                    $map: {
                        input: '$commonRecords',
                        as: 'commonRecord',
                        in: '$$commonRecord.name'
                    }
                }
            },
            pipeline: [{
                $match: {
                    $expr: {
                        $eq: [{
                            $indexOfArray: ['$$names', '$name']
                        }, -1]
                    }
                }
            }, ],
            as: "coll2"
        }
    },
    {
        $addFields: {
            coll2: {
                $map: {
                    input: '$coll2',
                    as: 'doc',
                    in: {
                        coll2: '$$doc'
                    }
                }
            }
        }
    },
    {
        $project: {
            records: {
                $concatArrays: ['$commonRecords', '$coll2']
            }
        }
    },
    {
        $unwind: '$records'
    },
    {
        $replaceRoot: {
            newRoot: '$records'
        }
    },
    {
        $project: {
            _id: 0,
            name: {
                $ifNull: ['$name', '$coll2.name']
            },
            marks1: {
                $ifNull: ['$marks', 0]
            },
            marks2: {
                $ifNull: ['$coll2.marks', 0]
            }
        }
    },
    {
        $addFields: {
            result: {
                $add: ['$marks1', '$marks2']
            }
        }
    }
])

I think adding proper explanation also will help viewers – wrangler Jan 31 '20 at 07:02 — wrangler, Jan 31 '20 at 07:02

score 0 · Answer 3 · answered Apr 13 '23 at 09:42

It is supported only after version 4.4 to $unionWith.

db.col1.aggregate([
  { $lookup: { 
      from: "col2", 
      localField: "name",
      foreignField: "name", 
      as: "detail" 
  }},
  { $unwind: { 
      path: "$detail", 
      preserveNullAndEmptyArrays: true
  }}, 
  { $project: { 
      name: 1,
      marks: "$marks",
      marks2: "$detail.marks" 
   }},
   { $unionWith: {
     coll: "col2",
     pipeline: [{
       $addFields: { marks: 0, marks2: "$marks" }
     }]
   }},
   { $group: { 
     _id: "$name", 
     marks:{ $first: "$marks" }, 
     marks2: { $first: "$marks2"},
   }},
   { $project: { 
      _id: 1,
      marks: 1,
      marks2: {$ifNull: ["$marks2", 0]},
      total: {$add: [ {$ifNull: ["$marks", 0]}, {$ifNull: ["$marks2", 0]} 
     ]},
   }}
])

score -2 · Answer 4 · answered Jul 20 '20 at 12:13

This is a sample:

    {
       $lookup:
         {
           from: [collection to join],
           local_Field: [field from the input documents],
           foreign_Field: [field from the documents of the "from" collection],
           as: [output field]
         }
    }

show this link

Full Outer join in MongoDB

4 Answers4