I am new to MongoDB please help me to merge the different data sets with the same block and floor name into one dataset. Actually, the below format is in one database collection with 4 data-set. can anyone help me to solve this problem in Node.JS and MongoDB?

I tried many ways to solve this issue but I didn't get any proper output, please help me as soon as possible, thanks in advance.

[
  {
    BlockName: "B1",
    FloorName: "F1",
    Units: [
      {
        UnitName: 104,
        Location: [
          "Hall",
          "Living"
        ]
      }
    ]
  },
  {
    BlockName: "B1",
    FloorName: "F2",
    Units: [
      {
        UnitName: 104,
        Location: [
          "Hall",
          "Living"
        ]
      }
    ]
  },
  {
    BlockName: "B2",
    FloorName: "F1",
    Units: [
      {
        UnitName: 104,
        Location: [
          "Hall",
          "Living"
        ]
      }
    ]
  },
  {
    BlockName: "B2",
    FloorName: "F2",
    Units: [
      {
        UnitName: 104,
        Location: [
          "Hall",
          "Living"
        ]
      }
    ]
  }
]

I need an output structure like this below:-

[
  {
    BlockName: "B1",
    Floors: [
      {
        FloorName: "F1",
        Units: [
          {
            UnitName: 104,
            Location: [
              "Hall",
              "Living"
            ]
          }
        ]
      },
      {
        FloorName: "F2",
        Units: [
          {
            UnitName: 104,
            Location: [
              "Hall",
              "Living"
            ]
          }
        ]
      }
    ]
  },
  {
    BlockName: "B2",
    Floors: [
      {
        FloorName: "F1",
        Units: [
          {
            UnitName: 104,
            Location: [
              "Hall",
              "Living"
            ]
          }
        ]
      },
      {
        FloorName: "F2",
        Units: [
          {
            UnitName: 104,
            Location: [
              "Hall",
              "Living"
            ]
          }
        ]
      }
    ]
  }
]

CodePudding user response：

1. $group - Group by BlockName and push the documents into Floors array.
2. $project - Decorate output documents.

db.collection.aggregate([
  {
    $group: {
      _id: "$BlockName",
      Floors: {
        $push: {
          "FloorName": "$FloorName",
          "Units": "$Units"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      BlockName: "$_id",
      Floors: 1
    }
  }
])

Sample Mongo Playground