CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Michał Jurczuk
Michał Jurczuk

Reputation: 3828

MongoDB return two object for every group

I want to get two objects $first and $last after grouping. Is it possible?

Something like this, but this is not working:

{ "$group": {
  "_id": "type",
  "values": [{
        "time": { "$first": "$time" },
        "value": { "$first": "$value" }
        }, 
        {
          "time": { "$last": "$time" },
          "value": { "$last": "$value" }
        }]
   }
}

Upvotes: 1

Views: 453

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151170

In order to get the $first and $last values from an array with the aggregation framework, you need to use $unwind first to "de-normalize" the array as individual documents. There is also another trick to put those back in an array.

Assuming a document like this

{
    "type": "abc",
    "values": [
        { "time": ISODate("2014-06-12T22:35:42.260Z"), "value": "ZZZ" },
        { "time": ISODate("2014-06-12T22:36:45.921Z"), "value": "KKK" },
        { "time": ISODate("2014-06-12T22:37:18.237Z"), "value": "AAA" } 
    ]
}

And assuming that your array is already sorted your would do:

If you do not care about the results being in an array just $unwind and $group:

db.junk.aggregate([
    { "$unwind": "$values" },
    { "$group": {
        "_id": "$type",
        "ftime":  { "$first": "$values.time" },
        "fvalue": { "$first": "$values.value" },
        "ltime":  { "$last": "$values.time" },
        "lvalue": { "$last": "$values.value" },
    }}
])

For those results in array then there is a trick to it:

db.collection.aggregate([
    { "$unwind": "$values" },
    { "$project": {
        "type": 1,
        "values": 1,
        "indicator": { "$literal": ["first", "last"] }
    }},
    { "$group": {
        "_id": "$type",
        "ftime":  { "$first": "$values.time" },
        "fvalue": { "$first": "$values.value" },
        "ltime":  { "$last": "$values.time" },
        "lvalue": { "$last": "$values.value" },
        "indicator": { "$first": "$indicator" }
    }},
    { "$unwind": "$indicator" },
    { "$project": {
        "values": {
            "time": {
                "$cond": [
                    { "$eq": [ "$indicator", "first" ] },
                    "$ftime",
                    "$ltime"
                ]
            },
            "value": {
                "$cond": [
                    { "$eq": [ "$indicator", "first" ] },
                    "$fvalue",
                    "$lvalue"
                ]
            }
        }
    }},
    { "$group": {
        "_id": "$_id",
        "values": { "$push": "$values" }
    }}
])

If your array is not sorted place an additional $sort stage before the very first $group to make sure your items are in the order you want them to be evaluated by $first and $last. A logical order where is by the "time" field, so:

{ "$sort": { "type": 1, "values.time": 1 } }

The $literal declares an array to identify the values of "first" and "last" which are later "unwound" to create two copies of each grouped document. These are then evaluated using the $cond operator to re-assign to a single field for "values" which is finally push back into an array using $push.

Remember to allways try to $match first in the pipeline in order to reduce the number of documents you are working on to what you reasonable want. You pretty much never want to do this over whole collections, especially when you are using $unwind on arrays.

Just as a final note $literal is introduced/exposed in MongoDB 2.6 and greater versions. For prior versions you can interchange that with the undocumented $const.

Upvotes: 1

Related Questions