MongoDB unwind empty array with match and projection - return all parents

Question

I am trying to filter on subdocuments to return the documents that match a certain criteria, in this case, return preferred phones. And if there is no match, return the parent with and empty preferred phone. This is my current model:

[{
    "_id": 1,
    "FirstName": "Elaine",
    "LastName": "Benes",
    "Phones": [{
        "PhoneNumber": "(123)123-1234",
        "PhoneTypeId": 1,
        "IsPreferred": true
    },{
        "PhoneNumber": "(321)321-4321",
        "PhoneTypeId": 2,
        "IsPreferred": false
    }]
},{
    "_id": 2,
    "FirstName": "Jerry",
    "LastName": "Seinfeld",
    "Phones": [{
        "PhoneNumber": "(123)123-1234",
        "PhoneTypeId": 1,
        "IsPreferred": true
    }]
},{
    "_id": 3,
    "FirstName": "Cosmo",
    "LastName": "Kramer",
    "Phones": [{
        "PhoneNumber": "(123)123-1234",
        "PhoneTypeId": 1,
        "IsPreferred": false
    }]
},{
    "_id": 4,
    "FirstName": "George",
    "LastName": "Costanza",
    "Phones": []
}]

And i would like to return the following:

[{
    "_id": 1,
    "FirstName": "Elaine",
    "LastName": "Benes",
    "PreferredPhone": {
        "PhoneNumber": "(123)123-1234",
        "PhoneTypeId": 1,
        "IsPreferred": true
    }
},{
    "_id": 2,
    "FirstName": "Jerry",
    "LastName": "Seinfeld",
    "PreferredPhone": {
        "PhoneNumber": "(123)123-1234",
        "PhoneTypeId": 1,
        "IsPreferred": true
    }
},{
    "_id": 3,
    "FirstName": "Cosmo",
    "LastName": "Kramer",
    "PreferredPhone": null
},{
    "_id": 4,
    "FirstName": "George",
    "LastName": "Costanza",
    "PreferredPhone": null
}]

I have tried using aggregation to unwind and do a match and then project, as follows:

{
    "$unwind": "$Phones"
},
{
    "$match": {
        "IsPreferred": true
    }
},
{
    "$project": {
        "FirstName": "$FirstName",
        "LastName": "$LastName",
        "PreferredPhone" : { 
            "PhoneNumber" : "$Phones.PhoneNumber", 
            "PhoneTypeId" : "$Phones.PhoneTypeId", 
            "PhoneId" : "$Phones.PhoneId" 
        }   
    }
}

I have also tried something similar to something proposed in this question: Unwind empty array in mongodb

I am only getting the first two documents when I really want all them with either the preferred phone or an empty array. Is this possible?

score 1 · Accepted Answer · answered Apr 13 '15 at 13:00

MongoDB 2.2's new $elemMatch projection operator provides another way to alter the returned document to contain only the first matched shapes element:

db.test.find(
     {}, 
     {'FirstName':1,'LastName':1,Phones: {$elemMatch: {'IsPreferred': true}}}
);

Returns:

[{
    "_id" : 1,
    "FirstName" : "Elaine",
    "LastName" : "Benes",
    "Phones" : [ 
        {
            "PhoneNumber" : "(123)123-1234",
            "PhoneTypeId" : 1,
            "IsPreferred" : true
        }
    ]
}
{
    "_id" : 2,
    "FirstName" : "Jerry",
    "LastName" : "Seinfeld",
    "Phones" : [ 
        {
            "PhoneNumber" : "(123)123-1234",
            "PhoneTypeId" : 1,
            "IsPreferred" : true
        }
    ]
}
{
    "_id" : 3,
    "FirstName" : "Cosmo",
    "LastName" : "Kramer"
}
{
    "_id" : 4,
    "FirstName" : "George",
    "LastName" : "Costanza"
}]

Although the field "Phones" is still an array.

MongoDB unwind empty array with match and projection - return all parents

1 Answers1