0

I am new to Mongodb trying to count the number of Delta values in DELTA_GROUP. In this example there are three "Delta" values under "DELTA_GROUP". In this case, the count value is 3 for this object. I need to satisfy two conditions here though.

First of all, I need to only count data collected within the specific time range I set (ex. between start point and end point using ISODate(gte,lte, etc)).

The Second, in the data with the specific time range, I want to count the number of delta values for every object and of course, there are a handful of objects within the specified time range. Thus, if I assume that there are only three delta values for each object (from example), and 10 objects total, the count result should be 30. How can I create a query for it with conditions above?

{
    "_id" : ObjectId("5f68a088135c701658c24d62"),
    "DELTA_GROUP" : [ 
        {
            "Delta" : 105,
        }, 
        {
            "Delta" : 108,
        }, 
        {
            "Delta" : 103,
        }
],
    "YEAR" : 2020,
    "MONTH" : 9,
    "DAY" : 21,
    "RECEIVE_TIME" : ISODate("2020-09-21T21:46:00.323Z")
}

What I have tried so far is shown below. In this way, I was able to list out counted value for each object, but still need to work to get totalized counted value for specified range of dates.

db.DELTA_DATA.aggregate([
{$match: 
    {'RECEIVE_TIME': 
        {
            $gte:ISODate("2020-09-10T00:00:00"),
            $lte:ISODate("2020-10-15T23:59:59")
        }
    }},
{$project: {"total": {count : {"$size":"$DELTA_GROUP"}}}}])
Brian Lee
  • 173
  • 3
  • 14
  • You need to write an aggregation query for this. To filter documents in an array and find their size there are aggregation array operators like `$filter`, `$size`, etc. See [Aggregation Array Operators](https://docs.mongodb.com/v4.2/reference/operator/aggregation/#array-expression-operators) – prasad_ Nov 24 '20 at 02:56
  • @prasad_ I mean, I am trying to sum up all size values of arrays across all documents.. Could you give me a short example how to write it? – Brian Lee Nov 26 '20 at 02:10
  • The aggregation query need to have two stages. (1) Match the date, (2) Count the array elements. For matching the dates see these two sample posts: [Mongo: dates in match aggregate query seem to be ignored](https://stackoverflow.com/questions/15024383/mongo-dates-in-match-aggregate-query-seem-to-be-ignored) _and_ [Is it possible to use a MongoDB aggregate query with date matching?](https://stackoverflow.com/questions/32479215/is-it-possible-to-use-a-mongodb-aggregate-query-with-date-matching). – prasad_ Nov 26 '20 at 03:10
  • ... For counting the array size, see this post: [MongoDB: count the number of items in an array](https://stackoverflow.com/questions/21387969/mongodb-count-the-number-of-items-in-an-array). – prasad_ Nov 26 '20 at 03:10
  • @prasad_ I have figured how to list them out by object, but still I need to figure out a way to sum up all counted values over objects. I modified the question. – Brian Lee Nov 26 '20 at 06:32
  • Use the `$sum` aggregation operator within the `$project`: `sum: { $sum: "$DELTA_GROUP.Delta" }` – prasad_ Nov 26 '20 at 06:55
  • @prasad_ Oh, sorry if I was confusing. The number of elements in array for each object was meant to be summed over all documents, not the delta value itself. – Brian Lee Nov 26 '20 at 07:26
  • Then use it in a `$group` stage - grouping of all documents. See [Examples](https://docs.mongodb.com/v4.2/reference/operator/aggregation/group/index.html#examples) – prasad_ Nov 26 '20 at 07:40

0 Answers0