Get the all the document with only lasted subdocument in mongodb-CodePudding

I have some record like that, which many record in sub document list

{
  id:1
  name:a
  list:[
   {type:x,time:1/1/2021},
   {type:y,time:1/1/2022}
  ]
},
{
  id:2
  name:b
  list:[
    {type:x,time:1/1/2021},
    {type:y,time:1/1/2022},
    {type:y,time:1/1/2023}
  ]
}

so I need get the record which have a list newest time and type y

{
    id:1
    name:a
    list:[{type:y,time:1/1/2022}]
},
{
    id:2
    name:b
    list:[{type:y,time:1/1/2023}]
}

So I just use mongodb recently and I dont have any idea for this case

CodePudding user response：

Here is an alternate solution that uses $map and $filter to reduce the amount of docs to $unwind. It also converts the string date to a real sortable ISODate.

db.foo.aggregate([
    {$addFields: {

        // filter() for only type y, then pass that list to map() and                                    
        // convert the time to ISODate.  i.e. X = map(filter($list))                                     
        X: {$map: {
            input: {$filter: {
                input: "$list",
                as: "zz",
                cond: {$eq:[ '$$zz.type','y']}
            }},
            as:"qq",
            in: {
                "type":"$$qq.type", // carry fwd "type" for convenience                                  
                "time":{$dateFromString: {dateString: "$$qq.time",format:"%m/%d/%Y"}}
            }
        }}
    }}

    ,{$unwind: "$X"}
    ,{$sort: {"X.time":-1}}
    ,{$group: {_id: "$id",
               name: {$first: "$name"},
               type: {$first: "$X.type"},
               time: {$first: "$X.time"}
              }}



]);

to yield something like this:

{
    "_id" : 1,
    "name" : "a",
    "type" : "y",
    "time" : ISODate("2022-01-01T00:00:00Z")
}
{
    "_id" : 2,
    "name" : "b",
    "type" : "y",
    "time" : ISODate("2023-01-01T00:00:00Z")
}

I did not put type and time back into a subdoc in an array named list because there can be only one newest item and it avoids an extra $addFields stage to "wrap" it in an array.

CodePudding user response：

You can $unwind the list, $sort by the time field, $group again the document to get the latest document.

db.collection.aggregate([
  {
    "$unwind": "$list"
  },
  {
    $match: {
      "list.type": "y"
    }
  },
  {
    $sort: {
      "list.time": -1
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        "$first": "$name"
      },
      list: {
        $first: "$list"
      }
    }
  },
  {
    "$addFields": {
      "list": [
        "$list"
      ]
    }
  }
])

Here is the Mongo playground for your reference.