在MongoJava中按組對多級陣列進行排序

我有以下架構的檔案

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

我正在嘗試查找與 nodeId 匹配的檔案，并嘗試匯總每個 hourZoneNumber 的 customerCount、itemQuantity 和金額。

下面是查詢

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
    }
  }
])

輸出如下：

{
  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") } ]
}

我希望輸出排序為：首先按nodeId排序，然后按端點內的endpointId排序，最后按hourZones中的hourZoneNumber排序。

我該怎么做呢 ？我嘗試對所有三個欄位使用 sort()。但它沒有用。另外，有人可以確認是否有比上述代碼更好的方法，因為我是 Mongo DB 的新手。

編輯：請在https://mongoplayground.net/p/FYm3QMMgrNI找到示例輸入資料

uj5u.com熱心網友回復：

由于您在開始時已經有了分離的資料，因此只需通過分組保存這些值，然后最后按它們排序。

編輯：為了對每個內部陣列進行排序，我們在每個內部和之前使用$push而不是：$addToSet$group$sort$group

db.collection.aggregate([
  {
    "$match": {
      "nodeId": {"$in": ["STORE_DEV_TTEC", "STORE_DEV_TTEZ"]
      },
      "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"}
    }
  },
  {"$sort": {"_id.hourZoneNumber": 1}
  },
  {
    "$group": {
      "_id": {
        "nodeId": "$_id.nodeId",
        "endpointId": "$_id.endpointId"
      },
      "hourZones": {
        "$push": {
          "hourZoneNumber": "$_id.hourZoneNumber",
          "customerCount": {"$sum": "$customerCount"},
          "itemQuantity": {"$sum": "$itemQuantity"},
          "amount": {"$sum": "$amount"}
        }
      },
      hourZoneKey: {$first: "$_id.hourZoneNumber"}
    }
  },
  {"$sort": {"_id.endpointId": 1}
  },
  {
    "$group": {
      "_id": "$_id.nodeId",
      "endpoints": {
        "$push": {
          "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}
  }
])

你可以在這里看到

標籤：mongodb 排序 mongodb查询 聚合框架

上一篇：需要滿足兩個條件的Javascript排序函式

下一篇：QMLListView模型setProperty僅作業1次