Vim Info: Mongo DB Queries ......

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 matches

db.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"}

])

SEARCH TEXT IN FIELDS:
Create text indexes and use $text in find query
Ref: MongoDB Create Indexes and $text in find() query
Click to Manage Index. 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 join
from 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 – leftVal
foreignField specifies the key from the right collection – rightVal
as 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

Vim Info

Thursday, June 17, 2021

Mongo DB Queries ......

$lookup – Left Outer Equi-Joins

No comments: