MongoDB: How to find Documents that contain information about only latest changes?

user_471011 0
Viewed 68

In the application, we already have a mechanism of logging changes that happen in our entities. For example, we have Category and Product entities,

where Category looks like:

Category {
name;
description
}

and Product looks like:

Product {
name;
price; 
quantity
}

Currently, we log information when any of the fields of our entities were modified:

Assume, that for Category entity the name property was changed twice; the description property was changed 3 times;

and for a Product entity the only price property was changed twice.

then in our LogCollection we will have the following Documents:

[
  {
    "_id": 1,
    "entity": "Category",
    "property": "name",
    "changed": "2020-08-30"
  },
  {
    "_id": 2,
    "entity": "Category",
    "property": "name",
    "changed": "2020-08-31"
  },
  {
    "_id": 3,
    "entity": "Category",
    "property": "description",
    "changed": "2020-08-29"
  },
  {
    "_id": 4,
    "entity": "Category",
    "property": "description",
    "changed": "2020-08-30"
  },
  {
    "_id": 5,
    "entity": "Category",
    "property": "description",
    "changed": "2020-08-31"
  },
  {
    "_id": 6,
    "entity": "Product",
    "property": "price",
    "changed": "2020-08-20"
  },
  {
    "_id": 7,
    "entity": "Product",
    "property": "price",
    "changed": "2020-08-31"
  }
]

The problem that I am trying to solve now:

I want to extract only the latest changes per each field for each unique entity.

I mean, that as result of some magic query I will get the following list of Documents:

[
  {
    "_id": 2,
    "entity": "Category",
    "property": "name",
    "changed": "2020-08-31"
  },
  {
    "_id": 5,
    "entity": "Category",
    "property": "description",
    "changed": "2020-08-31"
  },
  
  {
    "_id": 7,
    "entity": "Product",
    "property": "price",
    "changed": "2020-08-31"
  }
]

Those 3 Documents show me when exactly the latest changes for entity fields were made per each unique entity.

Could you please support me with a query to get those 3 Documents?

For sure, I have already reviewed Mongo DB documentation and found that potentially I have to use aggregation framework with $group and $max operators, but still I am stuck with writing real working query

aggregation-framework mongodb-query mongodb
2 Answers

This could be done with a $group stage.

First $match to limit the number of documents considered, unless you actually want to examine every log document since the beginning of time. Sort by the changed field in descending order, so the newest documents come first. Group by entity and property, keeping only the first document encountered. Use $replaceRoot or $project to restructure the document the way you need.

Note that the sample data is unable to determine which update came first if 2 happened on the same day.

db.collection.aggregate([
  {$match: {}}, //if desired
  {$sort: {changed: -1}}, 
  {$group: {
      _id: {
        property: "$property",
        entity: "$entity"
      },
      document: {$first: "$$ROOT"}
  }},
  {$replaceRoot: {newRoot: "$document"}}
])

Playground

user_22826340 
//source data, sort by latest date changed as the first document in the output/collection
> db.logCollection.find().sort({Category:1,property:1,changed:-1});
{ "_id" : 5, "entity" : "Category", "property" : "description", "changed" : "2020-08-31" }
{ "_id" : 4, "entity" : "Category", "property" : "description", "changed" : "2020-08-30" }
{ "_id" : 3, "entity" : "Category", "property" : "description", "changed" : "2020-08-29" }
{ "_id" : 2, "entity" : "Category", "property" : "name", "changed" : "2020-08-31" }
{ "_id" : 1, "entity" : "Category", "property" : "name", "changed" : "2020-08-30" }
{ "_id" : 7, "entity" : "Product", "property" : "price", "changed" : "2020-08-31" }
{ "_id" : 6, "entity" : "Product", "property" : "price", "changed" : "2020-08-20" }
//use sort to reflect latest document first and use $first as shown below
> db.logCollection.aggregate([
... {$sort:{Category:1,property:1,changed:-1}},
... {$group:{_id:{entity: "$entity",
...               property: "$property"},
...   latestChange: {$first:"$changed"}
... }
...   },
... ]);
{ "_id" : { "entity" : "Category", "property" : "name" }, "latestChange" : "2020-08-31" }
{ "_id" : { "entity" : "Product", "property" : "price" }, "latestChange" : "2020-08-31" }
{ "_id" : { "entity" : "Category", "property" : "description" }, "latestChange" : "2020-08-31" }
>
user_141485600
Related