CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Luis Paulo
Luis Paulo

Reputation: 129

Match array elements against a document value and filter

I have a collection of documents that have the following structure: enter image description here

Both leagues and entries are arrays that I unwind(), but then I need to find the occurrence where the document's property "nick" is showed on "playerOrTeamName".

db.getCollection('summoners').aggregate([
    {"$skip": 0},
    {"$limit": 2},
    {'$unwind': '$leagues'},
    {'$unwind': '$leagues.entries'},
    {'$match': {'leagues.entries.playerOrTeamName': '$nick'}},
],{

allowDiskUse:true })

Why will the "match" portion result in 0 results? I can assure that the player's nick will always occur on entries array.

PS: limit = 2 used for simplicity sakes

Upvotes: 1

Views: 256

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151170

The reason this fails is because "$nick" is meant to be the value of another field, but $match which is basically just a "regular MongoDB query" has no concept of "using a variable" from an existing field value in order to "match" on the condition.

So instead you should be using "aggregation logical operators" which apply using either the $redact pipeline stage, or instead by "filtering" the array content directly using $filter.

db.getCollection('summoners').aggregate([
    {"$skip": 0},
    {"$limit": 2},
    {'$unwind': '$leagues'},
    {'$unwind': '$leagues.entries'},
    {'$redact': {
      '$cond': {
        'if': { '$eq': [ '$leagues.entries.playerOrTeamName', '$nick' ] }
        'then': '$$KEEP',
        'else': '$$PRUNE'
      }
    }
])

Which performs a "logical" comparison on the field values and decides to "$$KEEP" where the condition is true or "$$PRUNE" from results where it was false.

Or directly on the array, keeping intact:

db.getCollection('summoners').aggregate([
    { "$skip": 0 },
    { "$limit": 2 },
    { "$addFields": {
      "leagues": {
        "$filter": {
          "input": {
            "$map": {
              "input": "$leagues",
              "as": "l",
              "in": {
                "entries": {
                  "$filter": {
                    "input": "$$l.entries",
                    "as": "e",
                    "cond": { "$eq": [ "$$e.playerOrTeamName", "$nick" ] }
                  }
                },
                "name": "$$l.name",
                "queque": "$$l.queque",
                "tier": "$$l.tier"
              }
            }
          },
          "as": "l",
          "cond": {
            "$gt": [ { "$size": "$$l.entries" }, 0 ]
          }
        }
      }
    }}
])

Which essentially re-maps the arrays by applying $filter to the inner "entries" for the comparison of fields, and where the "outer" array no longer has any results left in "entries" that array would be removed as well.

Upvotes: 1

Related Questions