How do I $concat fields in an array of objects?

Question

What I have

I have the following documents coming into my aggregation pipeline:

[
    { 
        "email" : "[email protected]", 
        "name"  : "Organization Name",  
        "users" : [
            {
                "lastName"  : "Nye", 
                "firstName" : "Bill", 
                "email"     : "[email protected]"
            },
            {
                "lastName"  : "Rogers", 
                "firstName" : "Mr.", 
                "email"     : "[email protected]"
            }
        ]
    },
    ...
]

What I want

Using$project I want to $concat the firstName and lastName fields within each of the array subdocuments to get the following result:

{ 
    "email" : "[email protected]", 
    "name"  : "Organization Name",  
    "users" : [
        {
            "name"  : "Bill Nye", 
            "email" : "[email protected]"
        },
        {
            "name"  : "Mr. Rogers", 
            "email" : "[email protected]"
        }
    ]
}

What I've tried - Attempt #1

I've tried the following aggregation:

db.organizations.aggregate([
    {
        $project: {
            email : 1,
            name  : 1,
            users : {
              name  : { $concat : [ "$users.firstName", " ", "$users.lastName" ] },
              email : 1
            }
        }
    }
]);

... but I get the following error:

$concat only supports strings, not Array

What I've tried - Attempt #2

I've also tried the following aggregation:

db.organizations.aggregate([
    {
        $project: {
            email : 1,
            name  : 1,
            users : {
              name  : { $concat : [ "$firstName", " ", "$lastName" ] },
              email : 1
            }
        }
    }
]);

... but name always returns null.

I feel as if this should be a relatively simple thing to do, however, I'm completely stumped.

How do I get the results that I'm looking for?

Sede · Accepted Answer · 2016-07-06 18:38:22Z

18

You can simply do this by $projecting your documents and use the $map aggregation variable operator to apply the $concat expression to each item in an array and returns an array with the applied results.

db.organizations.aggregate(
    [
        { "$project": { 
            "email": 1, 
            "name": 1, 
            "users": { 
                "$map": { 
                    "input": "$users", 
                    "as": "u", 
                      "in": { 
                          "name": { "$concat" : [ "$$u.firstName", " ", "$$u.lastName" ] }, 
                          "email": "$$u.email" 
                      } 
                }
            } 
        }} 
    ]
)

answered Jul 6, 2016 at 18:38

Sede

61.5k20 gold badges158 silver badges162 bronze badges

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

5 Comments

docksteaderluke Over a year ago

While this answer works just as well as @chridam's answer and tackles the problem from a different angle, I ended up using his solution. Thanks for your time!

Sede Over a year ago

@docksteaderluke I knew you will use this solution;) because despite the fact that the other answer is "factually correct", it's definitely not the way to go.

docksteaderluke Over a year ago

Care to elaborate? Why is it not the way to go?

Sede Over a year ago

@docksteaderluke The first problem is the length of the pipeline. The second is the presence of the $unwind in the pipeline. $unwind is very costly in terms of performance because it produces a Cartesian product of each document and the array field in the document. This results in a lot more documents to process down in the pipeline.

Abel Over a year ago

While that is true, unwind can be very fast if it only touches indexed elements, which makes it mem-only. At least this is so documented in Mongo's own reference. But I certainly agree that for the given issue, unwind is overkill.

Collectives™ on Stack Overflow

How do I $concat fields in an array of objects?

What I have

What I want

What I've tried - Attempt #1

What I've tried - Attempt #2

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I have

What I want

What I've tried - Attempt #1

What I've tried - Attempt #2

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related