Is there a way to get sum of field with a condition like WHERE (SQL) in MongoDB

Question

I'm looking for a way to get sum of a field in my mongodb. My DB looks like this:

{
    "_id" : ObjectId("553f35b8bfabe235158b4f4b"),
    "event_id" : NumberLong(342),
    "ticket_id" : NumberLong(85326),
    "ticket_code" : NumberLong(3445345345343),
    "ticket_type_id" : NumberLong(42),
    "ticket_created" : NumberLong(1429866042),
    "ticket_deleted" : NumberLong(0),
    "ticket_user_id" : NumberLong(3207),
    "ticket_used" : NumberLong(0),
    "order_id" : NumberLong(6988),
    "order_code" : NumberLong(444234),
    "order_created" : NumberLong(1429866042),
    "order_deleted" : NumberLong(0),
    "order_sales_pipeline" : NumberLong(1),
    "order_invoice_id" : NumberLong(4194),
    "order_invoice_amount" : 25000.0000000000000000,
    "order_invoice_created" : NumberLong(1429866053),
    "order_invoice_deleted" : NumberLong(0),
    "order_invoice_code" : NumberLong(419338),
    "payment_id" : NumberLong(4355),
    "payment_amount" : 25000.0000000000000000,
    "payment_currency" : NumberLong(2),
    "payment_author_id" : NumberLong(159),
    "payment_type_id" : NumberLong(2),
    "payment_created" : NumberLong(1429866053),
    "payment_deleted" : NumberLong(0),
    "create_time" : ISODate("2015-04-28T07:24:40.289Z")
}

I need to get sum of payment_amount field with conditions, I'd this in SQL like this:

SELECT SUM(payment_amount) as total FROM ticket_cache WHERE event_id = 342 AND ticket_type IN(84, 45, 365)

Is there a way to the same in mongodb?

more information please like your database structure and what result you want? — Preeti Maurya
– Preeti Maurya, Commented Apr 28, 2015 at 8:32
sure, its a aggregation function. look at here: docs.mongodb.org/manual/reference/operator/aggregation/sum — user3415653
– user3415653, Commented Apr 28, 2015 at 8:36

chridam · Accepted Answer · 2015-04-28 08:48:07Z

3

Yes, through mongodb's aggregation framework. For example, the following sums the price field from an orders collection where the documents fields match certain criteria:

db.orders.aggregate([
    {
        "$match": {
            "field1": "a",
            "field2": "b"
        }
    },
    {
        "$group": {
           "_id": null,
           "total": { "$sum": "$price" }
        }
   }
]);

Equivalent SQL

SELECT SUM(price) AS total
FROM orders
WHERE field1 = 'a' AND field2 = 'b'

For more examples, you could refer to the docs SQL to Aggregation Mapping Chart

-- UPDATE --

With your document schema, to get the sum of payment_amount field with the conditions event_id is 342 AND ticket_type IN (84, 45, 365), use the following aggregation pipeline which starts with a $match pipeline stage that filters the documents with a given criteria and then groups those filtered documents using the $group operator to calculate the sum of the field payment_amount:

var pipeline = [
    {
        "$match": {
            "event_id": 342,
            "ticket_type": { "$in": [84, 45, 365] }
        }
    },
    {
         "$group": {
             "_id": null,
             "total": { "$sum": "$payment_amount" }
         }
    }
];
db.collection.aggregate(pipeline);

edited Apr 28, 2015 at 8:48

answered Apr 28, 2015 at 8:40

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chridam Over a year ago

@Kossgreim No worries :-)

Uri Goren · Accepted Answer · 2015-04-28 08:50:32Z

0

You could use Mongo's map-reduce,

For example, this sql query:

SELECT SUM(payment_amount) as total FROM ticket_cache WHERE event_id = 342 AND ticket_type IN(84, 45, 365)

is equivalent to this map-reduce:

db.ticket_cache.mapReduce(
                     function () {
                       emit(this._id,
                         (([84, 45, 365].indexOf(this.ticket_type)>-1)&&(this.event_id==342) ?
                         this.payment_amount : 0) );
                     },//mapper
                     function(id, payment_amount) {
                        return Array.sum(payment_amount);
                     },//reducer
                     { out: "conditional_sum_example" }
                   )

edited Apr 28, 2015 at 8:50

answered Apr 28, 2015 at 8:37

Uri Goren

13.8k8 gold badges63 silver badges113 bronze badges

3 Comments

user3415653 Over a year ago

it is possible with a map-reduce, but not the easiest way. i think the aggregation pipeline is a better way to do a sum

Uri Goren Over a year ago

better in what way ? under the hood things run as map-reduce in mongo as well as in other nosql dbs.

user3415653 Over a year ago

yes this is true, but its harder to understand a map reduce as an simple aggregation query and a the query should be faster as an map-reduce

Indrajeet Singh · Accepted Answer · 2015-05-05 09:18:29Z

0

Try:
db.ticket_cache.aggregate(
   [
     { $match : { event_id : 342, ticket_type : { $in: [84, 45, 365] }}},
     {
       $group:
         {
           _id: { event_id: "$event_id" },
           totalAmount: { $sum: $payment_amount },
           count: { $sum: 1 }
         }
     }
   ]
)

answered May 5, 2015 at 9:18

Indrajeet Singh

2,99927 silver badges28 bronze badges

Collectives™ on Stack Overflow

Is there a way to get sum of field with a condition like WHERE (SQL) in MongoDB

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related