Retrieve sub-documents that match with maximum val

问题:

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.

回答1:

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 } } }
])

回答2:

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()