mongoDB - Get newest date from document in array

Question

I want to retrieve the array object with the newest dates for a particular document. But I sadly can't solve it, I always end up with errors.

Dateformat 2020-06-10T13:25:25.645+00:00 datetime.now()

Sample data

collection.insert_one(
    {
        "document_name": "My Document",
        "status": [
             {
                "status_time": datetimeobject,  # 2020-01-02T13:25:25.645+00:00
                "status_title": "Sample Title 1"
             },
             {
                "status_time": datetimeobject,  # 2020-06-10T13:25:25.645+00:00
                "status_title": "Sample Title"
             }
         ]
    })

What I've tried

result = collection.find_one({"document_name": "My Document"}, {"status": 1}).sort({"status.status_time": -1}).limit(1)

result = collection.find_one({"document_name": "My Document"}, {"$max": {"status.status_time": -1})

result = collection_projects.find_one({"document_name": "Document"}, {"status": {"$elemMatch": {"$max": "$´status_time"}}})

result = list(collection.find({"document_name": "Document"}, {"_id": 0, "status": 1}).limit(1))

result = collection_projects.find_one(
    {"document_name": "My Document"},
    {"status.status_time": {"$arrayElemAt": -1}})

Result I'm looking for

{
    "status_time": datetimeobject, # 2020-06-10T13:25:25.645+00:00
    "status_title": "Sample Title 2"  
}

@AyushGupta I guess it's a string but it's recognized as a date (I'm not sure). I updated and wrote the entry in my question. — BenjaminK
– BenjaminK, Commented Jun 10, 2020 at 12:24
@AyushGupta How would your example look like? I thought $unwind is to use when looking through multiple documents. But I'm only searing in one. — BenjaminK
– BenjaminK, Commented Jun 10, 2020 at 12:55

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-06-10 16:11:02Z

You need to use aggregation to achieve this :

Query 1 :

db.collection.aggregate([
    /** Re-create `status` field with what is needed */
    {
      $addFields: {
        status: {
          $reduce: {
            input: "$status", // Iterate on array
            initialValue: { initialDate: ISODate("1970-06-09T17:56:34.350Z"), doc: {} }, // Create initial values
            in: { // If condition is met push current value to accumulator or return acummulator as is
              initialValue: { $cond: [ { $gt: [ "$$this.status_time", "$$value.initialDate" ] }, "$$this.status_time", "$$value.initialDate" ] },
              doc: { $cond: [ { $gt: [ "$$this.status_time", "$$value.initialDate" ] }, "$$this", "$$value" ] }
            }
          }
        }
      }
    },
    /** 
     * re-create `status` field from `$status.doc`
     * Since it will always be having only on object you can make `status` as an object ratherthan an array
     * Just in case if `status` need to be an array you need do { status: [ "$status.doc" ] }  
     */
    {
      $addFields: { status: "$status.doc" }
    }
  ])

Test : mongoplayground

Ref : $reduce , pymongo

Query 2 :

db.collection.aggregate([
    /** unwind on `status` array  */
    {
      $unwind: {
        path: "$status",
        preserveNullAndEmptyArrays: true // preserves doc where `status` field is `[]` or null or missing (Optional)
      }
    },
    /** sort on descending order */
    {
      $sort: { "status.status_time": -1 }
    },
    /** group on `_id` & pick first found doc */
    {
      $group: { _id: "$_id", doc: { $first: "$$ROOT" } }
    },
    /** make `doc` field as new root */
    {
      $replaceRoot: { newRoot: "$doc" }
    }
  ])

Test : mongoplayground

Test both queries, I believe on a huge dataset $unwind & $sort might be a bit slow, similar to iteration on a huge array.

thammada.ts · Accepted Answer · 2020-06-10 18:45:44Z

You will have to use aggregate with $reduce, this solution is similar to @whoami's except there is no nested document when using $reduce

db.collection.aggregate([
  { 
    $match: {
      document_name: "My Document"
    }
  },
  {
    $project: { // use $project if you only want the status, use $addFields if you want other fields as well
      status: {
        $reduce: {
          input: "$status",
          initialValue: null,
          in: {
            $cond: [
              {
                $gte: [
                  "$$this.status_time",
                  "$$value.status_time"
                ]
              },
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  }
])

mongoplayground

Collectives™ on Stack Overflow

mongoDB - Get newest date from document in array

Sample data

What I've tried

Result I'm looking for

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample data

What I've tried

Result I'm looking for

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related