MongoDB Aggregator - grouping unique items that occur within 30 seconds of each other

Viewed 43

Problem I'm trying to solve: I have a set of documents in MongoDB that represent moments when a user enters and exits a page. My goal is to group these into "sessions".

Define Session: A session is any unique chunks of documents that occur within 30 seconds of each other. They are unique if they have the same uid, documentId, and clientType.

The goal is to turn this:

[
    {
        "_id": "1",
        "interactionType": "pageEnter",
        "uid": "u0",
        "documentId": "d0",
        "clientType": "web",
        "routePath": "/d0",
        "occurredAt": "2020-06-12T17:00:22.000Z"
    },
    {
        "_id": "2",
        "interactionType": "pageExit",
        "uid": "u0",
        "documentId": "d0",
        "clientType": "web",
        "routePath": "/d0",
        "occurredAt": "2020-06-12T17:00:32.000Z"
    },
    {
        "_id": "3",
        "interactionType": "pageEnter",
        "uid": "u0",
        "documentId": "d0",
        "clientType": "web",
        "routePath": "/d0/a",
        "occurredAt": "2020-06-12T17:00:42.000Z"
    },
    {
        "_id": "4",
        "interactionType": "pageExit",
        "uid": "u0",
        "documentId": "d0",
        "clientType": "web",
        "routePath": "/d0/a",
        "occurredAt": "2020-06-12T17:00:52.000Z"
    },
    {
        "_id": "5",
        "interactionType": "pageEnter",
        "uid": "u0",
        "documentId": "d0",
        "clientType": "web",
        "routePath": "/d0",
        "occurredAt": "2020-06-12T17:03:42.000Z"
    },
    {
        "_id": "6",
        "interactionType": "pageExit",
        "uid": "u0",
        "documentId": "d0",
        "clientType": "web",
        "routePath": "/d0",
        "occurredAt": "2020-06-12T17:03:52.000Z"
    }
]

Into this:

[
    {
        "_id": "d0-u0-web-2020-06-12T17:00:42.000Z",
        "uid": "u0",
        "documentId": "d0",
        "lastViewedAt": "2020-06-12T17:00:42.000Z",
        "totalDurationMilli": 20000,
        "history": [
            {
                "routePath": "/d0",
                "clientType": "web",
                "totalDurationMilli": 10000
            },
            {
                "routePath": "/d0/a",
                "clientType": "web",
                "totalDurationMilli": 10000
            }
        ]
    },
    {
        "_id": "d0-u0-web-2020-06-12T17:03:42.000Z",
        "uid": "u0",
        "documentId": "d0",
        "lastViewedAt": "2020-06-12T17:03:42.000Z",
        "totalDurationMilli": 10000,
        "history": [
            {
                "routePath": "/d0",
                "clientType": "web",
                "totalDurationMilli": 10000
            },
        ]
    },
]

Notice that both "session" documents have the same documentId, but different groups of history they are based from. This is because, as noted earlier, I want to separate the data in such a way that each session is at least 30 seconds apart.

So far my aggregator looks like this:

[
    // Filter by pageEnter and pageExit
    { $match: { interactionType: { $in: ['pageEnter', 'pageExit'] } } },

    // Sort by occurredAt
    { $sort: { occurredAt: 1 } },

    // Group by special id and and compose history.
    {
        $group: {
            _id: {
                uid: '$uid',
                documentId: '$documentId',
                clientType: '$clientType',
            },
            history: { $push: '$$ROOT' },
        },
    },

    // Project fields for final document.
    {
        $project: {
            _id: { $concat: ['$_id.documentId', '-', '$_id.uid', '-', '$_id.clientType', '-', { $arrayElemAt: ['$history.occurredAt', 0] }] },
            uid: { $arrayElemAt: ['$history.uid', 0] },
            documentId: { $arrayElemAt: ['$history.documentId', 0] },
            lastViewedAt: { $arrayElemAt: ['$history.occurredAt', 0] },
            totalDurationMilli: 'unknown',
            history: 1,
        },
    },
]

Which spits out this (mongodb playground):

    {
        _id: "d0-u0-web-2020-06-12T17:00:22.000Z",
        uid: "u0",
        documentId: "d0",
        lastViewedAt: "2020-06-12T17:00:22.000Z",
        totalDurationMilli: 'unknown',
        history: [
            {
                "_id": "1",
                "interactionType": "pageEnter",
                "uid": "u0",
                "documentId": "d0",
                "clientType": "web",
                "routePath": "/d0",
                "occurredAt": "2020-06-12T17:00:22.560Z"
            },
            {
                "_id": "2",
                "interactionType": "pageExit",
                "uid": "u0",
                "documentId": "d0",
                "clientType": "web",
                "routePath": "/d0",
                "occurredAt": "2020-06-12T17:00:32.000Z"
            },
            {
                "_id": "3",
                "interactionType": "pageEnter",
                "uid": "u0",
                "documentId": "d0",
                "clientType": "web",
                "routePath": "/d0/a",
                "occurredAt": "2020-06-12T17:00:42.000Z"
            },
            {
                "_id": "4",
                "interactionType: "pageExit",
                "uid": "u0",
                "documentId": "d0",
                "clientType": "web",
                "routePath": "/d0/a",
                "occurredAt": "2020-06-12T17:00:52.000Z"
            },
            {
                "_id": "5",
                "interactionType": "pageEnter",
                "uid": "u0",
                "documentId": "d0",
                "clientType": "web",
                "routePath": "/d0",
                "occurredAt": "2020-06-12T17:03:42.000Z"
            },
            {
                "_id": "6",
                "interactionType": "pageExit",
                "uid": "u0",
                "documentId": "d0",
                "clientType": "web",
                "routePath": "/d0",
                "occurredAt": "2020-06-12T17:03:52.000Z"
            }
        ]
    }

My biggest issue is I can't figure out how to actually group these items correctly. Are there any specific helpers I can use to solve this?

0 Answers
Related