Mongo Aggregation filter on collection 2 fields

Question

Hi I am working with mongoDb I have two collections as below:

Owners

name
address
familySize
petId

[
  {
    "name": "Johnsons",
    "address": "Lambert Street",
    "familySize": 4,
    "petId": "p153ATjs54"
  },
    {
    "name": "Markinsons",
    "address": "Pebble Street",
    "familySize": 2,
    "petId": "G34hts94"
  }
]

Pets

animal_Id // the foreign key if you may for petId
petName
preferredPlayToy
vaccinationInfo

[
  {
    "animal_Id": "G34hts94",
    "petName": "Oscar",
    "preferredPlayToy": "Ball",
    "vaccinationInfo": {
      "vacId": "a-984KA",
      "dosage": 3
    }
  },
  {
    "animal_Id": "p153ATjs54",
    "petName": "Piggy",
    "preferredPlayToy": "Bone",
    "vaccinationInfo": {
      "vacId": "G-397B",
      "dosage": 1
    }
  }
]

Now I am actually trying to find all Owners who have pet that have preferredPlayToy as "ball" Below is what I have in the pipeline but I can't seem to get it right:

db.owners.aggregate([
    {
        $lookup: {
            from: 'ms_pets',
            localField: 'petId',
            foreignField: 'aniId',
            as: 'petMaster'
        }
    },
    {
        name: 1,
                    address: 1
        'petMasterData': {
            $filter:{
                input: "$petMaster", 
                as: "master", 
                cond: { $eq: [ "$$master.preferredPlayToy", 'ball' ] } 
            } 
        }
    }
])

It'll also be great if I can pull out the pet name and vaccinationInfo from the Pets table as part of the response.

Sample Expected:

{
    "name": "Markinsons",
    "address": "Pebble Street",
    "petId": "G34hts94",
    "petMasterData":{

        "petName": "Oscar",
        "vaccinationInfo": {
            "vacId": "a-984KA",
            "dosage": 3
        }
    }

}

mickl mickl · Accepted Answer · 2019-08-01T18:39:01

You can run $lookup with custom pipeline to apply both filtering conditions and projection. To convert an array into single subdocument you can use $unwind which will also remove owners with empty petMasterData

db.owners.aggregate([
    {
        $lookup: {
            from: "ms_pets",
            let: { pet_Id: "$petId" },
            pipeline: [ 
                { $match: { 
                    $expr: { 
                        $and: [ 
                            { $eq: [ "$$pet_Id", "$animal_Id" ] }, 
                            { $eq: [ "$preferredPlayToy", "Ball" ] } ] 
                        } 
                    }
                },
                { $project: { petName: 1, vaccinationInfo: 1, _id: 0 } } 
            ],
            as: "petMasterData"                
        }
    },
    { $unwind: "$petMasterData" }
])

Mongo Aggregation filter on collection 2 fields

1 Answers