I am learning mongo and I am trying to provide two metrics for given user in given time range. Precisely I need to calculate this kind of array of objects that represents state of the backpack for the particular day:

{ 
  data: [
    { date: '2020-01-01', itemsCount: 1, itemsSize: 5 },
    { date: '2020-01-02', itemsCount: 3, itemsSize: 12 },
    ...

  ]
} 

where itemsCount is the total number of all user items and itemsSize is the sum of sizes of all items.

I have a mongodb collection of four types of events with the structure as below:

{
  type: "backpack.created"    // type of event
  backpackId: 1,
  timestamp: 1604311699,      // timestamp in seconds when event occurred
  ownerId: 1,
  size: 15,                   // sum of sizes of all items located in the backpack
  itemsCount: 5               // number of items in the backpack                    
}

{
  type: "backpack.owner.changed",    
  timestamp: 1604311699, 
  newOwnerId: 2,
  backpackId: 1,                    
}

{
  type: "backpack.deleted",
  backpackId: 1,
  timestamp: 1604311699,               
}

{
  type: "backpack.updated",
  backpackId: 1,
  size: 5,
  itemsCount: 25,
  timestamp: 1604311699,                             
}

First idea to solve the problem was to load all the events for given user and time range in memory and do calculations, but this sounds terrible to my memory. So I am wondering how to do such a query that will provide me given metrics? And is it possible to do it with mongo? I do not know how to handle ownership changes in this.

Note: backpack created and deleted same day means it's contribution for that day is 0.

CodePudding user response：

I do not believe what you wish to do, which is create a cross-backpack position by day, is fully served by a mongodb pipeline. The reason is that you need to track state day over day so that when, say, 3 days from now a backpack.deleted event occurs, you know how much to delete from the running aggregate position.

That said, mongodb can help you in 2 ways:

1. Act as a master filter of events for a range and excluding owner.changed which does not affect position.
2. A convenient "last event" of the day generator. Since update has new total levels, not incremental, the last update of the day is the new position; if the last event is delete, the position for that backpack becomes zero.

var sdate = new ISODate("2020-11-01");
var edate = new ISODate("2020-12-01");

c=db.foo.aggregate([
    // Convert timestamp into something more filterable:
    {$addFields: {D: {$toDate: {$multiply:[1000,"$timestamp"]} } }}

    // Use DB to do what it does best: filter:
    ,{$match: {type: {$ne: 'backpack.owner.changed'},
               D: {$gte: sdate, $lt: edate}
              }}

    // Ensure material is coming out date DESCENDING (most recent first):
    ,{$sort: {D:-1}}

    ,{$group: {_id: {
        D: {$dateToString: {format: '%Y-%m-%d', date:'$D'}},
        B: '$backpackId'
    }
               // Thank to the $sort above, regardless of the $group set
               // ordering of date   backpackId, taking the $first is the
               // last one for that particular day:
               , Lsize: {$first: '$size'}
               , LitemsCount: {$first: '$itemsCount'}
               , Laction: {$first: '$type'}
              }}

    // Optional but helpful to the eye:
    ,{$sort: {'_id.D':1,'_id.B':1}}
]);

At this point you end up with something like this:

{
    "_id" : {
        "D" : "2020-11-02",
        "B" : 1
    },
    "Lsize" : 8,
    "LitemsCount" : 28,
    "Laction" : "backpack.updated"
}
{
    "_id" : {
        "D" : "2020-11-02",
        "B" : 2
    },
    "Lsize" : 15,
    "LitemsCount" : 5,
    "Laction" : "backpack.created"
}
{
    "_id" : {
        "D" : "2020-11-03",
        "B" : 2
    },
    "Lsize" : 7,
    "LitemsCount" : 11,
    "Laction" : "backpack.updated"
}
{
    "_id" : {
        "D" : "2020-11-04",
        "B" : 1
    },
    "Lsize" : null,
    "LitemsCount" : null,
    "Laction" : "backpack.deleted"
}

It is left as an exercise to the reader to walk this cursor and for each date backpackId, accumulate a sum of size and itemsCount by backpackId. Any time a deleted event is hit, on that day the sum goes to zero. To get size and itemsCount from all the backpacks, simply ask for all the sums on a given date. Moving the agg logic out of MongoDB also makes it easier to represent date aggregates for which there is no material, e.g.:

    { date: '2020-01-01', itemsCount: 1, itemsSize: 5 },
    { date: '2020-01-02', itemsCount: 0, itemsSize: 0 },
    { date: '2020-01-03', itemsCount: 0, itemsSize: 0 },
    { date: '2020-01-04', itemsCount: 6, itemsSize: 21},
    ...