Get the all the document with only lasted subdocument in mongodb

Question

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

Answer 1

Query

• reduce starting from a minimun {"type": "y", "time": "1/1/0000"}
• if we find member with y and bigger date we update the time in $$value
• else keep old one
• when done we check if we found any bigger y value
• if we did we do [member_we_found] else []

*keeps only the max date if many have the same max date, keeps only 1
*its more nested but it doesn't use unwind/group/sort, i mostly send it as alternative document local way, do all with reduce.

Test code here

aggregate(
[{"$set": 
    {"list": 
      {"$reduce":  
        {"input": "$list",
          "initialValue": {"type": "y", "time": "1/1/0000"},
          "in": 
          {"$cond": 
            [{"$and": 
                [{"$eq": ["$$this.type", "y"]},
                  {"$gt": 
                    [{"$dateFromString": 
                      {"dateString": "$$this.time", "format": "%m/%d/%Y"}},
                     {"$dateFromString": 
                      {"dateString": "$$value.time", "format": "%m/%d/%Y"}}]}]},
              {"$setField": 
                {"field": "time", "input": "$$value", "value": "$$this.time"}},
              "$$value"]}}}}},
  {"$set": 
    {"list": 
      {"$cond": 
        [{"$eq": 
            [{"$getField": {"field": "time", "input": "$list"}}, "1/1/0000"]},
          [],
          ["$list"]]}}}])

Answer 2

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.

Answer 3

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.