Aggregate on MongoDB based on arrays

Question

I'm trying to do an array based on the next structure:

{
    _id:573a1390f
    plot:"A greedy tycoon decides, on a whim, to corner the world market in whea…",
    genres:"Short","Drama",
    countries:"México","USA"
},
{
    _id:573a1390f
    plot:"A woman, with the aid of her police officer sweetheart, endeavors to u…",
    genres:"Drama",
    countries:"México","USA","Canada"
}

What Im trying to do is to provide an output like this:

Country|Genre|Total Rows 
México |Short|1  
México |Drama|2 
USA    |Short|1 and so.

I have come up with the following:

{
  $unwind:
  {
    path: "$Genres"
  }
}
{
  $unwind:
  {
    path: "$Countries"
  }
}
{
  $Addfields:{CuentaxGenero:{sum:1}}
}
{
  $group:
  {
    _id: {pais:"$countries",genero:"$genres"},
    Total:{$sum:1}
   }
}

But the output at the end the field _id still looks like an object and can´t place it like a field. Is there any way to unwind the aggregation?

I'm just learning, hope you can help me.

Answer 1

1. You need the $project stage to decorate the output document, by flattening the _id object to multiple fields for country, and genre.

2. The $addFields stage can be removed as the added field doesn't seem to be needed to further stages.

db.collection.aggregate([
  {
    $unwind: {
      path: "$genres"
    }
  },
  {
    $unwind: {
      path: "$countries"
    }
  },
  {
    $group: {
      _id: {
        pais: "$countries",
        genero: "$genres"
      },
      Total: {
        $sum: 1
      }
    }
  },
  {
    $project: {
      _id: 0,
      pais: "$_id.pais",
      genero: "$_id.genero",
      Total: 1
    }
  }
])

Demo @ Mongo Playground