MongoDB filter a sub-array of Objects

Question

The document structure has a round collection, which has an array of holes Objects embedded within it, with each hole played/scored entered.

The structure looks like this (there are more fields, but this summarises):

{
  "_id": {
    "$oid": "60701a691c071256e4f0d0d6"
  },
  "schema": {
    "$numberDecimal": "1.0"
  },
  "playerName": "T Woods",
  "comp": {
    "id": {
      "$oid": "607019361c071256e4f0d0d5"
    },
      "name": "US Open",
      "tees": "Pro Tees",
      "roundNo": {
        "$numberInt": "1"
      },
    "scoringMethod": "Stableford"
  },
  "holes": [
    {
      "holeNo": {
        "$numberInt": "1"
      },
      "holePar": {
        "$numberInt": "4"
      },
      "holeSI": {
        "$numberInt": "3"
      },
      "holeGross": {
        "$numberInt": "4"
      },
      "holeStrokes": {
        "$numberInt": "1"
      },
      "holeNett": {
        "$numberInt": "3"
      },
      "holeGrossPoints": {
        "$numberInt": "2"
      },
      "holeNettPoints": {
        "$numberInt": "3"
      }
    }
  ]
}

In the Atlas web UI, it shows as (note there are 9 holes in this particular round of golf - limited to 3 for brevity):

I would like to find the players who have a holeGross of 2, or less, somewhere in their round of golf (i.e. a birdie on par 3 or better).

Being new to MongoDB, and NoSQL constructs, I am stuck with this. Reading around the aggregation pipeline framework, I have tried to break down the stages I will need as:

Filter by the comp.id and comp.roundNo
Filter this result with any hole within the holes array of Objects

Maybe I have approached this wrong, and should filter or structure this pipeline differently?

So far, using the Atlas web UI, I can apply these filters individually as:

{
  "comp.id": ObjectId("607019361c071256e4f0d0d5"),
  "comp.roundNo": 2
}

And:

{ "holes.0.holeGross": 2 }

But I have 2 problems:

The second filter query, I have hard-coded the array index to get this value. I would need to search across all the sub-elements of every document that matches this comp.id && comp.roundNo
How do I combine these? I presuming this is where the aggregation comes in, as well as enumerating across the whole array (as above).

I note in particular it is the extra ".0." part of the second query that I am not seeing from various other online postings trying to do the same thing. Is my data structure incorrect? Do I need the [0]...[17] Objects for an 18-hole round of golf?

user_4368485 · Answer

I would like to find the players who have a holeGross of 2, or less, somewhere in their round of golf

if that is the goal, a simple $lte search inside the holes array like the following would do:

db.collection.find({ "holes.holeGross": { $lte: 2  } })

you simply have to not specify an array index such as 0 in the property path in order to search each element of the array.

https://mongoplayground.net/p/KhZLnj9mJe5

MongoDB filter a sub-array of Objects

1 Answers