MongoDB query: Apply conditions at different levels in the document

Question

I am struggling to get the mongodb query working. Below is my example collection. As you can see that the picture List is an optional nested array.

{
  "firstName" : "Mark",
  "gender" : "M",
  "PictureList":[]
},
{
  "firstName" : "Jane",
  "gender" : "F",
  "PictureList":[]
},
{
  "firstName" : "Mary",
  "gender" : "F",
  "PictureList" : [{"name" : "20151128_233939.jpg","dpInd" : "N"}, 
                   {"name" : "20150712_231715.jpg","dpInd" : "Y"}
                  ]
}

My Question is? I need to

select all the records with Gender = "F"

Apply another filter at level 2 to only pull the records from the nested array whose dpInd = "Y". If this condition is not met then show empty results only for nested array. My Expected Results is

   {
      "firstName" : "Jane",
      "gender" : "F",
      "PictureList":[]
   }
   {
      "firstName" : "Mary",
      "gender" : "F",
      "PictureList" : [{"name" : "20150712_231715.jpg","dpInd" : "Y"}]
   }

Thanks for the response. My second condition is optional. Just like a outer join in traditional SQL. How to achieve this using MongoDB? I tried below but it only gives me one record. `db.users.aggregate([{$match:{"gender":"F"}},{$unwind:"$PictureList"},{$match:{"PictureList.dpInd":"Y"}}]).pretty();` response shown is `{ "firstName" : "Mary", "gender" : "F", "PictureList" : [{"name" : "20150712_231715.jpg","dpInd" : "Y"}] }` — user2596892, Dec 08 '15 at 12:11

styvane · Accepted Answer · 2015-12-08T17:04:10.963

You need to $project your documents and use the $map operator to return an array of sub-documents that match your critera and false using the $cond operator. You can filter out false from the array using the $setDifference operator.

db.people.aggregate([
    { "$match": { "gender": "F" } },
    { "$project": {
        "firstName": 1,
        "gender": 1, 
        "PictureList": {
            "$setDifference": [
                { "$map": { 
                    "input": "$PictureList", 
                    "as": "pl", 
                    "in": { 
                         "$cond": [ 
                              { "$eq": [ "$$pl.dpInd", "Y" ] }, 
                              "$$pl", 
                              false
                         ]
                    }
                }}, 
                [false]
            ]
        }
    }}
])

Which yields:

{
        "_id" : ObjectId("566660b147d21084f02042b3"),
        "firstName" : "Jane",
        "gender" : "F",
        "PictureList" : [ ]
}
{
        "_id" : ObjectId("566660b147d21084f02042b4"),
        "firstName" : "Mary",
        "gender" : "F",
        "PictureList" : [
                {
                        "name" : "20150712_231715.jpg",
                        "dpInd" : "Y"
                }
        ]
}

MongoDB query: Apply conditions at different levels in the document

1 Answers1