CODEWITHSUNDEEP
mongodbaggregation-framework
mcadio
mcadio

Reputation: 769

Concatenate fields in array of Sub-documents

I am trying to get a set of results, where players names are separated by a / in string format, but I can't seem to get it to work. I can get the players array to concatenate the first and last names of each subdocument, but joining those into one string is failing.

This is what my current result is:

{ "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "name" : "Mary Mack" }, { "name" : "Mary Minor" } ], "net" : 4, "team" : 2 }
{ "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "name" : "Jane Doe" }, { "name" : "Julie Doe" } ], "net" : 3, "team" : 3 }
{ "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "name" : "Melanie Maygonuts" }, { "name" : "Mackenzie Mightbecray" } ], "net" : 3, "team" : 4 }
{ "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "name" : "Isabella Iamluny" }, { "name" : "Alexis Alreadythere" } ], "net" : 3, "team" : 5 }

This is what I want the result to be:

{"event" : "Junior Girls 16s", "field" : "Main", "team" : "Mary Mack / Mary Minor", "net" : 4, "team" : 2 }
{ "event" : "Junior Girls 16s", "field" : "Main", "team" : "Jane Doe / Julie Doe", "net" : 3, "team" : 3 }
{ "event" : "Junior Girls 16s", "field" : "Main", "team" : "Melanie Maygonuts / Mackenzie Mightbecray", "net" : 3, "team" : 4 }
{ "event" : "Junior Girls 16s", "field" : "Main", "team" : "Isabella Iamluny / Alexis Alreadythere", "net" : 3, "team" : 5 }

The code for the first set of results:

db.registrations.aggregate([
     {$match: {event: "Junior Girls 16s"}},
     {$project: {event: "$event", field: "$field", net: "$net", team: "$team",
      "players": {
              "$map": {
                  "input": "$players",
                  "as": "u",
                    "in": {
                        "name": { "$concat" : [ "$$u.first", " ", "$$u.last" ] }
                   }
              }
         } }}
 ])

This is the code I tried last, which gives me an error invalid operator reduce:

db.registrations.aggregate([         
    {$match: {event: "Junior Girls 16s"}},         
    {$project: {event: "$event", field: "$field", net: "$net", team: "$team",          
        "players": {                  
            "$map": {                      
                "input": "$players",                      
                "as": "u",                        
                "in": {                            
                    "name": { "$concat" : [ "$$u.first", " ", "$$u.last" ] }                       
                }    
            }
        } 
    }},
    {$project: {event: "$event", field: "$field", net: "$net", team: "$team",
        "players": {
          $reduce: {
            input: "$players",
            initialValue: "",
            in: { $concat: ['$$name', ' / ', '$$this'] }
          }
        }
    }}
    ])

What am I missing? I feel like I'm SO close, but just cannot manipulate the data how I want.

Upvotes: 1

Views: 793

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151122

This really should not be done through the aggregation framework, but we will show the example anyway.

As already noted, your specific error is because you do not have a MongoDB that supports $reduce, which means MongoDB 3.4 or greater. When you do upgrade however, you can do a statement like this:

db.registrations.aggregate([
  { "$addFields": {
    "players": {
      "$reduce": {
        "input": { "$filter": {
          "input": { 
            "$reduce": {
              "input": { "$zip": {
                "inputs": [
                  { "$map": {
                    "input": "$players",
                    "as": "u",
                    "in": { "$concat": [ "$$u.first", " ", "$$u.last" ] }
                  }},
                  { "$map": {
                    "input": {
                      "$range": [ 0, { "$subtract": [ { "$size": "$players" }, 1 ] } ]
                    },
                    "as": "el",
                    "in": " / "
                  }}
                ],
                "useLongestLength": true
              }},
              "initialValue": [],
              "in": { "$concatArrays": [ "$$value", "$$this" ] }
            }
          },
          "as": "el",
          "cond": { "$ne": [ "$$el", null ] }
        }},
        "initialValue": "",
        "in": { "$concat": [ "$$value", "$$this" ] }
      }
    }
  }}
])

Which will produce the desired output:

{
    "_id" : ObjectId("592cfbc3820a42bc1e825de7"),
    "event" : "Junior Girls 16s",
    "field" : "Main",
    "players" : "Mary Mack / Mary Minor",
    "net" : 4,
    "team" : 2
}
{
    "_id" : ObjectId("592cfbc3820a42bc1e825de8"),
    "event" : "Junior Girls 16s",
    "field" : "Main",
    "players" : "Jane Doe / Julie Doe",
    "net" : 3,
    "team" : 3
}
{
    "_id" : ObjectId("592cfbc3820a42bc1e825de9"),
    "event" : "Junior Girls 16s",
    "field" : "Main",
    "players" : "Melanie Maygonuts / Mackenzie Mightbecray",
    "net" : 3,
    "team" : 4
}
{
    "_id" : ObjectId("592cfbc3820a42bc1e825dea"),
    "event" : "Junior Girls 16s",
    "field" : "Main",
    "players" : "Isabella Iamluny / Alexis Alreadythere",
    "net" : 3,
    "team" : 5
}

That's quite a mouthful to chew on isn't it? Which is exactly why you would not do that, and simply write such transformations in the client side code reading the data.

As a trivial JavaScript example for the shell:

db.registrations.find().forEach( doc => {
  doc.players = doc.players
    .map( p => `${p.first} ${p.last}` )
    .join(" / ");
  printjson(doc)
})

Outputs exactly the same thing. See how much cleaner that is now.

Source Data

Note that this used source data as described in the question where the original array content has "first" and "last" fields for the names in the array:

{ "_id" : ObjectId("592cfbc3820a42bc1e825de7"), "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "first" : "Mary", "last" : "Mack" }, { "first" : "Mary", "last" : "Minor" } ], "net" : 4, "team" : 2 }
{ "_id" : ObjectId("592cfbc3820a42bc1e825de8"), "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "first" : "Jane", "last" : "Doe" }, { "first" : "Julie", "last" : "Doe" } ], "net" : 3, "team" : 3 }
{ "_id" : ObjectId("592cfbc3820a42bc1e825de9"), "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "first" : "Melanie", "last" : "Maygonuts" }, { "first" : "Mackenzie", "last" : "Mightbecray" } ], "net" : 3, "team" : 4 }
{ "_id" : ObjectId("592cfbc3820a42bc1e825dea"), "event" : "Junior Girls 16s", "field" : "Main", "players" : [ { "first" : "Isabella", "last" : "Iamluny" }, { "first" : "Alexis", "last" : "Alreadythere" } ], "net" : 3, "team" : 5 }

Upvotes: 3

Related Questions