Quicks Hacks:
1. db.getCollection('books').aggregate([//{$match:{date:{"$gte":new ISODate("2022-05-23")}}},
{$group:{_id: { $month: "$time" },count:{$sum:1}}}, // { _id: 10, count: 2 }
//_id: { $dateToString: {date: "$time",format: "%Y-%m"}},count:{$sum:1}}}, // { _id: "2012-10", count: 2 }
,{$sort:{count:-1}}])
Reference:
[1]: https://www.bmc.com/blogs/mongodb-memory-usage-and-management/
This good one for checking the monogodb mem analyse: db.serverStatus().mem ,
[2]: https://www.mongodb.com/docs/v4.2/tutorial/analyze-query-plan/
@ Mongo DB Queries ......
UPDATE: db.subscriber.update( { username:"1003" }, { $set:{ "balance":10.00 } } )
Multiple Update:
db.subscriber.updateMany( { username: {$regex: /sipp/} }, { $set:{ "balance":10.00 } } )
CURSOR:
.limit( nPerPage )
.count()
FILTER:
db.subscriber.find({username:"1003"})
{duration: {$gt: 300}}
Pattern Match: {user: {$regex: /^sipp$/} }
SORTING: 1= ascending, -1 = descending
db.subscriber.find({balance:{$lt:0.04}}).sort( { balance: 1 } )
db.subscriber.find().sort( { "item.category": 1, "item.type": 1 } ) // Sort within sort
SELECT:
s=db.subscriber;
s.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )
SELECT item, status from subscriber WHERE status = "A"
db.subscriber.find( { status: "A" }, { _id: 0 } ) // just removes _id
BETWEEN TWO DATES:
db.cdr.find({
starttime:{
$gt:ISODate("2018-05-14T00:08:16.111Z"),
$lt:ISODate("2018-05-15T10:08:16.111Z")}
}).pretty();
FUNCTION:
db.system.js.save( {
_id : "myAddFunction" ,
value : function (x, y){ return x + y; print "hello" }
});
db.loadServerScripts();
myAddFunction(3, 5);
$where:
db.subscriber.find( { $where: function() {
return (hex_md5(this.name) == "9b53e667f30cd329dca1ec9e6a83e994")
} } );
SetOnInsert:
db.tariff.update(
{ _id: 1 },
{ $set: { item: "apple" }, $setOnInsert: { dateModified: new Date() }},
{ upsert: true }
);
AGGREGATE (match):
LInk2 - Just group explained
Link3 - equal commands for SQL
Link3 - MongoDB gui expalin
Sample 1:
db.articles.aggregate( [
{ $match: { $or: [ { score: { $gt: 70, $lt: 90 } }, { views: { $gte: 1000 } } ] } },
{ $group: { _id: null, count: { $sum: 1 } } }
] );
Output: { "_id" : null, "count" : 5 }
Eg:
db.cdr.aggregate([
{ $match: {
starttime:{
$gt:ISODate("2018-04-28T00:00:00.000Z"),
$lt:ISODate("2018-05-17T10:00:00.000Z")}}
},{
$group: {
_id: null,
debited_amt_sum: { $sum: "$debited_amt" },
duration_sum: { $sum: "$duration" },
count: {$sum: NumberInt(1)}
}
}
]);
Aggregate length: aggregate().toArray().length; // to get size of result
TO Get Size of array count: (get_device_id_size_wise)
db.subscriber.aggregate([
{ $project: { username: 1,
count: { $cond: { if: { $isArray: "$dlist" }, then: { $size: "$dlist" }, else: 0} }
} }
, {$sort:{count:-1}}
] )
TO RENAME DATA TYPE
db.temp.find({name: {$exists:true}}).forEach( function(x) {
db.temp.update({_id: x._id}, {$rename: {name: x.name.toString()}});
});
Add Update datatype long into to string Serial Number into db:
var counter = 4090
db.getCollection('temp').find({batchname:"batch1",serial:{$gt:0}}).sort({modified_date:1}).forEach(function(doc){
result = db.temp.updateOne({ _id: doc._id }, { $set: { serial: counter+"" }});
counter = counter + 1;
})
db.getCollection('subscriber').find({did: {$ne : null}}).forEach(function(doc){
print(doc.did)
//result = db.subscriber.updateOne({ _id: doc._id }, { $set: { dlist: [doc.did] }});
})
eg: Move field to Array list of object [Link] db.subscriber.find({pin:{$ne:""},vlist:{$ne:[]},group:"pin",username:"test1"}).forEach(
function( r ) {
var newItems = []; var pair = { pin: r.pin, pass: r.pass }; newItems.push( pair );
r.vlist.forEach( function( item ){ newItems.push( item ); } );
// printjson(typeof(r.vlist)); printjson(newItems);
db.subscriber.update({ username: r.username }, { "$set": { pin:"", pass:"", "vlist": newItems } } );
});
Set with Condition:
{ "$set":{ "users.user": { $cond:[ { $eq: [ "$user","$users.user"]}, "pin", "$users.user" ] }} },
Slice:
db.cdr.aggregate([
{$match: { callid :"1234561336" } },
{ $project: { rU: 1, users: { $slice: [ "$users" ,-3,2] } } }
// { "$set":{ "users.user": { $cond:[ { $eq: [ "$user","$users.user"]}, "pin", "$users.user" ] }} },
])
(or) Move to array to ROOT
{ $project: { rU: 1, users: { $slice: [ "$users" ,-2,1] }, rcvd: { $slice: [ "$users" ,-1,1] } } },
Find if the element is only Array:
db.cdr.find({ "$where": "return Array.isArray(this.objuser)"})
{_id: 1, userobj: [ { user: "test", dur: 10} ] }. // this matches
{_id: 1, userobj: { "test": { user: "test", dur: 10} } } // Does not matchesdb.cdr.find({$where: "(this.fieldname.length == 16)"})
[ref]Modify the document using another field in mongo same collection: var cursor = db.cdr.aggregate([
{$match: { caller:"test"}},
{ $addFields: { usersdata: [ "$objuser.test" ] ,cid: "$callid"}}
])
var requests = []; var count = 0; var i=0; var countWrite = 0;
cursor.forEach(doc => { //printjson(doc._id)
requests.push( {
'updateMany': {
'filter': { '_id': doc._id },
'update': { '$set': { 'users': doc.usersdata } },
'upsert' : true
}
});
//printjson(requests[count])
count ++; i++;
if (requests.length === 100) { //Execute per 500 operations and re-init
db.cdr.bulkWrite(requests,{ordered:true});
countWrite += requests.length;
requests = []; count =0;
}
});
if(requests.length > 0) {
countWrite += requests.length;
db.cdr.bulkWrite(requests),{ordered: true};
}
print("totcount="+i+ " countWrite="+countWrite);
{_id: 1, userobj: { "test": { dur: 10} } } // This will
{_id: 1, users: [ { user: "test", dur: 10} ] } // become like this
Note: Make async set {ordered:false}
Alternate method:
//For mongoDB version >4.2 : merge into same doc supported from 4.4 onwards only;
db.doc.aggregate([{ $match: {'experience.0': { $exists: false } } },
{$project:{experience:["$experience.0"]}}, { $merge: { into: "doc", on: "_id" }
])
Lets assume your collection name is doc:
db.doc.aggregate([
{$group: {_id:"$_id", experience:{$push:"$experience.0"}}},
{$out: "doc"}
])
db.cdr.aggregate([{ $match: {'users': { $exists: true } ,user: "Mujeeb_IN" }},
{$limit:2},
{$addFields:{adur: "$dur"}},
{$out: "cdr"}
])
To List:
db.people.getIndexes()
How indexes works -> Click here
Transactions in mongodb:
https://hackernoon.com/mongodb-transactions-5654cdb8fd24
Do Aggregation efficiently:
=========================
[Ref]:
https://stackoverflow.com/questions/21509045/mongodb-group-by-array-inner-elements
db.articles.aggregate([
{$match: { class_date: { $gte: date } } },
{$project: { _id: 0, class_artist: 1 } },
{$unwind: "$class_artist" },
{$group: { _id: "$class_artist", tags: { $sum: 1 } }},
{$project: { _id: 0,class_artist: "$_id", tags: 1 } },
{$sort: { tags: -1 } }
])
- Filter by date because you already set a var for the last 7 days
- Project only the field(s) we need { We need only one! }
- Unwind the array so we now have a record for every array element in every document
- Group on the Artist from the expanded documents
- Project into a document format you can use as group messed around with _id
- Sort the results in reverse order to see the top tagged first
Figure 3 shows a conceptual model for the Aggregation Framework pipeline. This is what's happening at each stage:
- On the left-hand side/start of the pipeline is the original collection contents – each record (document) containing a number of shapes (keys), each with a particular color (value)
- The
$match stage filters out any documents that don't contain a red diamond - The
$project stage adds a new “square” attribute with a value computed from the value (color) of the snowflake and triangle attributes - The
$lookup stage (new in 3.2 - more details later) performs a left-outer join with another collection, with the star being the comparison key. This creates new documents which contain everything from the previous stage but augmented with data from any document from the second collection containing a matching colored star (i.e., the blue and yellow stars had matching “lookup” values, whereas the red star had none). - Finally, the
$group stage groups the data by the color of the square and produces statistics (sum, average and standard deviation) for each group.
$lookup – Left Outer Equi-Joins
Figure 4 illustrates the syntax for performing the join:
leftCollection is the collection that the aggregation is being performed on and is the left collection in the joinfrom identifies the collection that it will be joined with – the right collection (rightCollection in this case)localField specifies the key from the original/left collection – leftValforeignField specifies the key from the right collection – rightValas indicates that the data from the right collection should be embedded within the resulting documents as an array called embeddedData
eg: https://www.mongodb.com/blog/post/joins-and-other-aggregation-enhancements-coming-in-mongodb-3-2-part-2-of-3-worked-examples
