We will create a stored procedure that does not change the variable value whenever the value is updated.Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Value int    ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(100); Query OK, 1 row affected (0.13 sec) Display all records from the table using select statement : mysql> select *from DemoTable;Output+----+-------+ | Id | Value | +----+-------+ | 1 | 100 | +----+-------+ 1 row ... Read More

You can use $addToSet operator for this. Let us first create a collection with documents −> db.insertDataIntoArrayDemo.insertOne(    {       "UserDetails":[          {             "UserId" :"user121",             "userGroupMessage":[]          },          {             "UserId" :"user221",             "userGroupMessage":["Cool", "Good Morning"]          }       ]    } ); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd694e157806ebf1256f128") }Following is the query to display all documents from ... Read More

To perform SELECT with COUNT, use aggregate function COUNT(). Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(100),    Subject varchar(100)    ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Subject) values('John', 'MySQL'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name, Subject) values('John', 'Java'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name, Subject) values('Carol', 'MongoDB'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Name, Subject) values('Carol', ... Read More

To change data type from date to date/time, use alter command.alter table yourTableName change yourColumnName yourColumnName datetime;Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    LoginDate date    ); Query OK, 0 rows affected (1.26 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(LoginDate) values('2019-01-21'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable(LoginDate) values('2018-05-01'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(LoginDate) values('2017-12-31'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement ... Read More

To get MySQL LIKE statement, use the REGEX in MongoDB. Let us first create a collection with documents −> db.likeInMongoDBDemo.insertOne({"Name" : "Sam"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6922857806ebf1256f123") } > db.likeInMongoDBDemo.insertOne({"Name" : "John" }); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6923157806ebf1256f124") } > db.likeInMongoDBDemo.insertOne({"Name" : "Scott"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6924557806ebf1256f125") } > db.likeInMongoDBDemo.insertOne({"Name" : "Sean"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6924f57806ebf1256f126") } > db.likeInMongoDBDemo.insertOne({"Name" : "Samuel"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6925857806ebf1256f127") }Following is the query to display all documents from a collection with ... Read More

To merge, use $setUnion operator. Let us first create a collection with documents −> db.mergeTwoArrayFieldDemo.insertOne({"NaturalNumbers":[1,2,3,8,10,20,30],"WholeNumbers":[0,1,2,63,78,20,45]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68e4057806ebf1256f11d") }Following is the query to display all documents from a collection with the help of find() method −> db.mergeTwoArrayFieldDemo.find().pretty();This will produce the following output −{    "_id" : ObjectId("5cd68e4057806ebf1256f11d"),    "NaturalNumbers" : [       1,       2,       3,       8,       10,       20,       30    ],    "WholeNumbers" : [       0,       1,       2,       63,       78,       20,       45    ] }Following is the query to merge two array field in MongoDB.> db.mergeTwoArrayFieldDemo.aggregate([    { "$project": {       "MergedArray": { "$setUnion": [ "$NaturalNumbers", "$WholeNumbers" ] }    }} ]);This will produce the following output −{ "_id" : ObjectId("5cd68e4057806ebf1256f11d"), "MergedArray" : [ 0, 1, 2, 3, 8, 10, 20, 30, 45, 63, 78 ] }

Let us first create a collection with documents −> db.findByObjectIdDemo.insertOne({"ClientName":"Larry", "ClientAge":23}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68cd657806ebf1256f11a") } > db.findByObjectIdDemo.insertOne({"ClientName":"Chris", "ClientAge":26}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68cdc57806ebf1256f11b") } > db.findByObjectIdDemo.insertOne({"ClientName":"David", "ClientAge":38, "isMarried":true}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68cf657806ebf1256f11c") }Following is the query to display all documents from a collection with the help of find() method −> db.findByObjectIdDemo.find().pretty();This will produce the following output −{    "_id" : ObjectId("5cd68cd657806ebf1256f11a"),    "ClientName" : "Larry",    "ClientAge" : 23 } {    "_id" : ObjectId("5cd68cdc57806ebf1256f11b"),    "ClientName" : "Chris",    "ClientAge" : 26 } { ... Read More

Let’s say you have saved the Login date of users. Now, you want the users who logged in between specific dates i.e. login date. For this, use $gte and $lt operator along with count(). Let us first create a collection with documents −> db.findDataByDateDemo.insertOne({"UserName":"John", "UserLoginDate":new ISODate("2019-01-31")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cdd8cd7bf3115999ed511ed") } > db.findDataByDateDemo.insertOne({"UserName":"Larry", "UserLoginDate":new ISODate("2019-02-01")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cdd8ce7bf3115999ed511ee") } > db.findDataByDateDemo.insertOne({"UserName":"Sam", "UserLoginDate":new ISODate("2019-05-02")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cdd8cf3bf3115999ed511ef") } > db.findDataByDateDemo.insertOne({"UserName":"David", "UserLoginDate":new ISODate("2019-05-16")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cdd8d00bf3115999ed511f0") } > db.findDataByDateDemo.insertOne({"UserName":"Carol", ... Read More

You can use $group operator with _id: null. Following is the syntax −db.yourCollectionName.aggregate([{$group: {_id:null, "anyFieldName": {$avg:"$yourFieldName"} } }]);Let us first create a collection with documents −> db.caculateTheAverageValueDemo.insertOne({"Population":100}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68a197924bb85b3f4895f") } > db.caculateTheAverageValueDemo.insertOne({"Population":500}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68a1c7924bb85b3f48960") } > db.caculateTheAverageValueDemo.insertOne({"Population":200}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68a237924bb85b3f48961") } > db.caculateTheAverageValueDemo.insertOne({"Population":100}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68a297924bb85b3f48962") } > db.caculateTheAverageValueDemo.insertOne({"Population":100}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68a2e7924bb85b3f48963") }Following is the query to display all documents from a collection with the help of find() ... Read More

Use aggregate framework along with $ifNull operator for this. The $concatArrays in aggregation is used to concatenate arrays. Let us first create a collection with documents −>db.concatenateArraysDemo.insertOne({"FirstSemesterSubjects": ["MongoDB", "MySQL", "Java"], "SecondSemesterSubjects":["C", "C++", ]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd687707924bb85b3f4895c") } > db.concatenateArraysDemo.insertOne({"FirstSemesterSubjects":["C#", "Ruby", "Python"]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd687927924bb85b3f4895d") } >db.concatenateArraysDemo.insertOne({"FirstSemesterSubjects":["HTML", "CSS", "Javascript"], "SecondSemesterSubjects":["CSS", "Javascript"]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd687bb7924bb85b3f4895e") }Following is the query to display all documents from a collection with the help of find() method −> db.concatenateArraysDemo.find().pretty();This will produce the following output −{    "_id" : ObjectId("5cd687707924bb85b3f4895c"), ... Read More