How to $lookup with case sensitive values localField, in mongoDB V6

Working with Data
#1

I have two collection and need to performs a left outer join on it. With $lookup is works good but the problem is that the collections come from some API where the value of localField is in lower case and foreignField is in upper case.

How to use $lookup to ignore or to lowerCase the value(s)? I try something but I have no chance. I found a solution but is for a older version of mongodb. If you have any experience please help :slight_smile:

db.mycollection.aggregate([
  {
    $lookup: {
      from: 'mycollection',
      localField: 'symbol',
      foreignField: 'index_id',
      as: 'foo',
    },
  },
]);
#2

this can be achieved automated way supporting both case with out touching the data.
Create Case Insensitive index and then try to match it.

//Case Insensitive ID collection 

db.createCollection("caseinsensitive_up2", {
    collation: {
        "locale": "en",
        "strength": 1
    }
})

db.caseinsensitive_up2.find({})

db.caseinsensitive_up2.save({ _id: "RAMKUMAR", name: "ramkumar" })
db.caseinsensitive_up2.save({ _id: "ramkumar", name: "ramkumar232" })
db.caseinsensitive_up2.save({ _id: "rAmKumar", name: "ramkumar" })

db.caseinsensitive_up2.find({ _id: 'rAmKumar' })

db.caseinsensitive_up2.update(
    { _id: 'RAmKuMaR' },
    { name: "ramkumar232" },
    { upsert: true }
)
#3

The solution:
https://stackoverflow.com/questions/75961851/how-to-lookup-with-case-sensitive-values-localfield-in-mongodb-v6/75961969#75961969

#4

pretty over complicated :slight_smile: