CODEWITHSUNDEEP
mongodbmongodb-query
Christian Dechery
Christian Dechery

Reputation: 876

Fetching data from different types of documents in one query

So, what I need is not exactly a join, but something very similar. The main difference is that all the data I need is in the same collection. The other answers I've seen, related to joins in MongoDB are all between collections.

In my collection I have different types of documents.

For the sake of example, consider these two:

{
    "success" : true,
    "action" : "logEventGA",
    "timestamp" : 1490795738759,
    "terminal" : "3533322585",
    "device" : {
        "cordova" : "4.3.0",
        "model" : "iPhone8,1",
        "platform" : "iOS",
        "uuid" : "785ED0AB-996D-4E54-8E0F-404FE076BE4F",
        "version" : "10.2.1",
        "manufacturer" : "Apple",
        "isVirtual" : false,
        "serial" : "unknown"
    },
    "action_GA" : "login"
}

and

{
    "success" : true,
    "action" : "loginAttempt",
    "timestamp" : 1483125811453,
    "terminal" : "3533322585",
    "id": {
        "doc": 23324355,
        "valid": true
    }   
    "httpCode" : NumberInt("200")
}

The defining characteristic of each one is their action. Depending on the action an object will have different informations.

So, I need a query to grab information from both these kinds of documents (actions "loginAttempt" and "logEventGA"), with something to match (join?) them - in this case, we can use the terminal attribute.

I tried running a query on the first type of document, than a forEach to grab the remaining data for the other type of document (doesn't matter the order) but it is painfully slow. Also, there are other criteria for each action (e.g. when action=loginAttempt, httpCode must be 200). Is there a way to do this all in one go?

Below is my attempt at the "join" for which the performance is sluggish. It takes like two seconds per line to process, and it seems to get slower and slower as it goes. Can't use this.

db.Logging.find({
    "timestamp": { $gte: new Date('2017-04-01').getTime() },
    "action":"loginAttempt", 
    "httpCode": 200
}, {
    terminal: 1, 
    "id.doc": 1,
    _id: 0
}).forEach(function(obj) { 
    let eventGA = db.Logging.findOne({"terminal":obj.terminal, "action_GA":"login"},{ _id:0, "device.uuid":1});
    console.log(obj.terminal+';'+obj.id.doc+';'+eventGA.device.uuid);
});

The expected outcome would be something like this (or as in CSV form in my code above):

{
        "terminal": 3533322585, // from either doc
        "uuid": "785ED0AB-996D-4E54-8E0F-404FE076BE4F", // from "logEventGA"
        "doc": 23324355 // from "loginAttempt"
}

Upvotes: 0

Views: 113

Answers (1)

s7vr
s7vr

Reputation: 75934

You can try below aggregation query. This will only work for a pair of two.

The below query sorts the data on action so it can use the sort to project the document fields. 

db.collection.aggregate(
{$match:{$or:[{ "timestamp": { $gte: new Date('2016-04-01').getTime() },
    "action":"loginAttempt", 
    "httpCode": 200},{"action":"logEventGA"}]}},
{$sort:{action:1}},
{$group:{"_id":"$terminal", "first":{"$first":"$$ROOT"}, "second":{"$last":"$$ROOT"}}},
{$project:{terminal:"$_id", device:"$first.device.uuid", doc:"$second.id.doc"}}
)

Upvotes: 1

Related Questions