Complex aggregate command MongoDB

Question

I have tons of documents in a Mongo database with the following structure:

{
    "_id" : {
        "birthDate" : "1978-08-09",
        "name" : "Peter"
},
    "value" : {
        "types" : {
            "euro" : 90,
            "unknown" : 2,
            "dollar" : 3
        }
    }
}

Not all documents contain all types (i.e.: some of them only have euro or don't have the unknown field).

I want to count the number of occurrences of each type for a specific name with the aggregate framework.

I have this:

db.collection.aggregate({$match: {_id : {name:"John"}}}, {$group: {_id: '', euro: {$sum: '$value.types.euro'}, dollar: {$sum: '$value.types.dollar'}, unknown: {$sum: '$value.types.unknown'}}})

But it is returning:

{ "result" : [ ], "ok" : 1 }

My question is: How can I count the type of each coin for a specific name with the Mongo's aggregate framework? Would it be also possible to get a list for every name in the form:

"result" : [
    {
        "name" : "Peter",
        "dollar" : 1,
        "euro" : 12,
        "unknown" : 4

    }
]

"result" : [
    {
        "name" : "John",
        "dollar" : 4,
        "euro" : 10,
        "unknown" : 3

    }
]

I am using MongoDB with the Java driver, so if the answer is in Java code it would be perfect.

Parvin Gasimzade · Accepted Answer · 2013-12-18 12:31:41Z

3

You can use the following query to group by name & count coins :

db.collection.aggregate(
  {$group : {_id : "$_id.name", 
             dollar : {$sum : "$value.types.dollar"}, 
             euro : {$sum : "$value.types.euro"}, 
             unknown : {$sum : "$value.types.unknown"}}}
)

Also if you want to find the number of coins for specific person you can use following query :

db.collection.aggregate(
  {$match : {"_id.name" : "John"}},
  {$group : {_id : "$_id.name", 
             dollar : {$sum : "$value.types.dollar"}, 
             euro : {$sum : "$value.types.euro"}, 
             unknown : {$sum : "$value.types.unknown"}}}
)

answered Dec 18, 2013 at 12:31

Parvin Gasimzade

26.2k8 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3083022 Over a year ago

That's what I was looking for. Now I'm wondering: would it be possible to only take the results which birthdate is between two dates? i.e. the exact same results from the first request but only those between, for example, 1980-07-05 and 1985-08-05

Parvin Gasimzade Over a year ago

you can update $match query as {$match : {"_id.name" : "John", "_id.birthDate" : {$gte : "1980-07-05", $lte : "1985-08-05"}}}

user3083022 Over a year ago

nice, now the problem is that the date is actually stored like this: ISODate("1980-08-05T22:00:00Z"). Those $gte don't seem to really work, I get {"result" : [ ], "ok" : 1 } when I update $match with {$match : {"_id.name" : "John", "_id.birthDate" : {$gte : 'ISODate("1980-07-5")', $lte : 'ISODate("1985-08-05")'}}}

Anthonny · Accepted Answer · 2013-12-18 13:21:58Z

1

To count the type of each coin for a specific name, you can do that :

db.collection.aggregate([
{
    $match: {'_id.name' : "John"}
}, 
{
    $project: {
        _id: 1,
        euro: {$cond: [ { $eq: [ '$value.types.euro', undefined ]} , 0, 1 ]},
        dollar: {$cond: [  { $eq: [ '$value.types.dollar', undefined ]} , 0, 1 ]},
        unknown: {$cond: [ { $eq: [ '$value.types.unknown', undefined ]}, 0, 1 ]}
    }  
},
{
    $group: {_id: "$_id.name", euro: {$sum: '$euro'}, dollar: {$sum: '$dollar'}, unknown: {$sum: '$unknown'}}
}
])

edited Dec 18, 2013 at 13:21

answered Dec 18, 2013 at 12:47

Anthonny

1,5911 gold badge9 silver badges9 bronze badges

3 Comments

user3083022 Over a year ago

Should this be equivalent to the second query that @parvin wrote? The results are very different

Anthonny Over a year ago

No it's not equivalent to the second query that @parvin wrote. My query count how many times each type (euro, dollar and unknow) are present in all the document with _id.name = 'John'. It's not a $sum of the value of the type. I hope it's clear, my english is not very good :)

user3083022 Over a year ago

Ok yes I get it! I was looking more for @parvin's one then. Thank you though

Collectives™ on Stack Overflow

Complex aggregate command MongoDB

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related