CODEWITHSUNDEEP
mongodbmongodb-query
y ramesh rao
y ramesh rao

Reputation: 3004

MongoDB Group querying for Embeded Document

I have a mongo document which has structure like

{
"_id" : "THIS_IS_A_DHP_USER_ID+2014-11-26",
"_class" : "weight",
"items" : [
    {
        "dateTime" : ISODate("2014-11-26T08:08:38.716Z"),
        "value" : 98.5
    },
    {
        "dateTime" : ISODate("2014-11-26T08:18:38.716Z"),
        "value" : 95.5
    },
    {
        "dateTime" : ISODate("2014-11-26T08:28:38.663Z"),
        "value" : 90.5
    }
],
"source" : "MANUAL",
"to" : ISODate("2014-11-26T08:08:38.716Z"),
"from" : ISODate("2014-11-26T08:08:38.716Z"),
"userId" : "THIS_IS_A_DHP_USER_ID",
"createdDate" : ISODate("2014-11-26T08:38:38.776Z")
}
{
"_id" : "THIS_IS_A_DHP_USER_ID+2014-11-25",
"_class" : "weight",
"items" : [
    {
        "dateTime" : ISODate("2014-11-25T08:08:38.716Z"),
        "value" : 198.5
    },
    {
        "dateTime" : ISODate("2014-11-25T08:18:38.716Z"),
        "value" : 195.5
    },
    {
        "dateTime" : ISODate("2014-11-25T08:28:38.716Z"),
        "value" : 190.5
    }
],
"source" : "MANUAL",
"to" : ISODate("2014-11-25T08:08:38.716Z"),
"from" : ISODate("2014-11-25T08:08:38.716Z"),
"userId" : "THIS_IS_A_DHP_USER_ID",
"createdDate" : ISODate("2014-11-26T08:38:38.893Z")
}

The query that want to fire on this document structure,

  1. finding documents for a particular user id
  2. unwinding the embedded array
  3. Grouping the documents based over _id with -
    1. summing the items.value of the embedded array
    2. getting the minimum of the items.dateTime of the embedded array

Note. The sum and min, I want to get as a object i.e. { value : sum , dateTime : min of the items.dateTime} inside an array of items

Can this be achieved in an single aggregation call using push or some other technique.

Upvotes: 0

Views: 123

Answers (2)

BatScream
BatScream

Reputation: 19700

When you group over a particular _id, and apply aggregation operators such as $min and $sum, there exists only one record per group(_id), that holds the sum and the minimum date for that group. So there is no way to obtain a different sum and a different minimum date for the same _id, which also logically makes no sense.

What you would want to do is:

db.collection.aggregate([
{$match:{"userId":"THIS_IS_A_DHP_USER_ID"}},
{$unwind:"$items"},
{$group:{"_id":"$_id",
         "values":{$sum:"$items.value"},
         "dateTime":{$min:"$items.dateTime"}}}
])

But in case when you do not query for a particular userId, then you would have multiple groups, each group having its own sum and min date. Then it makes sense to accumulate all these results together in an array using the $push operator.

db.collection.aggregate([
{$unwind:"$items"},
{$group:{"_id":"$_id",
         "result":{$sum:"$items.value"},
         "dateTime":{$min:"$items.dateTime"}}},
{$group:{"_id":null,"result":{$push:{"value":"$result",
                                     "dateTime":"$dateTime",
                                     "id":"$_id"}}}},
{$project:{"_id":0,"result":1}}
])

Upvotes: 1

Neo-coder
Neo-coder

Reputation: 7840

you should use following aggregation may it works 

db.collectionName.aggregate(
                           {"$unwind":"$items"},
                           {"$match":{"userId":"THIS_IS_A_DHP_USER_ID"}},
                          {"$group":{"_id":"$_id","sum":{"$sum":"$items.value"},
                          "minDate":{"$min":"$items.dateTime"}}}
                           )

Upvotes: 0

Related Questions