CODEWITHSUNDEEP
mongodbaggregation-framework
Moody
Moody

Reputation: 51

MongoDB Aggregation Count of Group By Inner Array

I am using MongoDB 3.2.

Having trouble with this query ...

(Apologies in advance for the SQL-like syntax, I'm just learning Mongo coming from SQL)

I need to find the COUNT of documents where 

"audit_event_type_id" : 1
shop2.date" BETWEEN "2014-10-01" AND "2015-09-01"

Grouping it by 

shop2.facility_name
MONTH(shop2.date)
YEAR(shop2.date)

ORDER BY

count DESC

Is there a way to convert a string to date in the query? I know I am storing shop2.date as a string when it should be ISODate. If not, let's assume that it's an ISODate.

Here is a document sample :

   {
        "_id" : ObjectId("56b38f31967a337c432119cb"),
        "audit_event_id" : 12382306,
        "audit_event_type_id" : 2,
        "group_id" : 3333489,
        "applicant_id" : 3428508,
        "service_credit_id" : 3804844,
        "page_hit_id" : 43870954,
        "shop1" : {
            "facility_id" : 28,
            "facility_name" : "Fake1",
            "date" : "2014-08-13",
            "time" : "07:00",
            "expedite" : false,
            "collect_biometrics" : false,
            "block_id" : 364814,
            "appointment_category" : "Renewal"
        },
        "shop2" : {
            "facility_id" : 29,
            "facility_name" : "Fake2",
            "date" : "2014-08-07",
            "time" : "07:00",
            "expedite" : false,
            "block_id" : 373614,
            "appointment_category" : "Biometrics"
        },
        "created_at" : "2014-07-30 00:44:36",
        "updated_at" : "2014-07-30 00:44:36",
        "user_id" : 3242890,
        "payment_id" : null,
        "mission_id" : 24,
        "affected_user_id" : null
    }

Any help would be appreciated. Thanks!!

Update

I've updated all shop1.date & shop2.date to ISODate. Here is the new document sample :

   {
        "_id" : ObjectId("56b38f31967a337c432119cb"),
        "audit_event_id" : 12382306,
        "audit_event_type_id" : 2,
        "group_id" : 3333489,
        "applicant_id" : 3428508,
        "service_credit_id" : 3804844,
        "page_hit_id" : 43870954,
        "shop1" : {
            "facility_id" : 28,
            "facility_name" : "Fake1",
            "date" : ISODate("2014-08-13T00:00:00Z"),
            "time" : "07:00",
            "expedite" : false,
            "collect_biometrics" : false,
            "block_id" : 364814,
            "appointment_category" : "Renewal"
        },
        "shop2" : {
            "facility_id" : 29,
            "facility_name" : "Fake2",
            "date" : ISODate("2014-08-07T00:00:00Z"),
            "time" : "07:00",
            "expedite" : false,
            "block_id" : 373614,
            "appointment_category" : "Biometrics"
        },
        "created_at" : "2014-07-30 00:44:36",
        "updated_at" : "2014-07-30 00:44:36",
        "user_id" : 3242890,
        "payment_id" : null,
        "mission_id" : 24,
        "affected_user_id" : null
    }

Upvotes: 1

Views: 881

Answers (1)

logan rakai
logan rakai

Reputation: 2557

You can use the aggregation framework in mongodb for this: 

db.Collection.aggregate([
{ $match: { 
    audit_event_type_id: 1,
    "shop2.date": { $gte: ISODate("2014-10-01T00:00:00.000Z"), $lt: ISODate("2015-09-01T00:00:00.000Z") } } 
},
{ $group : {
        _id : { "shop2.facility_name": "$shop2.facility_name", month: { $month: "$shop2.date" }, year: { $year: "$shop2.date" } },
        count: { $sum: 1 } }
},
{ $sort : { count: -1 } }
])

Upvotes: 2

Related Questions