0

I have the following query, what I want is to change field value to upper and use in $match condition.

db.getCollection('jobpostings').aggregate([
{"$match":{
    "expireDate":{"$gt": ISODate("2018-05-20T08:38:58.812Z")},
    "publishDate":{"$lt": ISODate("2018-05-22T00:00:00.000Z")},
    "isPublished":true,"isDrafted":false,
    "deletedAt":{"$eq":null},"deleted":false,
    "blocked":{"$exists":false},
    "locations":{"$in":["BALKH"]}
}},
{"$lookup":{
    "from":"companies","localField":"company.id","foreignField":"_id","as":"companyDetails"
}},
{"$match":{"companyDetails":{"$ne":[]}}},
{"$sort":{"isFeatured":-1,"publishDate":-1}},
{"$skip":0},{"$limit":12},
{"$project":{
    "position":1,"summary":1,"company":1,"publishDate":1,"expireDate":1,"locations":1,"minimumEducation":1,
    "workType":1,"skills":1,"contractType":1,"isExtensible":1,"salary":1,"gender":1,"yearsOfExperience":1,

}}

} ])

I want to use $toUpper with this line "locations":{"$in":["BALKH"]}, need to change value of locations field to upper and then check inside array.

jones
  • 1,423
  • 3
  • 35
  • 76
  • Whilst you "could" ( if you actually have MongoDB 3.6 ) it's still not a good idea. There are far better and more efficient ways to search your collection without an absolute match on the "case". Basically "anchored regex" `{ "$in": [/^BALKH$/i] }` or better would be ["collation"](https://stackoverflow.com/a/40914924/2313887) – Neil Lunn May 21 '18 at 09:33
  • @NeilLunn I have tried what you suggested, but my array is a dynamic array, and when I push element to array, all array elements changes to string and goes inside double quotes, so query return no result. – jones May 22 '18 at 04:58
  • You mean `"locations": { "$in": dynamic }`? Where `dynamic` is a list you build? Then simply use something like a `.map()`. i.e `dynamic = dynamic.map(e => new RegExp('^'+e+'$','i'))`. But really you should be adding the "collation" option to the pipeline options as long as your MongoDB version supports it. – Neil Lunn May 22 '18 at 05:05
  • @NeilLunn I used collation like `{collation:{locale: "en", strength: 2}}`, at the end of aggregate pipeline, after closing `]`, but it not differ. – jones May 22 '18 at 06:10
  • You need a supporting version. MongoDB 3.4 at least. These things are all described on the linked duplicate questions, which are actually there for you to read. If you cannot use collation then use the regex. Forcing projection before a match is just a bad idea as the whole collection needs to be transformed in the pipeline. – Neil Lunn May 22 '18 at 06:13

0 Answers0