MongoDB How to select the $max of embedded fields

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": ""
        }
      ]
    }
  ]
}

2 answers

  • answered 2018-08-09 05:15 mickl

    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

  • answered 2018-08-09 09:49 Veeram

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