CODEWITHSUNDEEP
mongodb
Vinz
Vinz

Reputation: 131

MongoDB: aggregate results by year

I am trying to aggregate my results by year but I can't figure it out why it isn't working.

The data looks like ...

{ 
    "_id" : ObjectId("5c0a3f9f97ed291ab951cd7c"), 
    "ArtNummer" : NumberInt(1), 
    "Artikelgruppe" : "Brote", 
    "ArtBezeichnung" : "Schweizer Brot", 
    "Datum" : ISODate("2016-09-30T22:00:00.000+0000"), 
    "KundeName" : "Hardt", 
    "KundenNr" : NumberInt(1), 
    "GelieferteMenge" : 9.0, 
    "Retoure" : 0.5, 
    "VerkaufteMenge" : 3.5
}

and my query code:

use bbn;
db.getCollection("abverkauf").aggregate(
    [
        { 
            "$group" : {
                "_id" : {
                    "ArtNr" : "$ArtNr",
                    "year" : "$year"
                }
            }
        }, 
        { 
            "$project" : {
                "ArtNr" : "$_id.ArtNr",
                "year" : { "$year" : "$Datum"}
            }
        }
    ], 
    { 
        "allowDiskUse" : true
    }
);

and result:

{ 
    "_id" : {

    }, 
    "year" : null
}

how can I aggregate the result by year or month?

Upvotes: 0

Views: 51

Answers (1)

mickl
mickl

Reputation: 49945

You should change your _id cause currently you're referencing two non-existing fields: ArtNr and year while to extract a year from date you should use $year as operator, try:

db.getCollection("abverkauf").aggregate([
        { 
            "$group" : {
                "_id" : {
                    "ArtNr" : "$ArtNummer",
                    "year" : { "$year" : "$Datum"}
                }
            }
        }, 
        { 
            "$project" : {
                "ArtNr" : "$_id.ArtNr",
                "year" : "$_id.year" 
            }
        }
])

prints:

{ "_id" : { "ArtNr" : 1, "year" : 2016 }, "ArtNr" : 1, "year" : 2016 }

Upvotes: 1

Related Questions