To execute SHOW CREATE TABLE in a stored procedure, use SHOW CREATE TABLE. Let us first create a table −mysql> create table DemoTable2011    -> (    -> StudentId int NOT NULL AUTO_INCREMENT,    -> StudentName varchar(20),    -> StudentAge int,    -> StudentCountryName varchar(20),    -> PRIMARY KEY(StudentId)    -> ); Query OK, 0 rows affected (0.80 sec)Following is the stored procedure executing SHOW CREATE TABLE −mysql> delimiter // mysql> create procedure test_show_create_demo(table_name varchar(100))    -> begin    -> set @query=concat("SHOW CREATE TABLE ", table_name);    -> prepare st from @query;    -> execute st;    -> end   ... Read More

To merge multiple documents in MongoDB, use aggregate(). Let us create a collection with documents −> db.demo436.insertOne( ...    { ...       "_id" : "101", ...       "Name": "Chris", ...       "details" : [ ...          { ...             "CountryName" : "US", ...             "Age" : 21 ...          } ...       ], ...       "Price" : 50 ...    } ... ); { "acknowledged" : true, "insertedId" : "101" } > db.demo436.insertOne( ... ... Read More

To append to array in MongoDB, use $concatArrays. Let us create a collection with documents −> db.demo435.insertOne({"FirstName":["Chris"], "LastName":["Brown"]} ); {    "acknowledged" : true,    "insertedId" : ObjectId("5e7719b1bbc41e36cc3cae97") } > db.demo435.insertOne({"FirstName":["David"], "LastName":["Miller"]} ); {    "acknowledged" : true,    "insertedId" : ObjectId("5e7719bdbbc41e36cc3cae98") } > db.demo435.insertOne({"FirstName":["John"], "LastName":["Doe"]} ); {    "acknowledged" : true,    "insertedId" : ObjectId("5e7719c6bbc41e36cc3cae99") }Display all documents from a collection with the help of find() method −> db.demo435.find().pretty();This will produce the following output −{    "_id" : ObjectId("5e7719b1bbc41e36cc3cae97"),    "FirstName" : [       "Chris"       ],       "LastName" : [     ... Read More

To sum, use aggregate() along with $sum. Let us create a collection with documents −> db.demo434.insertOne({"Name":"Chris", "Score":45}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e771603bbc41e36cc3cae93") } > db.demo434.insertOne({"Name":"David", "Score":55}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e77161abbc41e36cc3cae94") } > db.demo434.insertOne({"Name":"Chris", "Score":55}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e771624bbc41e36cc3cae95") }Display all documents from a collection with the help of find() method −> db.demo434.find();This will produce the following output −{ "_id" : ObjectId("5e771603bbc41e36cc3cae93"), "Name" : "Chris", "Score" : 45 } { "_id" : ObjectId("5e77161abbc41e36cc3cae94"), "Name" : "David", "Score" : 55 } { "_id" : ObjectId("5e771624bbc41e36cc3cae95"), "Name" : "Chris", ... Read More

To filter a query on specific date format, use $dateToString. Let us create a collection with documents −> db.demo433.insertOne({"DueDate":new Date("2019-11-23")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e771278bbc41e36cc3cae91") } > db.demo433.insertOne({"DueDate":new Date("2020-01-03")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e771290bbc41e36cc3cae92") }Display all documents from a collection with the help of find() method −> db.demo433.find();This will produce the following output −{ "_id" : ObjectId("5e771278bbc41e36cc3cae91"), "DueDate" : ISODate("2019-11-23T00:00:00Z") } { "_id" : ObjectId("5e771290bbc41e36cc3cae92"), "DueDate" : ISODate("2020-01-03T00:00:00Z") }Following is the query to filter a query on specific date format in MongoDB −> db.demo433.aggregate([ ... { $addFields: {stringDate: { $dateToString: { format: ... Read More

For this, you can use aggregate(). We have considered test records as “Value1”, “Value2”, etc. Let us create a collection with documents −> db.demo432.insertOne( ...    { ...       "_id" : 101, ...       "Name" : "David", ...       "Value1" : 67, ...       "Value2" : 87, ...       "Value3" : 78 ...    } ... ) { "acknowledged" : true, "insertedId" : 101 } > db.demo432.insertOne( ...    { ...       "_id" : 102, ...       "Name" : "Sam", ...       "Value1" : ... Read More

The fastest way is to use replaceOne() in MongoDB. Let us create a collection with documents −> db.demo431.insertOne({"Name":"Chris", "Age":32}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e770ba6bbc41e36cc3cae89") } > db.demo431.insertOne({"Name":"David", "Age":31}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e770bacbbc41e36cc3cae8a") } > db.demo431.insertOne({"Name":"John", "Age":24}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e770bb3bbc41e36cc3cae8b") } > db.demo431.insertOne({"Name":"Bob", "Age":22}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e770bb8bbc41e36cc3cae8c") }Display all documents from a collection with the help of find() method −> db.demo431.find();This will produce the following output −{ "_id" : ObjectId("5e770ba6bbc41e36cc3cae89"), "Name" : "Chris", "Age" : 32 } { "_id" : ... Read More

Let us create a collection with documents −> db.demo430.insertOne( ...    { ...       "details": [ ...          { ...             "Name":"Chris" ...          } , ...          {"Name":"David"}, ...          {"Name":"Adam"}, ...          {"Name":"Bob"} ... ...       ] ...    } ... ); {    "acknowledged" : true,    "insertedId" : ObjectId("5e7702ddbbc41e36cc3cae88") }Display all documents from a collection with the help of find() method −> db.demo430.find();This will produce the following output −{ "_id" : ObjectId("5e7702ddbbc41e36cc3cae88"), "details" : [ { "Name" : "Chris" }, { "Name" : "David" }, { "Name" : "Adam" }, { "Name" : "Bob" } ] }Following is the query to group records −> db.demo430.aggregate([{ "$group" : { "_id" : {"Name" : "$details.Name"}}}]);This will produce the following output −{ "_id" : { "Name" : [ "Chris", "David", "Adam", "Bob" ] } }

For new field, use $addFields in MongoDB. The $addFields is used to add new fields to documents. Let us create a collection with documents −> db.demo429.insertOne( ...    { ...       "_id": 101, ...       "Value": 3, ...       "details": [ ...          { ...             "Age": 29, ...             "Value": 3, ...             "details1": [ ...             1, ...             2, ...       ... Read More

For multiple write operations, use bulkWrite() in MongoDB. Let us create a collection with documents −> db.demo428.insertOne({ "Name" : "Chris", "Age" : 21 }); {    "acknowledged" : true,    "insertedId" : ObjectId("5e75f428bbc41e36cc3cae83") } > db.demo428.insertOne({ "Name" : "Chris", "Age" : 23 }); {    "acknowledged" : true,    "insertedId" : ObjectId("5e75f429bbc41e36cc3cae84") } > db.demo428.insertOne({ "Name" : "David", "Age" : 22 }); {    "acknowledged" : true,    "insertedId" : ObjectId("5e75f42abbc41e36cc3cae85") } > db.demo428.insertOne({ "Name" : "David", "Age" : 21 }); {    "acknowledged" : true,    "insertedId" : ObjectId("5e75f42abbc41e36cc3cae86") }Display all documents from a collection with the help of ... Read More