How to do MongoDb self join

user_1573670 0
Viewed 15

If I have a collection that is filled with:

db.Dater.insert({"Name" : "Ann", "Gender" : "F", "GenderSearch" : "M"  })
db.Dater.insert({"Name" : "Bob", "Gender" : "M", "GenderSearch" : "F"})
db.Dater.insert({"Name" : "Cat", "Gender" : "F", "GenderSearch" : "F"})

How can I write a query to get all the pairs of names where the key is the Name, the value is a list of Names where value.Gender = key.GenderSearch?

In SQL the desired results would be (although this is not a key to list value)

SELECT d1.Name, d2,Name 
FROM Dater d1
 JOIN Dater d2 ON d1.GenderSearch = d2.Gender
self-join mongodb
1 Answers

You can use $lookup for this:

  1. $lookup with a pipeline to get only the name of matching items
  2. $reduce to flatten the array
db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      let: {localField: "$GenderSearch"},
      pipeline: [
        {$match: {$expr: {$eq: ["$$localField", "$Gender"]}}},
        {$project: {Name: 1, _id: 0}}
      ],
      as: "potentialMatches"
    }
  },
  {
    $set: {
      potentialMatches: {
        $reduce: {
          input: "$potentialMatches",
          initialValue: [],
          in: {$concatArrays: ["$$value", ["$$this.Name"]]}
        }
      }
    }
  }
])

See how it works on the playground example

user_184823100
Related