Return a field that has an array in mongoDB, and return the first and last value in that array

Question

Scenario: Consider the document present in the MongoDB in collection named twitCount.

{
"_id" : ObjectId("53d1340478441a1c0d25c40c"),
"items" : [ 
    {
        "date" : ISODate("2014-07-22T22:18:05.000Z"),
        "value" : 4,
        "_id" : ObjectId("53d134048b3956000063aa72")
    }, 
    {
        "date" : ISODate("2014-07-21T22:09:20.000Z"),
        "value" : 10,
        "_id" : ObjectId("53d134048b3956000063aa71")
    }
   ...
],
"ticker" : "OM:A1M"

}

I only want to fetch the first and last date inside "items". I've tried lot of different "queries". But I cannot get it right. The "ticker" is unique

The following query is the only one that returns something, but it returns everything(that is expected).

    twitCount.aggregate([{ $match : { ticker: theTicker}} ], function(err, result){
                if (err) {
                  console.log(err);
                    return;
                }
             console.log(result)


            })

So, In the end I want the query to return it something like this [2013-02-01, 2014-07-24]; I really need help with this, all links on manual/core/aggregation are purple and I don't know where to get more information.

I'm lazy and would just project the entire array and then perform a shift and a pop on the result ^.^ — Jake Sellers, Jul 24 '14 at 17:27
Mongodb has a [$pop operator](http://docs.mongodb.org/manual/reference/operator/update/pop/) and one of the examples is removing the first element of the array, I'll post an actual answer if I can make it work but for now play with that. — Jake Sellers, Jul 24 '14 at 17:30
Also here is an answer using slice to solve a similar problem: http://stackoverflow.com/questions/7223273/get-n-th-element-of-an-array-in-mongo — Jake Sellers, Jul 24 '14 at 17:34
@JakeSellers You first statement might well be correct but the other two do not apply. `$pop` will modify the stored document in the collection and `$slice` cannot return both "first" and "last" at the same time in one query. So the choices really come down to JavaScript for one document or explaining the aggregation process for multiple documents. — Neil Lunn, Jul 25 '14 at 04:10
Agreed, $pop was immediately out after reading the docs, and I spent some time playing with $slice and different combinations but couldn't make it work. — Jake Sellers, Jul 25 '14 at 15:03

Neil Lunn · Accepted Answer · 2014-07-25T04:10:40.803

Hard to tell if your intent here is to work with a single document or multiple documents that match your condition. As suggested, a single document would really just involve using the shift and pop methods native to JavaScript on the singular result to get the first and last elements of the array. You might also need to employ array sort here

 twitCount.findOne({ "ticker": "OM:A1M" },function(err,doc) {

     doc.items = doc.items.sort(function(a,b) {
         return ( a.date.valueOf() > b.date.valueOf() ) ? 1
            : ( a.date.valueOf() < b.date.valueOf() ) ? -1 : 0;
     });
     doc.items =  [doc.items.shift(),doc.items.pop()];

     console.log( doc );
 })

The other suggestions don't really apply as operators like $pop permanently mondify the array in updates. And the $slice operator that can be used in a query would really only be of use to you if the array contents are already sorted, and additionally you would be making two queries to return first and last, which is not what you want.

But if you really are looking to do this over multiple documents then the aggregation framework is the answer. The key area to understand when working with arrays is that you must use an $unwind pipeline stage on the array first. This "de-normalizes" to a form where a copy of the document is effectively produced for each array element:

twitCount.aggregate([
    // Match your "documents" first
    { "$match": { "ticker": "OM:A1M" } },

    // Unwind the array
    { "$unwind": "$items" },

    // Sort the values
    { "$sort": { "items.date": 1 } },

    // Group with $first and $last items
    { "$group": {
       "_id": "$ticker",
       "first": { "$first": "$items" },
       "last": { "$last": "$items" }
    }}
],function(err,result) {

If you really want "items" back as an array then you can just do things a little differently:

twitCount.aggregate([
    // Match your "documents" first
    { "$match": { "ticker": "OM:A1M" } },

    // Unwind the array
    { "$unwind": "$items" },

    // Sort the values
    { "$sort": { "items.date": 1 } },

    // Group with $first and $last items
    { "$group": {
       "_id": "$ticker",
       "first": { "$first": "$items" },
       "last": { "$last": "$items" },
       "type": { "$first": { "$const": [true,false] } }
    }},

    // Unwind the "type"
    { "$unwind": "$type" },

    // Conditionally push to the array
    { "$group": {
        "_id": "$_id",
        "items": {
           "$push": {
               "$cond": [
                   "$type",
                   "$first",
                   "$last"
               ]
           }
        }
    }}
],function(err,result) {

Or if your $match statement is just intended to select and you want the "first" and "last" from each document "_id" then you just change the key in the initial $group to "$_id" rather than the "$ticker" field value:

twitCount.aggregate([
    // Match your "documents" first
    { "$match": { "ticker": "OM:A1M" } },

    // Unwind the array
    { "$unwind": "$items" },

    // Sort the values
    { "$sort": { "items.date": 1 } },

    // Group with $first and $last items
    { "$group": {
       "_id": "$_id",
       "ticker": { "$first": "$ticker" },
       "first": { "$first": "$items" },
       "last": { "$last": "$items" },
       "type": { "$first": { "$const": [true,false] } }
    }},

    // Unwind the "type"
    { "$unwind": "$type" },

    // Conditionally push to the array
    { "$group": {
        "_id": "$_id",
        "ticker": { "$first": "$ticker" },
        "items": {
           "$push": {
               "$cond": [
                   "$type",
                   "$first",
                   "$last"
               ]
           }
        }
    }}
],function(err,result) {

In that last case, you would get something like this, based on the data you have provided:

{
    "_id" : ObjectId("53d1340478441a1c0d25c40c"),
    "ticker" : "OM:A1M",
    "items" : [
            {
                    "date" : ISODate("2014-07-21T22:09:20Z"),
                    "value" : 10,
                    "_id" : ObjectId("53d134048b3956000063aa71")
            },
            {
                    "date" : ISODate("2014-07-22T22:18:05Z"),
                    "value" : 4,
                    "_id" : ObjectId("53d134048b3956000063aa72")
            }
    ]
}

You can find the Full List of Aggregation Operators in the documentation. It is worth getting to know how these function as depending on what you are doing the aggregation framework can be a very useful tool.

Return a field that has an array in mongoDB, and return the first and last value in that array

1 Answers1