Using match on array field before unwind

Question

I have the following document structure:

{
    "_id" : ObjectId("5a16b7cf930a1e000465d1c5"),
    "trackerId" : ObjectId("5a16b7b8930a1e000465d1c1"),
    "trackingEvents" : [ 
        {
            "type" : "checkin",
            "timestamp" : ISODate("2017-11-23T11:57:43.710Z"),
        }, 
        {
            "type" : "connectivity",
            "timestamp" : ISODate("2017-11-23T11:57:47.011Z"),
        }, 
        {
            "type" : "power",
            "timestamp" : ISODate("2017-11-23T11:57:47.036Z"),
        }
    ]
}

I would like to setup a query to count number of trackingEvents with "type":"power" grouped by day for all trackingEvents happened < 1 month ago. And have the following query which works fine, except for the fact that it is not fast enough:

db.getCollection('trackerEvents').aggregate(    [
        {$unwind: "$trackingEvents"},
        {$match:
            {
            "trackingEvents.type": "power",
            "trackingEvents.timestamp": {
                    "$gt": {
                        "$humanTime": "1 month ago" //redash operator
                    }
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$trackingEvents.timestamp"
                    }
                },
                count: {$sum: 1}
            }
        },
        ,
        { $sort : { "_id":1 } }
    ])

However, this query did not pass the code review as my colleague suggested to swap $match and $unwind operators so that $match goes before $unwind in order to increase perfomance of the query. If I swap these two operators, I get different results, could someone please suggest how is it possible to $match array elements of the document before $unwind?

Thanks!

You can e.g. have the match before AND after the unwind. The first stages can then use indexes. Depends on your data, of course. — dnickless
– dnickless, Commented Jan 15, 2018 at 9:56
You can also use the $filter operator for arrays. You could already filter the results out before you unwind — Alex P.
– Alex P., Commented Jan 15, 2018 at 10:02

chridam · Accepted Answer · 2018-01-15 14:20:03Z

2

You could use the $match operator as a way to filter early the documents then $filter at array level before $unwind:

var oneMonthAgo = new Date();
oneMonthAgo.setMonth(oneMonthAgo.getMonth()-1);
// var oneMonthAgo = moment().subtract(1, "months").unix();

db.getCollection('trackerEvents').aggregate([
    { 
        "$match": { 
            "trackingEvents.type": "power",
            "trackingEvents.timestamp": { "$gt": oneMonthAgo }
        } 
    },
    {
        "$project": {
            "trackingEvents": {
                "$filter": {
                    "input": "$trackingEvents",
                    "as": "event",
                    "cond": {
                        "$and": [
                            { "$eq": ["$$event.type", "power"] },
                            { "$gt": ["$$event.timestamp", oneMonthAgo] }
                        ]
                    }
                }               
            }
        }
    },
    { "$unwind": "$trackingEvents" },
    {
        "$group": {
            "_id": {
                "$dateToString": {
                    "format": "%Y-%m-%d",
                    "date": "$trackingEvents.timestamp"
                }
            },
            "count": { "$sum": 1}
        }
    },
    { "$sort": { "_id": 1 } }
]);

edited Jan 15, 2018 at 14:20

answered Jan 15, 2018 at 10:05

chridam

104k26 gold badges246 silver badges243 bronze badges

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

2 Comments

Andrey Yaskulsky Over a year ago

Thanks for the answer, can you please give a hint - how can I group by day these results? Should I remove $size and add $group operator to the query?

chridam Over a year ago

@AndreyYaskulsky Had completely misunderstood the question earlier, my bad. The update should have better performance as it has early document and field filtering pipelines before the $unwind.

Collectives™ on Stack Overflow

Using match on array field before unwind

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related