In MongoDB, I want to get the value of a field that occurs the least amount of times.

Example 1: If I have a field called version, 20 documents/entries use version 2, 30 use version 3 and 5 use version 1.

I want to get what version is the least used. So, with example 1, it should return version 1.

Example 2: 30 documents/entries use version 1.0.5, 10 use 1.0.2, 7 use 1.0.8 and 11 use 1.0.1

In this example, it should return 1.0.8 since that is in the document 7 times.

How can I do something like this?

I hope this makes sense.

CodePudding user response：

Simple $group->$sort->$limit can do the task:

db.collection.aggregate([
{
 $group: {
  _id: "$version",
  occurance: {
    $sum: 1
  }
 }
},
 {
  $sort: {
    occurance: 1
   }
 },
 {
  $limit: 1
 }
])

Explained:

1. Group by version value to get the occurance for all documents.
2. Sort in ASCENDING order.
3. Limit the result to 1st document ( this version will be least used )

Playground

CodePudding user response：

To find the value of a field that occurs the least amount of times in a MongoDB database, you can use the aggregate() method to create an aggregation pipeline. The pipeline should include the following steps:

Use the $group stage to group the documents by the value of the field you want to find the least common value for. For example, if you want to find the least common version, you would use a $group stage like this:

{
  $group: {
    _id: "$version",
    count: { $sum: 1 }
  }
}

This $group stage groups the documents by the value of the version field, and it uses the $sum operator to count the number of documents in each group. The result of this stage is a set of documents that contains the value of the field and the number of times it occurs in the collection.

Use the $sort stage to sort the documents by the count in ascending order. This will put the documents with the lowest count at the top of the results. For example, the $sort stage could look like this:

{ $sort: { count: 1 } }

This $sort stage sorts the documents by the count field in ascending order.

Use the $limit stage to limit the results to the first document. This will select the document with the lowest count, which is the least common value for the field. For example, the $limit stage could look like this:

{ $limit: 1 }

This $limit stage limits the results to the first document.

Use the $project stage to select only the field you want to return. For example, if you want to return the value of the version field, you could use a $project stage like this:

{ $project: { version: 1 } }

This $project stage selects only the version field and omits all other fields.

Here's an example of how you could use these stages to create an aggregation pipeline that finds the least common value for the version field:

db.collection.aggregate([
  {
    $group: {
      _id: "$version",
      count: { $sum: 1 }
    }
  },
  { $sort: { count: 1 } },
  { $limit: 1 },
  { $project: { version: 1 } }
])