Sort multiple levels of array after group in Mongo Java-CodePudding

I have documents with below schema

id : 
currencyCode : "USD"
businessDayStartDate : ""
hourZoneNumber : 1
customerCount : 0
itemQuantity : 4
nodeId : "STORE_DEV"
endpointId : "998"
amount : 4

I am trying to find documents that match nodeId and trying to aggregate customerCount, itemQuantity and amount for each hourZoneNumber.

Below is the query

db.getCollection("xxx").aggregate([
{ "$match": { "nodeId": { "$in":["STORE_DEV_1","STORE_DEV_2"] }, "businessDayStartDate" : { "$gte": "2022-03-04" , "$lte": "2022-03-07" } }},
{ "$group": {
       "_id": {
          "nodeId": "$nodeId",
          "endpointId": "$endpointId",
          "hourZoneNumber": "$hourZoneNumber"
       },
       "customerCount": { "$sum": "$customerCount" },
       "itemQuantity" : { "$sum": "$itemQuantity" },
       "amount" : { "$sum": "$amount" }
       
  }
},
{ "$group": {
       "_id": {
          "nodeId": "$_id.nodeId",
          "endpointId": "$_id.endpointId"
       },
       "hourZones": {
          "$addToSet": {
            "hourZoneNumber": "$_id.hourZoneNumber",
            "customerCount": { "$sum": "$customerCount" },
            "itemQuantity" : { "$sum": "$itemQuantity" },
            "amount" : { "$sum": "$amount" }
          }
       }
       
  }
},
{ "$group": {
       "_id": "$_id.nodeId",
       "endpoints": {
          "$addToSet": {
            "endpointId": "$_id.endpointId",
             "hourZones": "$hourZones"
          }
       },
       "total": {
            "$addToSet": {
                "customerCount": { "$sum": "$hourZones.customerCount" },
                "itemQuantity" : { "$sum": "$hourZones.itemQuantity" },
                "amount" : { "$sum": "$hourZones.amount" }
          }
       }
  }
},
{
    $project: {
      _id: 0,
      nodeId: "$_id",
      endpoints: 1,
      hourZones: 1,
      total: 1
    }
  }
])

Output is as below:

{
  nodeId: 'STORE_DEV_2',
  endpoints: [ 
    { endpointId: '998',
       hourZones: 
        [ 
          { hourZoneNumber: 1,
            customerCount: 0,
            itemQuantity: 4,
            amount: Decimal128("4") }
        ] } ],
  total: [ { customerCount: 0, itemQuantity: 4, amount: Decimal128("4") } ],
}
{ 
  nodeId: 'STORE_DEV_1',
  endpoints: 
   [ { endpointId: '999',
       hourZones: 
        [ { hourZoneNumber: 2,
            customerCount: 2,
            itemQuantity: 4,
            amount: Decimal128("4") },
          { hourZoneNumber: 1,
            customerCount: 4,
            itemQuantity: 8,
            amount: Decimal128("247.56") } ] } ],
  total: 
   [ { customerCount: 6,
       itemQuantity: 12,
       amount: Decimal128("251.56") } ]
}

I want the output to be sorted as : First sort by nodeId, then by endpointId within the endpoints and lastly by hourZoneNumber within hourZones.

How do I do this ? I tried using sort() with all the three fields. But it did not work. Also, can someone please confirm if there is any better way than the above code, as I am new to Mongo DB.

Edit: Please find sample input data at https://mongoplayground.net/p/FYm3QMMgrNI

CodePudding user response：

Since you already have the separated data at the beginning, it is simply a matter of saving these values through the grouping and then sorting by them in the end.

db.collection.aggregate([
  {
    "$match": {
      "nodeId": {
        "$in": [
          "STORE_DEV_2",
          "STORE_DEV"
        ]
      },
      "businessDayStartDate": {
        "$gte": "2022-03-04",
        "$lte": "2022-03-07"
      }
    }
  },
  {
    "$sort": {
      "nodeId": 1,
      "endpointId": 1,
      "hourZoneNumber": 1
    }
  },
  {
    "$group": {
      "_id": {
        "nodeId": "$nodeId",
        "endpointId": "$endpointId",
        "hourZoneNumber": "$hourZoneNumber"
      },
      "customerCount": {
        "$sum": "$customerCount"
      },
      "itemQuantity": {
        "$sum": "$itemQuantity"
      },
      "amount": {
        "$sum": "$amount"
      }
    }
  },
  {
    "$group": {
      "_id": {
        "nodeId": "$_id.nodeId",
        "endpointId": "$_id.endpointId"
      },
      "hourZones": {
        "$addToSet": {
          "hourZoneNumber": "$_id.hourZoneNumber",
          "customerCount": {
            "$sum": "$customerCount"
          },
          "itemQuantity": {
            "$sum": "$itemQuantity"
          },
          "amount": {
            "$sum": "$amount"
          }
        }
      },
      hourZoneKey: {
        $first: "$_id.hourZoneNumber"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.nodeId",
      "endpoints": {
        "$addToSet": {
          "endpointId": "$_id.endpointId",
          "hourZones": "$hourZones"
        }
      },
      endpointKey: {
        $first: "$_id.endpointId"
      },
      hourZoneKey: {
        $first: "$hourZoneKey"
      }
    }
  },
  {
    "$sort": {
      "nodeId": 1,
      "endpointKey": 1,
      "hourZoneKey": 1
    }
  },
  {
    $project: {
      _id: 0,
      nodeId: "$_id",
      endpoints: 1,
      hourZones: 1,
      total: 1
    }
  }
])

You can see it here