CODEWITHSUNDEEP
mongodb
Randeep
Randeep

Reputation: 99

MongoDB group by subdocument counts and year

Here is a sample of my document from collection called products:

{
    "_id" : "B000KIT6LQ",
    "brand" : "unknown",
    "category" : "Electronics",
    "price" : "11.99",
    "title" : "Scosche KA2067B 2005..."
    "reviews" : [
        {
            "date" : ISODate("1969-12-31T23:59:59Z"),
            "score" : 5,
            "user_id" : "AK7M5Y7E9O3L7",
            "sentiment" : 0.5,
            "text" : "Bought this so I ...",
            "user_gender" : "female",
            "voted_total" : 0,
            "voted_helpful" : 0,
            "user_name" : "Alex",
            "summary" : "It is what it is"
        },
        {
            "date" : ISODate("1969-12-31T23:59:59Z"),
            "score" : 5,
            "user_id" : "A26VRLMPEA8IDR",
            "sentiment" : 0.352,
            "text" : "Years ago I worked as an...",
            "user_gender" : "male",
            "voted_total" : 0,
            "voted_helpful" : 0,
            "user_name" : "Jack R. Smith",
            "summary" : "Great Kit"
        },
        {
            "date" : ISODate("1969-12-31T23:59:59Z"),
            "score" : 4,
            "user_id" : "A1TGBDVX3QXCRH",
            "sentiment" : 0.19318181818181818,
            "text" : "This insert works great in my ...",
            "user_gender" : "female",
            "voted_total" : 0,
            "voted_helpful" : 0,
            "user_name" : "J. Reed",
            "summary" : "Fits great in my 2006 Spectra5"
        }
    ]
    }

I have many documents with multiple categories. I am trying to create a mongo query which will result in all categories with the number of reviews (subdocument) per year. I have to group by categories and year, and get the count for number of reviews. This is the query that I have got so far:

db.products.aggregate([
    { $unwind : "$reviews" },
    { $group: {
        _id: {category: "$category", date: "$reviews.date.getFullYear()"},
        count: { $sum: 1 }}},
    {$sort:{"count": -1}}
])

For some reason the getFullYear() method is not working for me. If I group by $reviews.date I get the results. Any pointers on getting the query right is appreciated. Thanks

Upvotes: 1

Views: 361

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 311935

You can't use JavaScript functions like getFullYear() in your aggregate pipeline, you need to use the equivalent aggregation Date operator, which in this case is $year.

db.products.aggregate([
    { $unwind : "$reviews" },
    { $group: {
        _id: {category: "$category", date: {$year: "$reviews.date"}},
        count: { $sum: 1 }}},
    {$sort:{"count": -1}}
])

Upvotes: 1

Related Questions