I have a collection which stores data every 2 seconds for every uID (which is the primary key). Now I want to query last 30 documents for each uID and group them together according to their index number. How to proceed in this case? If I apply $limit, this returns only 30 documents for all uIDs.

db.getCollection("devices").aggregate(
    [
        { 
            "$match" : { 
                "uID" : { 
                    "$in" : [
                        "20200308", 
                        "20200306", 
                        "12345678"
                    ]
                }
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);

The above query will return me all the documents, how to limit query to 30 doc per uID? Also if query is successful, how to group all the array indexes of distinct documents so that I get the sum of values of a field which has 30 documents grouped together. Example:

[
    {
        _id: 0,  // index value of all array after grouping them.
        sumOfValuesFoundInArrayIndex: 100,
    },
    {
        _id: 1,
        sumOfValuesFoundInArrayIndex: 600,
    }
    .
    .
    .
    .
    {
        _id: 29,
        sumOfValuesFoundInArrayIndex: 600,
    }
]

JSON example:

[
  {
    "timeStamp": 1644962269,
    "uID": "20200308",
    "capacityLeft": 500
  },
  {
    "timeStamp": 1644962272,
    "uID": "20200308",
    "capacityLeft": 499
  },
  {
    "timeStamp": 1644962275,
    "uID": "20200306",
    "capacityLeft": 300
  },
  {
    "timeStamp": 1644962277,
    "uID": "20200308",
    "capacityLeft": 499
  },
  {
    "timeStamp": 1644962277,
    "uID": "20200306",
    "capacityLeft": 300
  },
  {
    "timeStamp": 1644962279,
    "uID": "12345678",
    "capacityLeft": 753
  },
  {
    "timeStamp": 1644962281,
    "uID": "12345678",
    "capacityLeft": 752
  },
  {
    "timeStamp": 1644962283,
    "uID": "12345678",
    "capacityLeft": 751
  }
]

Now according the the JSON, I need to find 30 docs for each uID and sort them with timeStamp so that when I query for all my mentioned devices I get a last 30 docs for the mentioned uIDs, group them by their array index and then sum all the capacity left.

CodePudding user response：

It looks to me something like this:

db.collection.aggregate([
{
 $match: {
  uID: {
    $in: [
      "20200308",
      "20200306"
    ]
  }
 }
},
{
 $sort: {
  uID: 1,
  "timeStamp": -1
 }
},
{
 $group: {
  _id: "$uID",
  ts: {
    $push: "$$ROOT"
  }
 }
},
{
 $project: {
  ts: {
    $slice: [
      "$ts",
      30
    ]
  }
 }
},
{
  $unwind: "$ts"
},
{
 $group: {
  _id: "$_id",
  sumcapLast30: {
    $sum: "$ts.capacityLeft"
  }
 }
}
])

Explained:

1. Match all needed uID
2. Sort by uID and desc by timeStamp
3. group by uID
4. splice and leave only the first 30 elements from the ts array.
5. unwind the ts array
6. Group and sum the first 30 elements per uID

playground