MongoDB Compass: How to filter embedded array object in the MongoDB document

Question

I want to filter the documents based on importkeyid and MissingPersonIds.PhotoId array field. The filter on MissingPersonIds.PhotoId should work like 'sql - like' operator.

Schema:

[{
  "_id": {
    "$oid": "61ada7da9a30fd8471869bbc"
  },
  "ImportKeyId": 5843,
  "Name" : "AV"
  "MissingPersonIds": [
    {
      "PhotoId": "2 - Copy.jpg",
      "Description": "Account ID not found"
    },
    {
      "PhotoId": "2 - Copy - Copy.jpg",
      "Description": "Account ID not found"
    },
    {
      "PhotoId": "2 - Copy - Copy (2).jpg",
      "Description": "Account ID not found"
    },
    {
      "PhotoId": "202020 - Copy (2).jpg",
      "Description": "Account ID not found"
    },
    {
      "PhotoId": "202020 - Copy - Copy.jpg",
      "Description": "Account ID not found"
    },
    {
      "PhotoId": "202020 - Copy - Copy (2).jpg",
      "Description": "Account ID not found"
    }
  ]
},
{
  "_id": {
    "$oid": "619cd7d2181999c9a4da790a"
  },
  "ImportKeyId": 5753,
  "Name" : 'av1'
  "MissingPersonIds": [
    {
      "PhotoId": "94578.jpg",
      "Description": "Photo id is not found"
    },
    {
      "PhotoId": "371426759.jpg",
      "Description": "Photo id is not found"
    }
  ]
}
.
.
.
]

Based on following conditions:

Filter: { "ImportKeyId" : 5843 }

Projection: { MissingPersonIds : { $slice:[0,5] }, "MissingPersonIds": { $elemMatch: { PhotoId : { $regex: /202020 /i } } } }

I was expecting below output but $elemMatch returns only one matched record

[{
  "_id": {
    "$oid": "61ada7da9a30fd8471869bbc"
  },
  "ImportKeyId": 5843,
  "Name" : "AV"
  "MissingPersonIds": [  
    {
      "PhotoId": "202020 - Copy (2).jpg",
      "Description": "Account ID not found"
    },
    {
      "PhotoId": "202020 - Copy - Copy.jpg",
      "Description": "Account ID not found"
    }
  ]
}]

What should I used instead of $elemMatch ?

Answer 1

Maybe something like this

db.collection.aggregate([
  {
    "$match": {
      "ImportKeyId": 5843
   }
  },
 {
"$project": {
  "_id": 1,
  "ImportKeyId": 1,
  MissingPersonIds: {
    $filter: {
      input: "$MissingPersonIds",
      as: "item",
      cond: {
        $gt: [
          {
            $indexOfCP: [
              {
                $toLower: "$$item.PhotoId"
              },
              "202020 "
            ]
          },
          -1
        ]
      }
    }
  }
  }
  }
 ])

Output:

[
  {
    "_id": ObjectId("61ada7da9a30fd8471869bbc")
    "ImportKeyId": 5843,
    "MissingPersonIds": [
  {
    "Description": "Account ID not found",
    "PhotoId": "202020 - Copy (2).jpg"
  },
  {
    "Description": "Account ID not found",
    "PhotoId": "202020 - Copy - Copy.jpg"
  },
  {
    "Description": "Account ID not found",
    "PhotoId": "202020 - Copy - Copy (2).jpg"
  }
  ],
 
  }
]

explained:

1. In the match stage you filter only documents with the ImportKeyId: 5843
2. With the project stage you fiter _id & ImportKeyId , and you add new field MissingPersonIds that will filter the elements in the array containing only strings like in SQL having "%202020 %" inside