Aggregate $sum with condition on sum limit in mongo query

Working with Data
1

I have requirement to update the data based on the inputs using recordID , each recordID has count of associated records. since associated records has huge volume of data , I wanted to control the number of update based on the associated records count via configuration. So in the query, i want to sum the associated document count and should not cross limit configured in property. I wanted to fetch the records using $sum (aggregation) operation in the aggregate query but not sure how to add the criteria.

{ 
    "_id" : ObjectId("5f8f52168"), 
    "recordID" : "11989", 
    "count" : NumberInt(5)
}
{ 
    "_id" : ObjectId("5f8f52148"), 
    "recordID" : "2561", 
    "count" : NumberInt(10)
 }
{ 
    "_id" : ObjectId("5f8f52038"), 
    "recordID" : "57546", 
    "count" : NumberInt(30)
}
{ 
    "_id" : ObjectId("5f8f52138"), 
    "recordID" : "12623", 
    "count" : NumberInt(40)
}
{ 
    "_id" : ObjectId("5f8f52188"), 
    "recordID" : "199429", 
    "count" : NumberInt(50) 
}
{ 
    "_id" : ObjectId("5f8f52148a"), 
    "recordID" : "12793", 
    "count" : NumberInt(60), 
}

Example1: Let’s say when totalcount<=50) I need to fetch the documents, in which total count sum less than or equal 50 (totalsum<=50), it should return below documents.

count <=50 ( count" = 5 + 10 + 30 )

{ 
    "_id" : ObjectId("5f8f52168"), 
    "recordID" : "11989", 
    "count" : NumberInt(5)
}
{ 
    "_id" : ObjectId("5f8f52148"), 
    "recordID" : "2561", 
    "count" : NumberInt(10)
 }
{ 
    "_id" : ObjectId("5f8f52038"), 
    "recordID" : "57546", 
    "count" : NumberInt(30)
}

Example2 : when totalSum<=70, query should return below documents.

count <=70 ( count : 30 + 35 = 65 )

{ 
    "_id" : ObjectId("5f8f52038"), 
    "recordID" : "57546", 
    "count" : NumberInt(30)
}
{ 
    "_id" : ObjectId("5f8f52138"), 
    "recordID" : "12623", 
    "count" : NumberInt(35)
}

Mongo query some thing

db.records.aggregate({ $match: {} },
{ $group: { _id : null, sum : { $sum: "$count" } 
  --where sum<=50
} });
2

Hi @Imran_khan,

I am a bit confused by the requirements and the provided example. On one hand you do not have documents with count 35 bit in the example you do?

Additionally, why documents 5, 10 are not included in under 70 update?

In general I expect that together with count stage you will need to $push the individual objects and unwind them following by $match

I can help more once I understand the use case.

Best
Pavel

3

@Pavel_Duchovny : I have requirement to sum the count value but with in the limit configured .

Example: Let’s say , i have below documents.

{ 
    "_id" : ObjectId("5f8f52168"), 
    "recordID" : "11989", 
    "count" : NumberInt(5)
}
{ 
    "_id" : ObjectId("5f8f52148"), 
    "recordID" : "2561", 
    "count" : NumberInt(10)
 }
{ 
    "_id" : ObjectId("5f8f52038"), 
    "recordID" : "57546", 
    "count" : NumberInt(30)
}
{ 
    "_id" : ObjectId("5f8f52138"), 
    "recordID" : "12623", 
    "count" : NumberInt(40)
}
{ 
    "_id" : ObjectId("5f8f52188"), 
    "recordID" : "199429", 
    "count" : NumberInt(50) 
}
{ 
    "_id" : ObjectId("5f8f52148a"), 
    "recordID" : "12793", 
    "count" : NumberInt(60), 
}

Now : my CountLimit 50 , Now many mongo query should pick all the records , so total sum of count should be less or equal to countLimint.

db.records.aggregate({ $match: {} },
{ $group: { _id : null, sum : { $sum: "$count" } 
  --where sum<=50
} });

it should return the result where total sum of count <=50

{ 
    "_id" : ObjectId("5f8f52168"), 
    "recordID" : "11989", 
    "count" : NumberInt(5)
}
{ 
    "_id" : ObjectId("5f8f52148"), 
    "recordID" : "2561", 
    "count" : NumberInt(10)
 }
{ 
    "_id" : ObjectId("5f8f52038"), 
    "recordID" : "57546", 
    "count" : NumberInt(30)
}


"count" : NumberInt(5) + NumberInt(10) + NumberInt(30) = 45 ( should not cross the upper limit)
4

Hi @Imran_khan,

So you need to do kind of a rollup count… Where you count all previous documents and exit once you got to the limit point.

It sounds like this is more suitable to a mapReduce operation rather than aggregation.

In 4.4 this will be possible with a $accumulator function:

https://docs.mongodb.com/manual/reference/operator/aggregation/accumulator/#grp._S_accumulator

However, not sure what MongoDB version you on and if map reduce is a possibility for you?

Best
Pavel

5

@Pavel_Duchovny : Thanks for the information. Currently we are using : 4.2.8 mongo version.

6

Hi did you resolve this i’m facing a similar predicament and i can’t figure out the pipeline

7

Hello @David_Meck, Welcome to the MongoDB Community forum!

If using the MongoDB v4.2 or lesser, you can try this logic and code an aggregation query:

sort by the field `count` ascending
group the documents into an array
iterate the array using the `$reduce` array operator
    for each iteration, 
        keep a running total of the `count` field
        compare the running total with the `count_limit`
        if less
            add the document to an `array_of_matching_documents`
            next iteration
        else 
            do nothing
            next iteration

The array_of_matching_documents has the documents which are within the count_limit - use this for further processing.

8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.