MongoDB, counting the occurrences of EACH item in several arrays

Question

I'm fairly new to Mongo, sorry if this has some simple answer I've not grasped.

I want to be able to count the number of times each number(genre id) appears in all of the 'movie_genres' arrays and get back counts for each number. My end-goal is to tally how many times a certain genre number appears in the user's account (includes all movies).

I hope to get back that this user has 2 28's, 2 53's, 1 18

Movies can be added all the time to the user's 'movies_watched' array.

const userSeed = [
  {
    email: "rambo@hotmail.com",
    password: "12345",
    movies_watched: [
      {
        title: "Rambo",
        movie_id: 7555,
        movie_runtime: 99,
        movie_genres: [28, 53],
      },
      {
        title: "Rambo: Last Blood",
        movie_id: 522938,
        movie_runtime: 99,
        movie_genres: [28, 53, 18],
      },
    ],

Answer 1

you need to use aggregation and set (only unique values)

query:

db.getCollection('movies')
    .aggregate([
        // optionaly filter records to apply on
        //{
        //    $match: { email: /rambo/ }
        //},
        {
            $project: { // project values you want to see in result
                // record id is present by default
                email: "$email",
                genres: { // brand new field with desired output
                    $reduce: { // reduce like Array.reduce in javascript
                        input: "$movies_watched", // array to reduce
                        initialValue: [],
                        in: {
                            $setUnion: // the function doing the magic
                            // if you use $concatArrays instead - it will contain duplicities
                            [
                                "$$value", // destination in initialValue
                                "$$this.movie_genres" // field to take items from
                            ]
                        }
                    }
                }
            }
        }
    ])

result:

[
  {
    "_id": {"$oid": "60a7c3d23541f3714eb91332"},
    "email": "rambo@hotmail.com",
    "genres": [18, 28, 53]
  },
  {
    "_id": {"$oid": "60a7c4373541f3714eb9135f"},
    "email": "dexter@hotmail.com",
    "genres": [13, 18, 28, 53]
  }
]

dataset:

[
    {
        "_id" : ObjectId("60a7c3d23541f3714eb91332"),
        "email" : "rambo@hotmail.com",
        "password" : "12345",
        "movies_watched" : [ 
            {
                "title" : "Rambo",
                "movie_id" : 7555,
                "movie_runtime" : 99,
                "movie_genres" : [ 
                    28, 
                    53
                ]
            }, 
            {
                "title" : "Rambo: Last Blood",
                "movie_id" : 522938,
                "movie_runtime" : 99,
                "movie_genres" : [ 
                    28, 
                    53, 
                    18
                ]
            }
        ]
    },
    {
        "_id" : ObjectId("60a7c4373541f3714eb9135f"),
        "email" : "dexter@hotmail.com",
        "password" : "123456",
        "movies_watched" : [ 
            {
                "title" : "Dexter",
                "movie_id" : 444,
                "movie_runtime" : 99,
                "movie_genres" : [ 
                    13, 
                    18
                ]
            }, 
            {
                "title" : "Rambo: Last Blood",
                "movie_id" : 522938,
                "movie_runtime" : 99,
                "movie_genres" : [ 
                    28, 
                    53, 
                    18
                ]
            }
        ]
    }
]