I've tried to read around before post this question but wasn't able to figure this out.

I'm trying to return only the first element that matches a given condition from a query in my database.

The collection documents are like this:

{
    "key": 1,
    timestamps: {
      history: [
        {
          time: 10
        },
        {
          time: 9
        },
        {
          time: 8
        },
        
      ]
    }
  },

And I want to retrieve for example the first element inside the history array with a timestamp lower than 10.

So the expected result should be something like this:

[
  {
    "timestamps": {
      "history": [
        {
          "time": 9
        }
      ]
    }
  }
]

You can find an example into this playground https://mongoplayground.net/p/jHKb9o-0tHm .

So far I'm able to retrieve all the elements inside the array that are lower than 10 but not only the first match, how can I accomplish that?

Also since I'm trying to make it as efficient as possible, is it faster to retrieve only the first element for the db or it is only an additional operation to do for the database because the operation of returning only first element is done after that the db already found all the elements?

CodePudding user response：

The array field "timestamps.history" is a nested field and to get the first matching element using $elemMatch projection operator fails with an error: "Cannot use $elemMatch projection on a nested field.".

In this case use the Positional Projection Operator $ as shown below to get the desired result.

db.collection.find(
 {
   key: 1, "timestamps.history.time": { $lt: 10 }
 },
 {
   "timestamps.history.$": 1
 }
)