Combine arrays when grouping documents

Question

Documents in the database are in below format

{
  product: 'product1',
  state: 'state1',
  nondnd: [1, 2, 3],
  dnd: [4, 5],
  land: [],
  emails: ['a', 'b', 'c']
},
{
  product: 'product1',
  state: 'state1',
  nondnd: [9, 8, 2],
  dnd: [10, 7, 11],
  land: [2, 4, 6, 8],
  emails: ['d']
},
{
  product: 'product1',
  state: 'state2',
  nondnd: [9, 8, 2],
  dnd: [10, 7, 11],
  land: [1, 3],
  emails: ['e', 'g']
}

I need to group the above documents on the basis of product & state and get them in below format

{
  _id: {
    product: 'product1',
    state: 'state1'
  },
  nondnd: [1, 2, 3, 9, 8, 2],
  dnd: [4, 5, 10, 7, 11],
  land: [2, 4, 6, 8],
  emails: ['a', 'b', 'c', 'd']
},
{
  _id:{
    product: 'product1',
    state: 'state2'
  },
  nondnd: [2, 5, 8],
  dnd: [1, 4, 7],
  land: [1, 3],
  emails: ['e', 'g']
}

I tried to unwind them individually and group them. But when I unwind them, same number is repeating. Please help me out

Answer 1

Depending on your available version and practicality you could possibly just apply $reduce and $concatArrays in order to "join" the resulting "array of arrays" in the grouped document:

db.getCollection('stuff').aggregate([
  { "$group": {
    "_id": {
      "product": "$product", "state": "$state"
    },
    "nondnd": { "$push": "$nondnd" },
    "dnd": { "$push": "$dnd" },
    "land": { "$push": "$land" },
    "emails": { "$push": "$emails" }
  }},
  { "$addFields": {
    "nondnd": {
      "$reduce": {
        "input": "$nondnd",
        "initialValue": [],
        "in": { "$concatArrays": [ "$$value", "$$this" ] }
      }
    },
    "dnd": {
      "$reduce": {
        "input": "$dnd",
        "initialValue": [],
        "in": { "$concatArrays": [ "$$value", "$$this" ] }
      }
    },
    "land": {
      "$reduce": {
        "input": "$land",
        "initialValue": [],
        "in": { "$concatArrays": [ "$$value", "$$this" ] }
      }
    },
    "emails": {
      "$reduce": {
        "input": "$emails",
        "initialValue": [],
        "in": { "$concatArrays": [ "$$value", "$$this" ] }
      }
    }      
  }}
])

Or even "ultra-modern" where you really don't like repeating yourself ( but you probably should be generating the pipeline stages anyway ):

db.getCollection('stuff').aggregate([
  { "$project": {
    "product": 1,
    "state": 1,
    "data": {
      "$filter": {
        "input": { "$objectToArray": "$$ROOT" },
        "cond": { "$in": [ "$$this.k", ["nondnd","dnd","land","emails"] ] }  
      }  
    }  
  }},
  { "$unwind": "$data" },
  { "$unwind": "$data.v" },
  { "$group": {
    "_id": {
      "product": "$product",
      "state": "$state",
      "k": "$data.k"
    },
    "v": { "$push": "$data.v" }    
  }},
  { "$group": {
    "_id": {
      "product": "$_id.product",
      "state": "$_id.state"  
    },
    "data": { "$push": { "k": "$_id.k", "v": "$v" } }  
  }},
  { "$replaceRoot": {
    "newRoot": {
      "$arrayToObject": {
        "$concatArrays": [
          [{ "k": "_id", "v": "$_id" }],
          { "$map": {
            "input": ["nondnd","dnd","land","emails"],
            "in": {
              "$cond": {
                "if": { "$ne": [{ "$indexOfArray": [ "$data.k", "$$this" ] },-1] },
                "then": { 
                  "$arrayElemAt": [
                    "$data",
                    { "$indexOfArray": [ "$data.k", "$$this" ] }
                  ]
                },
                "else": { "k": "$$this", "v": [] }
              }
            }
          }}
        ]
      }
    }
  }}
])

Or you can alternately join the arrays at the source and map them to a type. Then reconstruct after the grouping:

db.getCollection('stuff').aggregate([
  { "$project": {
    "product": 1,
    "state": 1,
    "combined": {
      "$concatArrays": [
        { "$map": {
          "input": "$nondnd",
          "in": { "t": "nondnd", "v": "$$this" }
        }},
        { "$map": {
          "input": "$dnd",
          "in": { "t": "dnd", "v": "$$this" }  
        }},
        { "$map": {
          "input": "$land",
          "in": { "t": "land", "v": "$$this" }
        }},
        { "$map": {
          "input": "$emails",
          "in": { "t": "emails", "v": "$$this" }    
        }}
      ]        
    }
  }},
  { "$unwind": "$combined" },
  { "$group": {
    "_id": {
      "product": "$product", "state": "$state"
    },
    "combined": { "$push": "$combined" }      
  }},
  { "$project": {
    "nondnd": {
      "$map": {
        "input": {
          "$filter": {
            "input": "$combined",
            "cond": { "$eq": [ "$$this.t", "nondnd" ] }
          }
        },
        "in": "$$this.v"
      }  
    },
    "dnd": {
      "$map": {
        "input": {
          "$filter": {
            "input": "$combined",
            "cond": { "$eq": [ "$$this.t", "dnd" ] }
          }
        },
        "in": "$$this.v"
      }  
    },
    "land": {
      "$map": {
        "input": {
          "$filter": {
            "input": "$combined",
            "cond": { "$eq": [ "$$this.t", "land" ] }
          }
        },
        "in": "$$this.v"
      }  
    },
    "emails": {
      "$map": {
        "input": {
          "$filter": {
            "input": "$combined",
            "cond": { "$eq": [ "$$this.t", "emails" ] }
          }
        },
        "in": "$$this.v"
      }  
    }  
  }}
])

So largely depending on $map and $filter in both constructing and deconstructing the contents of a single joined array, which is of course perfectly fine to $unwind.

The same result comes from each case:

/* 1 */
{
    "_id" : {
        "product" : "product1",
        "state" : "state2"
    },
    "nondnd" : [ 
        9.0, 
        8.0, 
        2.0
    ],
    "dnd" : [ 
        10.0, 
        7.0, 
        11.0
    ],
    "land" : [ 
        1.0, 
        3.0
    ],
    "emails" : [ 
        "e", 
        "g"
    ]
}

/* 2 */
{
    "_id" : {
        "product" : "product1",
        "state" : "state1"
    },
    "nondnd" : [ 
        1.0, 
        2.0, 
        3.0, 
        9.0, 
        8.0, 
        2.0
    ],
    "dnd" : [ 
        4.0, 
        5.0, 
        10.0, 
        7.0, 
        11.0
    ],
    "land" : [ 
        2.0, 
        4.0, 
        6.0, 
        8.0
    ],
    "emails" : [ 
        "a", 
        "b", 
        "c", 
        "d"
    ]
}