MongoDB Aggregation Count of Group By Inner Array

Question

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
    }

It would be best if you could convert the date fields to proper date objects rather than strings for your aggregation pipeline to be effective — chridam
– chridam, Commented Feb 6, 2016 at 21:12
yes @chridam you are right. I am in the process of doing that now. I am taking a mongodump before I use a foreach statement to update all date strings to ISODates. — Moody
– Moody, Commented Feb 6, 2016 at 21:16

logan rakai · Accepted Answer · 2016-02-07 06:56:09Z

2

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 } }
])

answered Feb 7, 2016 at 6:56

logan rakai

2,5772 gold badges17 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

MongoDB Aggregation Count of Group By Inner Array

Update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related