MongoDB unwind + filter on two fields of unwinded documents

Question

I'm running a Mongo aggregation which "merges" together 2 different documents. Everything works fine, matching etc. Right now I'm trying to match on two fields of a sub-document, but it looks like it results in an OR instead of and AND. Let me give some contest:

Main Document

{
_id: 1234567890a,
title: "mainTitle",
country: "ITA"
}

First sub-Document

{
_id:1234567890b,
mainDocumentId: 1234567890a,
someField: "123",
property: "car"
}

What I mean to do is to aggregate these 2 documents and filter on someField $eq: 123 AND property $eq: car . If there is a sub-document like:

{
_id:1234567890b,
mainDocumentId: 1234567890a,
someField: "123",
property: "bus"
}

The pipeline shouldn't consider it My aggregation looks like this:

db.mycollection.aggregate([{$lookup: {
  from: 'sub_collection',
  localField: '_id',
  foreignField: 'mainDocumentId',
  as: 'SubCollection'
}},
{ $match : { "SubCollection.someField" : '123' , 'SubCollection.property':'car'} },
{ $unwind : "$_id" },
{ $sort : { "createdAt" : -1}},
{ $skip : 0}, 
{ $limit : 40}
])

The result I get is every document with a subdocument which matches OR the first OR the second condition. I tried with $elemMatch but this isn't its purpose and in fact it doesn't work. I also tried to specify the $and in the $match stage

db.mycollection.aggregate([{$lookup: {
  from: 'sub_collection',
  localField: '_id',
  foreignField: 'mainDocumentId',
  as: 'SubCollection'
}},
{ $match : { 
    $and: [
    { 
        "SubCollection": {
            $elemMatch: { 
                "someField": '123'
             } 
         } 
     },
    { 
        "SubCollection": {
            $elemMatch: { 
                "property":  { $eq : 'car' }
             } 
         } 
}
     ]}
},
{ $unwind : "$_id" },
{ $sort : { "createdAt" : -1}},
{ $skip : 0}, 
{ $limit : 40}
])

I feel like it's a pretty simple task, but clearly I'm missing something: any tip will be appreciated! Thank you!

- EDIT - This is my output based on the $lookup I posted above:

{
_id: 1234567890a,
title: "mainTitle",
country: "ITA",
subDocument: [
{
    _id:1234567890b,
    mainDocumentId: 1234567890a,
    someField: "123",
    property: "car"
    },
    {
    _id:1234567890b,
    mainDocumentId: 1234567890a,
    someField: "123",
    property: "bus"
    }
]
}

Charchit Kapoor · Answer

$match is not the correct stage for filtering array elements. You need to use $project or $addFields, along $filter operator, to filter out array elements. Like this:

db.collection.aggregate([
  {
    "$project": {
      subDocument: {
        "$filter": {
          "input": "$subDocument",
          "as": "doc",
          "cond": {
            "$and": [
              {
                "$eq": [
                  "$$doc.someField",
                  "123"
                ]
              },
              {
                "$eq": [
                  "$$doc.property",
                  "car"
                ]
              }
            ]
          }
        }
      }
    }
  }
]);

See it in action here. You can add the above $project stage in your pipeline.

MongoDB unwind + filter on two fields of unwinded documents

1 Answers