How to query all children of an object within a document?

Question

Using the three documents as examples

[
  {
    _id: '/players/c/cruzne02.shtml',
    url: '/players/c/cruzne02.shtml',
    name: 'Nelson Cruz',
    image: 'https://www.baseball-reference.com/req/202108020/images/headshots/f/fea2f131_mlbam.jpg',
    teams: {
      MIL: [ 2005 ],
      TEX: [
        2006, 2007, 2007, 2008,
        2008, 2009, 2009, 2010,
        2010, 2011, 2011, 2012,
        2012, 2013, 2013
      ],
      BAL: [ 2014 ],
      SEA: [
        2015, 2016,
        2016, 2017,
        2017, 2018,
        2018
      ],
      MIN: [ 2019, 2020, 2020, 2021, 2021 ],
      TBR: [ 2021 ]
    }
  },
  {
    _id: '/players/b/berrijo01.shtml',
    url: '/players/b/berrijo01.shtml',
    name: 'Jose Berrios',
    image: 'https://www.baseball-reference.com/req/202108020/images/headshots/d/d94db113_mlbam.jpg',
    teams: {
      MIN: [
        2016, 2017, 2017,
        2018, 2018, 2019,
        2019, 2020, 2020,
        2021, 2021
      ],
      TOR: [ 2021 ]
    }
  },
  {
    _id: '/players/m/mauerjo01.shtml',
    url: '/players/m/mauerjo01.shtml',
    name: 'Joe Mauer',
    image: 'https://www.baseball-reference.com/req/202108020/images/headshots/4/43c69595_mlbam.jpg',
    teams: {
      MIN: [
        2004, 2005, 2005, 2006, 2006,
        2007, 2007, 2008, 2008, 2009,
        2009, 2010, 2010, 2011, 2011,
        2012, 2012, 2013, 2013, 2014,
        2014, 2015, 2015, 2016, 2016,
        2017, 2017, 2018, 2018
      ]
    }
  }
]

Say I wanted to get the two players who played in 2010 which would be the first and third documents. How do I write the find when being provided with the year I'm looking for to get a player. I tried looking for wildcards and ways to query through all children of an object but in this case the keys aren't always matching.

Like for 2010 it would be TEX: 2010 for the first document and MIN: 2010 for the third. Can anyone help me?

Answer 1

Query

• convert teams to array to have

  [["MIL", [2005] ["TEX": [2006,2007,...]] ...]
  

• reduce starting with false, and checking if 2010 is member in the second member of any of those array [2005],[2006,2007,...] etc if it is member at least in 1 of those arrays, then reduce returns true, and the document passes the match stage

• works with 1 match stage, and its fast

*you can avoid using $$result and $$t but i used them to give names to the reduce arguments, you can remove them.

Test code here

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$reduce": {
          "input": {
            "$map": {
              "input": {
                "$objectToArray": "$teams"
              },
              "as": "m",
              "in": [
                "$$m.k",
                "$$m.v"
              ]
            }
          },
          "initialValue": false,
          "in": {
            "$let": {
              "vars": {
                "result": "$$value",
                "t": "$$this"
              },
              "in": {
                "$cond": [
                  {
                    "$or": [
                      "$$result",
                      {
                        "$in": [
                          2010,
                          {
                            "$arrayElemAt": [
                              "$$t",
                              1
                            ]
                          }
                        ]
                      }
                    ]
                  },
                  true,
                  false
                ]
              }
            }
          }
        }
      }
    }
  }
])

Test code here

Query (almost same as the above, without any extra variables etc)

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $reduce: {
          input: {
            $objectToArray: "$teams"
          },
          initialValue: false,
          in: {
            $cond: [
              {
                $or: [
                  "$$value",
                  {
                    $in: [
                      2010,
                      "$$this.v"
                    ]
                  }
                ]
              },
              true,
              false
            ]
          }
        }
      }
    }
  }
])