Filter based on a Sub-Sub-Array

[Dan_Burt]

My document has an array of arrays and I would like to filter based on matching 1 value from the lowest arrays.

The basic structure of my document is (there are other fields but they aren’t required to demonstrate):

{
  "_id": {
    "$oid": "60701a691c071256e4f0d0d6"
  },
  "schema": {
    "$numberDecimal": "1.0"
  },
  "playerName": "Dan Burt",
  "comp": {
    "id": {
      "$oid": "607019361c071256e4f0d0d5"
    },
      "name": "Roll Up 2021",
      "tees": "Blue",
      "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 UI, it looks like this:

The holes array is made up of another array per hole played.

I would like to filter only the round's where holes.X.holeGross equals 2 (i.e. made a birdie on a par 3 for those familiar with golf) for a specific comp._id and comp.roundNo.

But I don’t want to loop through and process this. I think an aggregation query or pipeline can be used, but I cannot fathom how to use them, or even how to begin typing stuff into the Atlas UI to start test queries…

I am reading the documentation, found some StackOverflow articles and watched a few Youtube videos, but I am struggling to replicate for my particular situation of this additional sub-array.

[Dan_Burt]

In the Atlas UI, when viewing the documents, I can apply this filter to the round collection, which finds matching documents for the 1st hole (0 index) and the holeGross equals 4:

{"holes.0.holeGross": 2}

But how do I apply this across all sub-arrays of holes?

[Dan_Burt]

Think I have worked out the comp filters should be:

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

Is this my first pipeline “stage”?

[MaxOfLondon]

Hi Dan,

I am not sure I fully understand if you would like to match documents having holeGross with value 2 in any element of holes array?

To start with in screenshot you provided the holes is not an array but an object that complicates things.

If it was an array this could be approached with $elemMatch array operator making it quite simple like
db.games.find({"holes": {"$elemMatch": {"holeGross" : 2}}}) which should return all documents from games collection that have at least one holeGross of 2

[Dan_Burt]

Thanks again @MaxOfLondon - you have pinpointed the problem… wrong data types!

When doing the initial data modelling, holes was intended to be an Array of Objects. Somehow with other coding (translating this through PHP currently), these documents were being saved as Object of Objects, which meant the standard querying methods weren’t working.

Using the Atlas UI, I can filter the matching documents with the simple / standard syntax:

{ "holes.holeGross": { $lte: 2  } }

Which doesn’t require any aggregation.