How to add values by category?

Asked

Viewed 584 times

2

I have some data structured as follows:

"_id" : "",
"filmes" : [ 
    {
        "nome" : "Filme 1",
        "categoria" : "terror",
        "qtd" : 7
    }, 
    {
        "nome" : "Filme 2",
        "categoria" : "comedia",
        "qtd" : 7
    },
    {
        "nome" : "FIlme 3",
        "categoria" : "terror",
        "qtd" : 7
    },
    {
        "nome" : "Filme 4",
        "categoria" : "terror",
        "qtd" : 7
    },
    {
        "nome" : "Filme 5",
        "categoria" : "comedia",
        "qtd" : 7
    },
    {
        "nome" : "Filme 6",
        "categoria" : "romance",
        "qtd" : 7
    },
]

I am trying to develop a query that adds the values by category and brings me the data as follows (sum of "Qtd" by category):

"_id" : "",
"livros" : [ 
    {
        "categoria" : "terror",
        "qtd" : 21
    }, 
    {
        "categoria" : "comedia",
        "qtd" : 14
    },
    {
        "categoria" : "romance",
        "qtd" : 7
    },
]

I tried some stuff like:

db.filmes.aggregate(
  [
    {
      $project:
      {
        _id: "$_id",
        totalfilmes: { $sum: "$filmes.qtd" }
      }
    }
  ]
)

but I can’t aggregate by category. Anyone has any idea ?

mongodb

1 answer

1


For this type of operation it is necessary to group together with $unwind (deconstruct), which will deconstruct the array. I’ll explain.

db.teste.aggregate([ {$unwind: "$filmes"} ])

It will give the following result:

{ 
    "_id" : ObjectId("5a68d429f9d74b22f6ac43be"), 
    "filmes" : {
        "nome" : "Filme 1", 
        "categoria" : "terror", 
        "qtd" : 7.0
    }
}
{ 
    "_id" : ObjectId("5a68d429f9d74b22f6ac43be"), 
    "filmes" : {
        "nome" : "Filme 2", 
        "categoria" : "comedia", 
        "qtd" : 7.0
    }
}
...demais itens

And then group by category and not by id. The $unwind will allow you to access each item of the array, therefore of _id of the grouping, which is where we indicate the fields that will be included, being _id: "$filmes.categoria". That is, accessing the category of each film.

Upshot

db.filmes.aggregate(
  [
    {
      $unwind: "$filmes"
    },
    {
      $group:
      {
        _id: "$filmes.categoria",
        total: {$sum: "$filmes.qtd"}
      }

    }
  ]
)

Browser other questions tagged mongodb

You are not signed in. Login or sign up in order to post.