How to add values by category?

Question

How to add values by category?

Navigation

#1 by (1 votes)

2

I have some data structured like this:

"_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'm trying to develop a query that adds up 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 things like:

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

but I can not add by category. Anyone have any ideas?

mongodb

asked by anonymous 23.01.2018 / 12:54

1 answer

How to get all values of 'YamlDotNet' in 'File.yml'? Slides in HTML5 / CSS3 - Background does not change

score 1 · Accepted Answer

For this type of operation it is necessary to group together with $unwind (detrain), 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 access to each item in the array , hence the _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 movie.

Result

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

    }
  ]
)