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
– wdberkeley, Commented Feb 4, 2015 at 15:22
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
– inolasco, Commented Feb 4, 2015 at 15:32
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
– inolasco, Commented Feb 4, 2015 at 15:38
There isn't, sadly. The $project stage doesn't project field values into array values very well, see SERVER-8141. — wdberkeley
– wdberkeley, Commented Feb 4, 2015 at 16:26

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

4

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.

answered Mar 3, 2015 at 3:56

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

inolasco Over a year ago

Awesome. Not sure how I didn't think of this, I knew about the $map operator, but didn't think of passing it an array of the possible phone types :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related