Array of objects Max value

[sivanzakay (sivan)]

According the documents below :
How do I get all documents that
field driver.vehicle.make = Powell Motors" and this field has the max year in the array ?
(It should return only doc 1 and doc 3)
I would like it will be executed in one query

doc 1 :

"driver": {
  "last_name": "McQueen",
  "vehicle": [
    {
      "make": "Powell Motors",
      "model": "Canyonero",
      "year": 2020
    },
    {
      "make": "Miller-Meteor",
      "model": "Ecto-1",
      "year": 2005
    }
  ]
}

doc 2 :

 "driver": {
  "last_name": "McQueen",
  "vehicle": [
    {
      "make": "Powell Motors",
      "model": "Canyonero",
      "year": 1999
    },
    {
      "make": "Miller-Meteor",
      "model": "Ecto-1",
      "year": 2018
    }
  ]
}

doc 3 :

"driver": {
"last_name": "McQueen",
"vehicle": [
  {
    "make": "Powell Motors",
    "model": "Canyonero",
    "year": 2003
  },
  {
    "make": "Miller-Meteor",
    "model": "Ecto-1",
    "year": 1998
  }
]}

[sivanzakay (sivan)]

I'll appreciate your help.
Thanks
Sivan

[aaron-nimocks]

Can you explain field has the max year in the array part? Not sure why document 1 and 3 would be returned or what you are looking for.

[sivanzakay (sivan)]

Hi Aaron ,
Let's look at the array of "vehicle" objects for each document.
I want to get all documents in which has object that meets the following conditions :
Field "year" of that object is maximum in the array and the Field "make" of this object == "Powell Motors"

doc 1 : Max year is 2020 (>2005) .
The object with the max year is :

{
"make": "Powell Motors",
"model": "Canyonero",
"year": 2020
}

Field "make" of this object equals to "Powell Motors" (conditions are met)

doc 2 : Max year is 2018.
The object with the max year is :

{
"make": "Miller-Meteor",
"model": "Ecto-1",
"year": 2018
}

Field "make" of this object equals to "Miller-Meteor". (Does not meet the requirement. Object "make" field equals to "Miller-Meteor" and not "Powell Motors" as required)

doc 3: Max year is 2003.
The object with the max year is :

{
"make": "Powell Motors",
"model": "Canyonero",
"year": 2003
}
Field "make" of this object equals to "Powell Motors"(conditions are met)

Thanks,

Sivan