mongodb sorted by conditional element in a array

Question

I have a demand looks a little complex:

I have a collection in which the document has a array called startTimeArray stored the timeStamps of a event. Such as:

"startTimeArr" : [
    NumberLong("1425340800000"),
    NumberLong("1425513600000"),
    NumberLong("1426032000000"),
    NumberLong("1425427200000")
]

I want the find result to be sorted by the minimum Time stamp that greater than the current time(ie, time has not passed yet, the minimum on ).for example, if current time stamp in millisecond is 1425513500000 then the time stamp participate in the sorting action of this document is 1425513600000, which is the minimum stamp of the startTimeArr array that are greater than 1425513500000.

So The element of startTimeArr participate in the sorting action should be the one:

greater than the current time.
the minimum one that meet with #1

I guess I can do this with a aggregate such as:

 db.event.aggregate(
    {
      $match:{
         startTimeArr:{
              $gt: 1425513500000 // presume the current time is 1425513500000
           }
      }
    },
    {
      $group:{
          _id:"$_id", minTimeStamp:{?????} // and I don't know what to write here.
      }
    },
    {
      $sort:{minTimeStamp:1}
    }
 )

But I cant get a clue of how to write the minTimeStamp calculation.

Could you tell how to do this? Or if there are other ways to do this, will be appreciated too.

Since the problem is a bit too complex, I will just put some samples and outputs expected as required.

If we have 3 document like this:

{
  "_id" : ObjectId("537d98c20cf2264603faf0eb"),
  "name": "Let's go to LA",
  "fee": 500,
  "duration":2,
  "startTimeArr":[
    NumberLong("1425310000000"),
    NumberLong("1425320000000"),
    NumberLong("1425330000000"),
    NumberLong("1425380000000")
   ]
}
{
  "_id" : ObjectId("537caa7e0cf2264603faf0e9"),
  "name": "Let's go to NewYork",
  "fee": 800,
  "duration":3,
  "startTimeArr":[
    NumberLong("1425320000000"),
    NumberLong("1425330000000"),
    NumberLong("1425340000000"),
    NumberLong("1425350000000")
   ]
}
{
  "_id" : ObjectId("537c5ec50cf2264603faf0e7"),
  "name": "Let's go to Washington",
  "fee": 700,
  "duration":2,
  "startTimeArr":[
    NumberLong("1425350000000"),
    NumberLong("1425360000000"),
    NumberLong("1425370000000"),
    NumberLong("1425390000000")
   ]
}

And if current time is 1425300000000 then the time participate the sorting will be:

 LA         1425310000000
 NewYork    1425320000000
 Washington 1425350000000

So the find result of these three documents will be sorted as the order LA, NewYork, Washington

But when time moves on and the current time is 142534000000 then the time participate the sorting procedure will be:

LA         1425380000000
NewYork    1425350000000
Washington 1425350000000

So the find results expected to be sorted as the order NewYork, Washington, LA.

Could you please write more clearly what exactly do you want to get. Right now you are telling about find and doing aggregate which confuses me. — Salvador Dali
– Salvador Dali, Commented Apr 20, 2015 at 4:20
sorry about that, I'll try to edit, to make things more specific. — armnotstrong
– armnotstrong, Commented Apr 20, 2015 at 4:21
do you want the results in the startTimeArr to be sorted or all documents to be sorted? — Salvador Dali
– Salvador Dali, Commented Apr 20, 2015 at 4:35
@SalvadorDali all the document, in fact the startTimeArr is already sorted. — armnotstrong
– armnotstrong, Commented Apr 20, 2015 at 5:01
@armnotstrong To help me understand your problem more clearly, can you post some sample documents and the expected output/result? — chridam
– chridam, Commented Apr 20, 2015 at 8:45

Stennie · Accepted Answer · 2015-04-20 10:26:13Z

1

It looks like the aggregation operator you're missing is $min, which can be used in a $group step in your aggregation. You'll also need to $unwind the array of start times before grouping:

var timeNow = 1425300000000;  // set the time cut off for testing

db.event.aggregate(
    // Find documents with matching events
    // Note: could take advantage of an index to limit results
    { $match: {
        startTimeArr:{
            $gt: timeNow
        }
    }},

    // Convert start time array to stream of docs
    { $unwind: "$startTimeArr" },

    // Limit to matching array elements
    { $match: {
        startTimeArr:{
            $gt: timeNow
        }
    }},

    // Find the minimum timestamp in each matching document
    { $group:{
        _id:"$_id",
        name: { $first: "$name" },
        minTimeStamp:{ $min: "$startTimeArr" }
    }},

    // Sort in order of minimum timestamp (ascending)
    { $sort: { "minTimeStamp" : 1 }}
)

