Home > OS >  Project and filter using elemMatch on child elements
Project and filter using elemMatch on child elements

Time:12-10

This is the content of my collection:

[{
  "_id": {
    "$oid": "61b253a89e51973f978b818d"
  },
  "id": 1,
  "level1": [
    {
      "name": "child11",
      "level2": [
        {
          "name": "chil21"
        },
        {
          "name": "chilxx"
        }
      ]
    },
    {
      "name": "child12",
      "level2": [
        {
          "name": "chil23"
        },
        {
          "name": "chil24"
        }
      ]
    }
  ]
},{
  "_id": {
    "$oid": "61b254b69e51973f978b818e"
  },
  "id": 2,
  "level1": [
    {
      "name": "child13",
      "level2": [
        {
          "name": "chil25"
        },
        {
          "name": "chil26"
        }
      ]
    },
    {
      "name": "child14",
      "level2": [
        {
          "name": "chil27"
        },
        {
          "name": "chil28"
        },
        {
          "name": "chilxx"
        },
        {
          "name": "chil2a"
        }
      ]
    }
  ]
}]

I now want to aggregate such that

  • the aggregated documents have the same structure as the original documents in the collection
  • the level1 Array only contains elements that itself contain a level2 element with name = chilxx

The expected aggregation output should therefore be:

[{
  "_id": {
    "$oid": "61b253a89e51973f978b818d"
  },
  "id": 1,
  "level1": [
    {
      "name": "child11",
      "level2": [
        {
          "name": "chil21"
        },
        {
          "name": "chilxx"
        }
      ]
    }
  ]
},{
  "_id": {
    "$oid": "61b254b69e51973f978b818e"
  },
  "id": 2,
  "level1": [
    {
      "name": "child14",
      "level2": [
        {
          "name": "chil27"
        },
        {
          "name": "chil28"
        },
        {
          "name": "chilxx"
        },
        {
          "name": "chil2a"
        }
      ]
    }
  ]
}]

Here is my (not working) aggregation approach:

[
  {
    '$match': {}
  }, {
    '$project': {
      '_id': 1, 
      'id': 1, 
      'level1': {
        '$filter': {
          'input': '$level1', 
          'as': 'l1', 
          'cond': {
            '$$l1.level2': {
              '$elemMatch': {
                'name': 'chilxx'
              }
            }
          }
        }
      }
    }
  }
]

However, this causes an error:

Invalid $project :: caused by :: Unrecognized expression '$$l1.level2'

Thank you in advance.

CodePudding user response:

  • $match
  • $set
db.collection.aggregate([
  {
    "$match": {
      "level1.level2.name": "chilxx"
    }
  },
  {
    "$set": {
      "level1": {
        "$filter": {
          "input": "$level1",
          "as": "first",
          "cond": {
            $ne: [
              {
                "$filter": {
                  "input": "$$first.level2",
                  "as": "second",
                  "cond": {
                    $eq: [
                      "$$second.name",
                      "chilxx"
                    ]
                  }
                }
              },
              []
            ]
          }
        }
      }
    }
  }
])

mongoplayground

  • Related