CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Mayank Shekhar
Mayank Shekhar

Reputation: 21

MongoDB aggregation $group queryquery

I have a collection in which documents are like

{
    "_id" : "api",
    "titleAndTimeDetails" : [ 
        {
            "Title" : "api data",
            "timeTaken" : NumberLong(8091446063)
        }, 
        {
            "Title" : "class data",
            "timeTaken" : NumberLong(519163710)
        }, 
        {
            "Title" : "API or datasets for age world",
            "timeTaken" : NumberLong(34245103)
        }, 
        {
            "Title" : "What does a null result mean API?",
            "timeTaken" : NumberLong(171605137)
        }
 ]
}

/* 2 */
{
    "_id" : "government",
    "titleAndTimeDetails" : [ 
        {
            "Title" : "Is there an open standard for the CAFR (Comprehensive Annual Finance Report)?",
            "timeTaken" : NumberLong(574587563)
        }, 
        {
            "Title" : "College Scorecard full data base",
            "timeTaken" : NumberLong(9422714)
        }, 
        {
            "Title" : "List of German local politicians",
            "timeTaken" : NumberLong(691311396)
        }, 
        {
            "Title" : "Trying to extrapolate patient costs by physician from public Medicare pricing data",
            "timeTaken" : NumberLong(9590779130)
        }, 
        {
            "Title" : "Are there good examples of open read-write APIs in Federal government?",
            "timeTaken" : NumberLong(1784634763)
        } 
   ]
}

The query statement is to display the Title for each "_id" which has minimum time taken. Please can someone help me with this?

Upvotes: 2

Views: 69

Answers (2)

Clement Amarnath
Clement Amarnath

Reputation: 5466

db.collection.aggregate([{$project: { timeTaken:{$min:"$titleAndTimeDetails.
timeTaken"}}}])

The result which we get for the sample documents given in the question, is as shown below

{ "_id" : "api", "timeTaken" : NumberLong(34245103) }
{ "_id" : "government", "timeTaken" : NumberLong(9422714) }

To get the desired result $project and $min of the aggregation pipeline are used

Upvotes: 1

barbakini
barbakini

Reputation: 3144

You should first $unwind titleAndTimeDetails then $sort by titleAndTimeDetails.timeTaken and finally $group by _id and get $first of Title.

db.collection.aggregate([
    { $unwind: "$titleAndTimeDetails" },
    { $sort: { "titleAndTimeDetails.timeTaken": 1 } },
    { $group: { _id: "$_id", title: { $first: "$titleAndTimeDetails.Title" } } }
])

Upvotes: 2

Related Questions