Sample output:

{
  "result": [
    {
      "_id": ObjectId("537d98c20cf2264603faf0eb"),
      "name": "Let's go to LA",
      "minTimeStamp": NumberLong("1425310000000")
    },
    {
      "_id": ObjectId("537caa7e0cf2264603faf0e9"),
      "name": "Let's go to NewYork",
      "minTimeStamp": NumberLong("1425320000000")
    },
    {
      "_id": ObjectId("537c5ec50cf2264603faf0e7"),
      "name": "Let's go to Washington",
      "minTimeStamp": NumberLong("1425350000000")
    }
  ],
  "ok": 1
}

edited Apr 20, 2015 at 10:26

answered Apr 20, 2015 at 9:26

Stennie

65.6k15 gold badges155 silver badges181 bronze badges

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

4 Comments

armnotstrong Over a year ago

Hi Stennie, thanks for answer, but I find that the sorting element which expected to be different according to different timeNow kept the same and so the result's order are kept the same pastebin.com/B2g7Z4dB

Stennie Over a year ago

@armnotstrong Sorry, there was a missing $match step after the $unwind. Edited to fix.

armnotstrong Over a year ago

It works! thank you, but can I get the output as the raw document rather than only the field that was grouped? ie, with fee and duration etc, but I don't want to specific every field in $group(since the fields may be change and added with demands grows)

Stennie Over a year ago

If you don't want to list all the fields in the $group step you can use the $$ROOT variable (available in MongoDB 2.6+). For example, replace name: { $first: "$name" } in the $group with fields: { $first: "$$ROOT" }. If you find yourself doing a lot of manipulation to get the common output you're after, you might want to rethink your schema to better accommodate these queries. FYI: Storing large arrays with unbounded growth can be a performance anti-pattern.

chridam · Accepted Answer · 2015-04-20 10:10:38Z

0

You will need to use the $min aggregation operator within the $group pipeline stage in order to get the minimum timestamp from the startTimeArr array values. However, you need to first deconstruct the startTimeArr array field from the filtered input documents (using a $match operator) to output a document for each element using the $unwind operator. In the $group pipeline stage, the $first operator enables you to bring back the other fields in your grouping that you can later project at the end. Thus your aggregation pipeline would look like this:

var date = new Date(),
    timestamp = +date;

db.event.aggregate([
    {
      "$match": {
         "startTimeArr": {
              "$gt": 1425300000000 // or use the timestamp variable to get the actual current timestamp
           }
      }
    },
    {
        "$unwind": "$startTimeArr"
    },
    {
      "$group": {
          "_id":"$_id", 
          "minTimeStamp": {
              "$min": "$startTimeArr"
          },
         "name": { "$first": "$name" },         
         "fee": { "$first": "$fee" },
         "duration": { "$first": "$duration" },
      }
    },
    {
        "$project": {
            "_id": 0,
            "name": 1,
            "minTimeStamp" : 1,
            "fee": 1,
            "duration": 1
        }
    },
    {
      "$sort": { "minTimeStamp": 1 }
    }
]);

Output:

 /* 0 */
{
    "result" : [ 
        {
            "minTimeStamp" : NumberLong(1425310000000),
            "name" : "Let's go to LA",
            "fee" : 500,
            "duration" : 2
        }, 
        {
            "minTimeStamp" : NumberLong(1425320000000),
            "name" : "Let's go to NewYork",
            "fee" : 800,
            "duration" : 3
        }, 
        {
            "minTimeStamp" : NumberLong(1425427200000),
            "name" : "Let's go to Washington",
            "fee" : 700,
            "duration" : 2
        }
    ],
    "ok" : 1
}

edited Apr 20, 2015 at 10:10

answered Apr 20, 2015 at 9:29

chridam

104k26 gold badges246 silver badges243 bronze badges

2 Comments

armnotstrong Over a year ago

Thanks chridam, the $unwind operand really give me a clue. And instead of just get the grouped field, is there any way that I can get the raw document as an output? Meanwhile I put just the word LA, NewYork, Washington there just for convince, no strugle to cut the output like that, sorry if any misleading.

armnotstrong Over a year ago

And it seemed that whatever the current time is, the result will always keep sorting with the minimum element of each startTimeArr, pastebin.com/DwZ8njAe and pastebin.com/B2yKzxGt

Collectives™ on Stack Overflow

mongodb sorted by conditional element in a array

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related