Aggregation ($sum) of array elements based on array index?

[deha....@gmail.com]

Is it possible to aggregate ($sum) array elements using array indexes.

For example, for the following collection with 3 documents as below,

/* 1 */

{ "acct": 1,"asOf":ISODate("2012-07-14T01:00:00+01:00"),"ccy": "USD","gc": 1,

"tS" : [

  {"1":[0,1,2,3]},

  {"2":[0,1,2,3]},

  {"3":[0,1,2,3]}

 ]

}

/* 2 */

{ "acct": 1,"asOf":ISODate("2012-07-14T01:00:00+01:00"),"ccy": "USD","gc": 2,

"tS" : [

  {"1":[0,1,2,3]},

  {"2":[0,1,2,3]},

  {"3":[0,1,2,3]}

 ]

}

/* 3 */

{ "acct": 1,"asOf":ISODate("2012-07-14T01:00:00+01:00"),"ccy": "USD","gc": 3,

"tS" : [

  {"1":[0,1,2,3]},

  {"2":[0,1,2,3]},

  {"3":[0,1,2,3]}

 ]

}

 Is it possible to get the sum all array elements for "ts.1" for all three documents, sum could work on array indices? 

 db.pbo.aggregate(

    [

        { $project : {"_id": 0,"acct":1,"asOf":1,"ccy":1,"gc":1,"tS1" :"$tS.1"} },

        {$unwind: "$tS1"},

        { $group : { "_id": {"acct" : "$acct" ,"asOf" : "$asOf" ,"ccy" : "$ccy" },

            "tS10": {"$sum":"$tS1[0]"}, "tS11": {"$sum":"$tS1[1]"}, "tS12": {"$sum":"$tS1[2]"}

         }}

])  

[Asya Kamsky]

Yes, you can sum all the elements for tS.1 but only because it's a
named field here (not an array index):

> db.array.find()
{ "_id" : ObjectId("53f27c63b841c46daea2ca19"), "acct" : 1, "asOf" :
ISODate("2012-07-14T00:00:00Z"), "ccy" : "USD", "gc" : 1, "tS" : [ {

"1" : [ 0, 1, 2, 3 ] }, { "2" : [ 0, 1, 2, 3 ] }, { "3" : [ 0, 1, 2, 3
] } ] }

{ "_id" : ObjectId("53f27c70b841c46daea2ca1a"), "acct" : 1, "asOf" :
ISODate("2012-07-14T00:00:00Z"), "ccy" : "USD", "gc" : 2, "tS" : [ {

"1" : [ 0, 1, 2, 3 ] }, { "2" : [ 0, 1, 2, 3 ] }, { "3" : [ 0, 1, 2, 3
] } ] }

{ "_id" : ObjectId("53f27c7db841c46daea2ca1b"), "acct" : 1, "asOf" :
ISODate("2012-07-14T00:00:00Z"), "ccy" : "USD", "gc" : 3, "tS" : [ {

"1" : [ 0, 1, 2, 3 ] }, { "2" : [ 0, 1, 2, 3 ] }, { "3" : [ 0, 1, 2, 3
] } ] }

> db.array.aggregate({$unwind:"$tS"},{$unwind:"$tS.1"}, {$group:{_id:1,sum1:{$sum:"$tS.1"}}})
{ "_id" : 1, "sum1" : 18 }

Asya

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user"
> group.
>
> For other MongoDB technical support options, see:
> http://www.mongodb.org/about/support/.
> ---
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mongodb-user...@googlegroups.com.
> To post to this group, send email to mongod...@googlegroups.com.
> Visit this group at http://groups.google.com/group/mongodb-user.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mongodb-user/80948efe-92b5-4a84-8ef1-43c1f8693e3b%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[deha....@gmail.com]

Hi Asya, yes I already tried modelling the array elements as named fields, it is working. 

But that adds up some overhead on the record size because each document has kind of 2 dimension array 30 x 60.

I am wondering if any plan in MongoDB to provide capability to do aggregation based on array index for fixed array sizes.

[Asya Kamsky]

It's probably going to be possible once this ticket is implemented, but I don't think it will be the most efficient way to do it anyway...

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a9bff91c-5b7f-49fe-85b4-0c4b07cb3440%40googlegroups.com.