CODEWITHSUNDEEP
node.jsmongodbmongodb-queryaggregation-framework
Mustafa Mamun
Mustafa Mamun

Reputation: 2661

Retrieve sub-documents that match with maximum value in the array

I have the following data structure in mongodb

[
    {
        "id" : "unique id 1",
        "timeStamp" : "timeStamp",
        "topicInfo" : [
            { 
                topic : "topic1", 
                offset : "offset number",
                time: 1464875267637
            },
            { 
                topic : "topic2", 
                offset : "offset number",
                time: 1464875269709
            },
            { 
                topic : "topic3", 
                offset : "offset number",
                time : 1464875270849
            }
       ]
   },
   {
       "id" : "unique id 2",
       "timeStamp" : "timeStamp",
       "topicInfo" : [
           { 
               topic : "15", 
               offset : "offset number",
               time : 1464875271884
           },
           { 
               topic : "topic2", 
               offset : "offset number",
               time : 1464875273887
           },
           { 
               topic : "topic3", 
               offset : "offset number",
               time : 1464875272848
           }
       ]
   }
 ]

Now I want to find all the entry That has topic called "topic2" and the value of time is maximum compare to other object's in the "topicInfo" array. I also want to sort them by "timeStamp". From the example code the query should return the second object. I am not able to write the query any help would be much appreciated.

Upvotes: 2

Views: 211

Answers (2)

Sede
Sede

Reputation: 61243

The optimal best way to do this is in MongoDB 3.2 or newer. We need to $project our documents and use the $filter operator to return a subset of the "topicInfo" array that matches our condition. And as of MongoDB3.2 , we can use the $max in the $project stage in the condition expression and perform a logical operation on the returned value.

The final stage in the pipeline is the $match stage where you filter out those documents with empty "topicInfo" using the $exists element query operator and the dot notation to access the first element in the array. This also reduces both the amount of data sent over the wire and the time and memory used to decode documents on the client-side.

db.collection.aggregate([
    { "$project": { 
        "topicInfo": { 
            "$filter": { 
                "input": "$topicInfo", 
                "as": "t", 
                "cond": { 
                    "$and": [ 
                        { "$eq": [ "$$t.topic", "topic2"] }, 
                        { "$eq": [ "$$t.time", { "$max": "$topicInfo.time" } ] }
                    ] 
                } 
            } 
        } 
    }},
    { "$match": { "topicInfo.0": { "$exists": true } } }
])

Upvotes: 2

Tomzan
Tomzan

Reputation: 2818

You can do it with aggregation framework like this: 

db.test.aggregate(
    { $unwind: '$topicInfo' }, 
    { $match: { 'topicInfo.topic': 'topic2' } }, 
    { $group: { 
        _id: '$id', 
        timestamp: { $first: '$timestamp' }, 
        time: { $max: '$topicInfo.time' } } 
     }, 
     { $sort: { timestamp: 1 } }).pretty()

Upvotes: 1

Related Questions