Working with MongoDB in .Net Core (Part 5) - Mongo Queries

Mongo Aggregation pipeline Queries

//Pipeline Query
use Collection;
db.getCollection(collectionName).aggregate(
    [
        {
            "$match" : {
                "{field1}" : { "$eq" : "string" },
                "{field2}" : { "$eq" : "string" }
            },
        },
        {
            "$group" : {
                "_id" : "$field",
                "Count" : { "$sum" : 1.0 },
                "Results" : { "$addToSet" : "$field" }
            }
        },
        {
                "$sort" : { "Count" : -1.0 }
        },
        {
                "$project" : {
                    "count" : 1.0,
                    "_id" : 1.0,
                    "AccountId" : 1.0
                }
        }
    ],
    {
        "allowDiskUse" : false
    }
);
//Query to find unique field values
use Collection;
db.getCollection(collectionName).aggregate(
    [
        {
            "$group" : {
                "_id" : "$field",
                "Count" : { "$sum" : 1.0 },
            }
        },
        {
                "$sort" : { "Count" : -1.0 }
        }
    ],
    {
        "allowDiskUse" : false
    }
);
//Query to get sample field values
use Collection;
db.getCollection(collectionName).aggregate(
    [
        {
            "$match" : {
                "$and" : [
                    {
                        "{field}" : { "$ne" : "null" },
                    },
                    {
                        "{field}" : { "$ne" : "[ ]" },
                    }
                ]
            }
        },
        {
                "$project" : {
                    "field" : 1.0
                }
        }
    ],
    {
        "allowDiskUse" : false
    }
);
//Update Call - Looping through documents
function Update()
{
    print("Start time:" + new Date.toISOString());
    var counter=0;
    db.collectionname.find({"{field}" : {"$exists" : false}}).forEach(function(doc)
    {
        var maxDate;
        if ({datefield1} != null && {datefield2} != null)
        {
            var date1 = {datefield1}.toISOString();
            var date2 = {datefield2}.toISOString();
            if(date1 > date2)
                maxDate = date1;
            else
                maxDate = date2;
        }
        var result = db.collectionname.Update({_id: doc.id}, { $set : { datefield3 : ISODate(maxDate)}});  //Update statement
        counter += result.nModified;  //Increments counter of only the docs modified and ignores docs that failed to udpate due to some error/issues
    });

    print("Documents updated:" + counter);
    print("End Time:" + new Date().toISOString())
};
//Upload temp table data into collections

function Update()
{
    var tempCount = db.temp.count();
    if(tempCount == 0)
       print("No Input Data Found");
    else
       print("Total Records Imported" + tempCount);
    
    var bulk = db.collectionName.initializeUnorderedBulkOp();

    db.temp.find().forEach(function(doc) {
        var res = db.collectionName.find({ "field": value});
        if(res.count() <= 0) 
            print("does not exist");
        else
            //bulk update and insert new documents and add new fields for inserted documents only
            bulk.find({ field: value }).upsert().update({ $set: { "field" : value, "field": NumberInt(value), "datefield": new ISODate()}, $setOnInsert: { field: value}});     
    });

    var result = bulk.execute();
    var updatedCount = result.nModified;
    var upsertedCount = result.nUpserted;
    print(updatedCount);
    print(upsertedCount);
}

Update();

db.temp.drop(); //drop temp collection after execution

//Indexes (naming conventions - idx_field1_1, idx_field1_-1)
db.collectionName.dropIndex("idx_name");
db.collectionName.createIndex({"field1": 1}, {"background": true, "name": "index_name", sparse: true}); //Sparse Index
db.collectionName.createIndex({"field1": 1}, {"background": true, "name": "index_name"}); 
db.collectionName.createIndex({"field1": 1, "field2": 1}, {"background": true, "name": "idx_field1_field2_1"}); 
db.collectionName.createIndex({"field1": 1}, {expireAfterSeconds: 220752000, "name": "index_name"}); //TTL Index





Syntax Highligher - https://tohtml.com/jScript/