MongoDB aggregation: Project separate document fields into a single array field

Question

I have a document like this:

{fax: '8135551234', cellphone: '8134441234'}

Is there a way to project (without a group stage) this document into this:

{
    phones: [{
        type: 'fax',
        number: '8135551234'
    }, {
        type: 'cellphone',
        number: '8134441234'
    }]
}

I could probably use a group stage operator for this, but I'd rather not if there's any other way, because my query also projects several other fields, all of which would require a $first just for the group stage.

Hope that's clear. Thanks in advance!

On the off-chance it inspires an alternative solution, why are you trying to group the fax and cellphone fields into an array like that? — wdberkeley
The reason for matching it like that, is to format the documents from a large collection (npi database) into the model my app uses, where you can have an arbitrary number of phones of any type, as opposed to specific fields for each type of phone. — inolasco
BTW, having the document formatted like this isn't a strict requirement, and there are ways around it in database (group stage) or in code (post processing the result in the app). I just wonder if there is way to do this in database outside of a group stage or not, for future reference. — inolasco
There isn't, sadly. The $project stage doesn't project field values into array values very well, see SERVER-8141. — wdberkeley

Neil Lunn Neil Lunn · Accepted Answer · 2015-03-03T03:56:57

MongoDB 2.6 Introduces the the $map operator which is an array transformation operator which can be used to do exactly this:

db.phones.aggregate([
    { "$project": {
        "phones": { "$map": {
            "input": { "$literal": ["fax","cellphone"] },
            "as": "el",
            "in": {
                "type": "$$el",
                "number": { "$cond": [
                     { "$eq": [ "$$el", "fax" ] },
                     "$fax",
                     "$cellphone"
                 ]}
             }
        }}
    }}
])

So your document now looks exactly like you want. The trick of course to to create a new array with members "fax" and "cellphone", then transform that array with the new document fields by matching those values.

Of course you can also do this in earlier versions using $unwind and $group in a similar fashion, but just not as efficiently:

db.phones.aggregate([
    { "$project": {
        "type": { "$const": ["fax","cellphone"] },
        "fax": 1,
        "cellphone": 1
    }},
    { "$unwind": "$type" },
    { "$group": {
        "_id": "_id",
        "phones": { "$push": { 
            "type": "$type",
            "number": { "$cond": [
                { "$eq": [ "$type", "fax" ] },
                "$fax",
                "$cellphone"
            ]}
        }}
    }}
])

Of course it can be argued that unless you are doing some sort of aggregation then you may as well just post process the collection results in code. But this is an alternate way to do that.

MongoDB aggregation: Project separate document fields into a single array field

1 Answers