Hello i am new to MongoDB and hope someone can help me with my problem. I have a collection with the following document structure:

    // collection: avgsalarydep
{
    "_id" : ObjectId("60509a82f5034f42e0e0f909"),
    "id" : "zufferli-j",
    "firstname" : "Mark",
    "middlename" : "Clemens ",
    "lastname" : "Zufferli",
    "works_in" : "F",
    "salary" : 50000,
    "email_address" : "[email protected]",
    "avgjoin" : [
        {
            "_id" : "F",
            "averagesalary" : 64833
        }
    ]
}

Now i simply want to write a find query which shows all the people where their salary is lower than the averagesalary. I tried for example things like:

.find({$expr:{$lt:["$salary", "$avgjoin.averagesalary"]}})

.find({$expr:{$lt:["$salary", "avgjoin.averagesalary"]}})

{$match:{ $expr: { $lt: { "$salary" , "$avgjoin.averagesalary" } }} }

But either it just returns all the people (ignoring if they earn less) or says "No records found". What am i doing wrong? If you need more clarification please feel free to ask. Help would be much appreciated.

CodePudding user response：

Something like this works:

db.avgsalarydep.find({
  $expr: {
    $lt: [
      "$salary",
      {
        $min: "$avgjoin.averagesalary"
      }
    ]
  }
})

Working Mongo playground

I used the $min aggregation operator in case there can be more than one document in the avgjoin array.

I noticed that in your example document, there's only one element in avgjoin, which has the same _id as your document's works_in field. If this is always the case, updating the document so that there is a single field equivalent to avgjoin.averagesalary would allow something like your original attempt to work.