CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-query
Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17586

How to get the max with a where condition in this situation Mongoose / Node

I am working with Mongoose, I have a collection that has documents like this 

{
"_id" : 1,
    "body" : "[{\"order_id\":\"647936\",\"order_datetime\":\"2015-12-02 11:10:00\"}]",
    "user_info" : {
        "contact_email" : "[email protected]",
        "contact_phone" : "1234567",
    },
    "type" : "ORDERS",
    "version" : 1
}

{
"_id" : 2,
    "body" : "[{\"order_id\":\"647936\",\"order_datetime\":\"2015-12-02 11:10:00\"}]",
    "user_info" : {
        "contact_email" : "[email protected]",
        "contact_phone" : "1234567",
    },
    "type" : "ORDERS",
    "version" : 2
}

{
"_id" : 3,
    "body" : "[{\"order_id\":\"647936\",\"order_datetime\":\"2015-12-02 11:10:00\"}]",
    "user_info" : {
        "contact_email" : "[email protected]",
        "contact_phone" : "1234567",
    },
    "type" : "ORDERS",
    "version" : 3
}

As you can see in body field you can see the order_id , so same order_id can be repeated in multiple in documents but the version will be different.

What I want is I want to search for the maximum version number for a given order_id .

In my case it would be 3 .

I tried to use simple queries like 

myCollection.aggregate([
                        { "$match" : { "body.order_id" : 647936 } },
                        { "$group": {
                            "_id" :"version",
                            "max": { "$max": "version" }
                            }}
                        ] , function(err, data){

                                console.log(err);
                                console.log(data);
                            });

But the result is 

null
[]

** Note that my mongoose connection is working fine and I can do some simple queries and results are OK.

Upvotes: 0

Views: 292

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50416

Your data is the problem here since what seems to be intended as a structured document has been stored as a string:

// Bad bit
"body" : "[{\"order_id\":\"647936\",\"order_datetime\":\"2015-12-02 11:10:00\"}]",

Instead you would want this:

// Acutally data and not a string
"body" : [{ "order_id": "647936", "order_datetime": ISODate("2015-12-02 11:10:00.000Z" }],

With data like that, getting the latest version is a simple matter of ordering the results, without the overhead of .aggregate():

myCollection.find({ "body.order_id": "647936" })
    .sort({ "version": -1 }).limit(1).exec(function(err,result) {

})

No need to aggregate and it's much faster than doing so, as you are just picking out the document with the latest (largest) version number.

In order to "fix" the data you can do something like this as a "one shot" execution in the shell:

var bulk = db.myCollection.initializeOrderedBulkOp(),
    count = 0;

// query selects just those "body" elements that are currently a string
db.myCollection.find({ "body": { "$type": 2 } }).forEach(function(doc) {
    var fixBody = JSON.parse(doc.body);  // Just parse the string
    fixBody.forEach(function(el) {
        // Fix dates
        el.order_datetime = new Date(
            Date.parse(el.order_datetime.split(" ").join("T") + "Z")
        );
    });

    // And queue an update to overwrite the "body" data
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "body": fixBody }
    });
    count++;

    // Send every 1000
    if ( count % 1000 == 0 ) {
        bulk.execute();
        bulk = db.myCollection.initializeOrderedBulkOp(),
    }
});

// Send any remaining batched
if ( count % 1000 != 0 )
    bulk.execute();

You might also want to convert those "strings" other than the date to numeric values in a similar fashion and change appropriately in the query.

Upvotes: 1

Related Questions