Get frequency for multiple elements in all documents inside a collection mongodb

user_14991753 0
Viewed 109

So heres my problem.

I am new to mongodb and have a collection which documents are saved like this:

{
 "_id": {
    "$oid": "60626db173b4ca321c02ee3e"
 },
 "year": "2021",
 "name": "Book 1",
 "authors": ["Joe, B", "Jessica, K"],
 "createdAt": {
     "$date": "2021-03-30T00:15:45.859Z"
 }
},
{
 "_id": {
    "$oid": "60626db173b4ca321c02ee4e"
 },
 "year": "2021",
 "authors": ["Carl, B", "Jessica, K"],
 "name": "Book 2"
 "createdAt": {
     "$date": "2021-03-30T00:15:45.859Z"
 }
},

I need to get both the frequency of all authors and the years of the books.

The expected result would be something like this (as long as i can get each element frequency it doesn't really matter how the results are returned):

{
  "authors": {
     "Joe, B": 1,
     "Carl, B": 1,
     "Jessica, K": 2
  },
  "year": {
     "2021": 2
  }
}

I've seen this thread How to count occurence of each value in array? which does the job in one array but i have no idea if its possible to adapt to get the frequency of multiple elements (year, authors) at the same time or how to do it.

I appreciate any help. Thank you.

aggregation-framework mongodb-query mongodb aggregate database
1 Answers

Demo - https://mongoplayground.net/p/95JtQEThxvV

$group by year $push authors into the array get $sum count of the year occurrence, $unwind into individuals documents.

$group by authors and get $sum count of the author occurrence

$group by null to combine all documents, use $addToSet to push unique values and convert $arrayToObject to get final output in $project

$first

db.collection.aggregate([
  {
    $group: {
      _id: { year: "$year" },
      authors: { $push: "$authors" },
      yearCount: { $sum: 1 }
    }
  },
  { $unwind: "$authors" },
  { $unwind: "$authors"},
  {
    $group: {
      _id: { author: "$authors" },
      year: { $first: "$_id.year" },
      yearCount: { $first: "$yearCount" },
      authors: { $push: "$authors" },
      authorCount: { $sum: 1 }
    }
  },
  {
    "$group": {
      _id: null,
      years: {
        $addToSet: { k: "$year", v: "$yearCount" }
      },
      authors: {
        $addToSet: { k: "$_id.author", v: "$authorCount" }
      }
    }
  },
  {
    $project: {
      _id: 0,
      years: { $arrayToObject: "$years" },
      authors: { $arrayToObject: "$authors" }
    }
  }
])

Demo 2 - For author count grouped by year- https://mongoplayground.net/p/_elnjmknroF

user_22242650
Related