MongoDB How to select the $max of embedded fields

Question

I am trying to get record details , but I want the most recent date in an embedded field
( something like max in SQL ).

In the data below, the most recent action event is "Date": "2017-10-18 15:36:00".

I would like to return from the collection ( collection name is subs ) :

_id , EmailAddress , CustomFields - and the most recent action record ( or null for no action records )

And I would ideally like to filter the list as well , something like where lastest action > 01 Jan 2014

{
  "EmailAddress": "joe@example.com",
  "Name": "",
  "Date": "2009-01-23 06:22:00",
  "State": "Active",
  "CustomFields": [
    {
      "Key": "[FirstName1]",
      "Value": "Joe"
    },
    {
      "Key": "[LastName1]",
      "Value": "Bloggs"
    }
  ],
  "ReadsEmailWith": "Gmail",
  "ConsentToTrack": "",
  "history": [
    {
      "ID": "d5e593987eab28595bafa1893fe3ddd6",
      "Type": "Campaign",
      "Name": "Retail 18th October 2017",
      "Actions": [
        {
          "Event": "Click",
          "Date": "2017-10-18 15:36:00",
          "IPAddress": "101.187.99.233",
          "Detail": "http://www.example.com"
        },
        {
          "Event": "Open",
          "Date": "2017-10-18 15:36:00",
          "IPAddress": "101.187.99.233",
          "Detail": ""
        }
      ]
    },
    {
      "ID": "ab46afa75b85f2eacdf8584c2287f844",
      "Type": "Campaign",
      "Name": "August 2010",
      "Actions": [
        {
          "Event": "Click",
          "Date": "2010-08-30 15:20:00",
          "IPAddress": "",
          "Detail": "http://www.example.com"
        },
        {
          "Event": "Open",
          "Date": "2010-08-30 15:20:00",
          "IPAddress": "",
          "Detail": ""
        },
        {
          "Event": "Open",
          "Date": "2010-08-30 14:28:00",
          "IPAddress": "",
          "Detail": ""
        }
      ]
    },
    {
      "ID": "1e414ee3554008ce93fa5087f7376923",
      "Type": "Campaign",
      "Name": "July 2010",
      "Actions": [
        {
          "Event": "Open",
          "Date": "2010-07-26 15:24:00",
          "IPAddress": "",
          "Detail": ""
        }
      ]
    }
  ]
}

Answer 1

You can use below aggregation in 3.4 to get the Action element with highest date.

db.col.aggregate({"$project":{
  "maxEle":{
    "$reduce":{
      "input":"$history.Actions",
      "initialValue":{"Date":""},
      "in":{
        "$cond":[
          {"$gt":[{"$max":"$$this.Date"},"$$value.Date"]},
          {"$arrayElemAt":["$$this",{"$indexOfArray":["$$this",{"$max":"$$this.Date"}]}]},
          "$$value"
        ]
      }
    }
  }
}})

Answer 2

You can try below aggregation:

db.subs.aggregate([
    {
        $project: {
            _id: 1,
            EmailAddress: 1,
            CustomFields: 1,
            MostRecentActionRecord: {
                $reduce: {
                    input: {
                        $reduce: {
                            input: {
                                $map: {
                                    input: "$history",
                                    as: "h",
                                    in: "$$h.Actions"
                                }
                            },
                            initialValue: [],
                            in: { $concatArrays: [ "$$value", "$$this" ] }
                        }
                    },
                    initialValue: null,
                    in: {
                        $cond: {
                            if: { $and: [ { $gt: [ "$$this.Date", "2014-01-01 00:00:00" ] }, { $gt: [ "$$this.Date", "$$value.Date" ] } ] },
                            then: "$$this",
                            else: "$$value"
                        }
                    }
                }
            }
        }
    }
])

Using $map and inner $reduce allows you to flatten your Actions into single array. Then you can use $reduce again with $cond to get most recent value, including your condition. Setting null as initial value will cause null to be returned when there's no match