Aggregate array field and text field in a collection mongo Node

Question

I want to merge two document in same collection by grouping two fields, one field is just a normal text field (WorkFlow), and another one is an array field (Service), i was able to group the normal field(WorkFlow) but am stuck to group the array field, please help me I have a short in time to do it, I've gone through some article but I couldn't find any solution, please help me to overcome this..

** i need to get the output, in any method if its not in aggregate then any other solutions

I have the document in collection like below:

[{
  "_id": {
    "$oid": "62f9fd54259335683bc54ac3"
  },
  "WorkFlow": "Vendor",
  "Service": [
    {
      "value": "6235b52ea216f20e1b00ad43"
    },
    {
      "value": "6235b538a216f20e1b00ad46"
    }
  ],
  "AssetServiceClassification": "Business",
  "LevelArray": [
    {
      "Level": "6235b4f5a216f20e1b00ad36",
      "StaffName": "620a23bbc7e6a4378ff8ad74",
      "Designation": "61efa3a5a444008633b223dd"
    },
    {
      "Level": "6235b500a216f20e1b00ad39",
      "StaffName": "620b4d4995c3061565e63b08",
      "Designation": "61efa3aca444008633b223e0"
    }
  ]
},
{
  "_id": {
    "$oid": "62f9f5f9e26c8912b86c61b8"
  },
  "WorkFlow": "Vendor",
  "Service": [
    {
      "value": "6235b538a216f20e1b00ad46"
    },
    {
      "value": "6235b52ea216f20e1b00ad43"
    }
  ],
  "AssetServiceClassification": "Normal",
  "LevelArray": [
    {
      "Level": "6235b4f5a216f20e1b00ad36",
      "StaffName": "620a2351c7e6a4378ff8ad4c",
      "Designation": "61efa3a5a444008633b223dd"
    },
    {
      "Level": "6235b500a216f20e1b00ad39",
      "StaffName": "620a2387c7e6a4378ff8ad60",
      "Designation": "61efa3aca444008633b223e0"
    }
  ]
}]

and I need an output structure like below:

[{
  "WorkFlow": "Vendor",
  "Service": [
    {
      "value": "6235b52ea216f20e1b00ad43"
    },
    {
      "value": "6235b538a216f20e1b00ad46"
    }
  ],
  "Assets": [
    {
      "_id":  "62f9fd54259335683bc54ac3",
      "AssetServiceClassification": "Business",
      "LevelArray": [
        {
          "Level": "6235b4f5a216f20e1b00ad36",
          "StaffName": "620a23bbc7e6a4378ff8ad74",
          "Designation": "61efa3a5a444008633b223dd"
        },
        {
          "Level": "6235b500a216f20e1b00ad39",
          "StaffName": "620b4d4995c3061565e63b08",
          "Designation": "61efa3aca444008633b223e0"
        }
      ]
    },
    {
      "_id":  "62f9f5f9e26c8912b86c61b8",
      "AssetServiceClassification": "Normal",
      "LevelArray": [
        {
          "Level": "6235b4f5a216f20e1b00ad36",
          "StaffName": "620a2351c7e6a4378ff8ad4c",
          "Designation": "61efa3a5a444008633b223dd"
        },
        {
          "Level": "6235b500a216f20e1b00ad39",
          "StaffName": "620a2387c7e6a4378ff8ad60",
          "Designation": "61efa3aca444008633b223e0"
        }
      ]
    }
  ]
}]

Answer 1

I think this pipeline will help you get the output you want:

db.collection.aggregate([
  {
    "$unwind": "$Service"
  },
  {
    "$sort": {
      "Service.value": 1
    }
  },
  {
    "$group": {
      "_id": {
        "_id": "$_id",
        "WorkFlow": "$WorkFlow",
        "LevelArray": "$LevelArray",
        "AssetServiceClassification": "$AssetServiceClassification"
      },
      "Service": {
        "$push": "$Service"
      }
    }
  },
  {
    "$project": {
      "_id": "$_id._id",
      "WorkFlow": "$_id.WorkFlow",
      "LevelArray": "$_id.LevelArray",
      "AssetServiceClassification": "$_id.AssetServiceClassification",
      "Service": "$Service"
    }
  },
  {
    "$group": {
      "_id": {
        "WorkFlow": "$WorkFlow",
        "Service": "$Service"
      },
      "Assets": {
        "$push": {
          "_id": "$_id",
          "LevelArray": "$LevelArray",
          "AssetServiceClassification": "$AssetServiceClassification"
        }
      }
    }
  }
])