MongoDB Aggregation Pipeline bucket & bucketAuto Stage

The The $bucket  and $bucketAuto stage in the aggregate method in the MongoDB Aggregation Pipeline to allow the incoming document of a collection to be categorized into groups called buckets. 

Point to Consider for $bucket Stage

  • The incoming document of a collection to be categorized into buckets
  • Each document in the bucket is applied with the groupby expression , specified by boundaries in the bucket
  • A default value is specified when the documents in the bucket having groupBy values outside of the boundaries
  • A default value is specified when the documents in the bucket having different BSON type than the values in boundaries
  • The buckets arranges the input document using $sort if the groupBy expression resolves to an array or a document
  • At least one document should be placed to form bucket. 
$bucket Syntax
{
$bucket: {
groupBy: <expression>,
boundaries: [ <lowerbound1>, <lowerbound2>, ... ],
default: <literal>,
output: {
<output1>: { <$accumulator expression> },
...
<outputN>: { <$accumulator expression> }
}
}
}
$bucket document fields

The $bucket document contains the below given fields:

Field  Type Description
groupBy expression expressions are used to groupby the documents. Each input document either should be groupby using expression or document value should be specified with the boundaries range
Boundaries Array

indicates an array of values (values must be in ascending order and all of the same type) based on the groupBy expression that specify the boundaries for each bucket. The adjacent pair of values indicates lower boundary and upper boundary for the bucket.
Example for boundary with lover and upper ranges
An array of [ 4, 8, 15 ] creates two buckets:

[4,8) with inclusive lower bound 4 and exclusive upper bound 8.
[8, 15) with inclusive lower bound 8 and exclusive upper bound 15

default Literal

indicates the _id of an additional bucket that contains all documents whose groupBy expression result does not fall into a bucket specified by boundaries.

The default value can be of a different type than boundaries types and should be less than the lowest boundaries value, or greater than or equal to the highest boundaries value

output Document

indicates the fields to include in the output documents with _id field (in case of default value) using accumulator expressions

<outputfield1>: { <accumulator>: <expression1> },

<outputfieldN>: { <accumulator>: <expressionN> }

$bucketAuto Stage:

The $bucketAuto is similar to $bucket for grouping the incoming document but in the $bucketAuto it automatically determines the bucket boundaries to evenly distribute the documents into the specified number of buckets.

Point to Consider for $bucketAuto Stage

  • Allows for grouping the incoming document 
  • Automatically determines the bucket boundaries to evenly distribute the documents into the specified number of buckets
  • The _id.min field indicates the inclusive lower bound for the bucket
  • The _id.max field indicates the exclusive upper bound for the bucket
  • The final bucket in the series will have inclusive upper bound
  • The count field that contains the number of documents in the bucket

$bucketAuto Syntax

 {
$bucketAuto: {
groupBy: <expression>,
buckets: <number>,
output: {
<output1>: { <$accumulator expression> },
...
}
granularity: <string>
}
}

The $bucketAuto contains the below given fields:

Field  Type Description
groupBy expression used to groupBy the incoming documents 
Buckets integer 32-bit integer that indicates the bucket count into which input documents are grouped
Output document

indicates the fields in the output documents with _id field by using accumulator expressions. The default count is to be added explicitly in the output document.

output: {
<outputfield1>: { <accumulator>: <expression1> },

count: { $sum: 1 }
}

Granularity string

indicates the preferred number series to calcualte the boundary edges end on preferred round numbers or their powers of 10. It can be applicable if the all groupBy values are numeric and none of them are NaN.

Supported value for granularity are:

“R5”
“R10”
“R20”
“R40”
“R80”
“1-2-5”
“E6”
“E12”
“E24”
“E48”
“E96”
“E192”
“POWERSOF2”

The documents are ordered using $sort before determining the bucket boundaries if the groupBy expression refers to an array or document

 


MongoDB Aggregation Pipeline addFields Stage

The $addFields stage is one of the stages to be used in the MongoDB Aggregation Pipeline stages. The $addFields stage  allows to add new fields in the document. The generated output document  contains the existing fields and new fields added using $addFields stage

Point to Consider for $addFields Stage:

  • $addFields appends new fields to existing documents
  • An aggregation operation can include one or more $addFields stages
  • $addFields can be added to embedded documents having arrays using dot notation
  • $addFields can be added to an existing array field using $concatArrays

$addFields Syntax: 

{ $addFields: { <newField1>: <expression1>, <newField2>: <expression2>,... } }
Aggregation Pipeline with $addFields  example

Lets consider a collection – studentMarks with the below given documents. 

studentMarks
{
_id: 1,
subject: "Computer Science",
student: "Mohit Sharma",
assignment: [ 14, 17 ],
test: [ 18, 12 ],
extraCredit: 15
}
{
_id: 2,
subject: "Computer Science",
student: "Rohan Kapoor",
assignment: [ 18,16 ],
test: [ 14,16 ],
extraCredit: 14
}

We need to add 3 new $addFields as assignmentTotal , testTotal, creditTotal to be added in the output document.

db.studentMarks.aggregate( [
{
$addFields: {
assignmentTotal: { $sum: "$assignment" } ,
testTotal: { $sum: "$test" }
}
},
{
$addFields: { totalMarks:
{ $add: [ "$assignmentTotal", "$testTotal", "$extraCredit" ] } }
}
] )

The operation returns the output documents which includes the 3 new fields 

{
"_id": 1,
"subject": "Computer Science",
"student": "Mohit Sharma",
"assignment": [ 14, 17 ],
"test": [ 18, 12 ],
"extraCredit": 15
"assignmentTotal" : 31,
"testTotal" : 30,
"totalMarks" : 76
}
{
"_id": 2,
"subject": "Computer Science",
"student": "Rohan Kapoor",
"assignment": [ 18,16 ],
"test": [ 14,16 ],
"extraCredit": 14,
"assignmentTotal" : 34,
"testTotal" : 30,
"totalMarks" : 78
}
Adding Fields to an Embedded Document

Embedded documents can be added with new fields using dot notation.  Consider the below example for carModels with the provided fields in the document

{ _id: 1,
model: "Ford",
specs: { capacity: 5, wheels: 4 , doors:4}
}

{ _id: 2,
model: "Toyota",
specs: { capacity: 5, wheels: 2 , doors: 2 }
}

Add the new field gear into the embedded documents

db.carModels.aggregate( [
{
$addFields: {
"specs.gear": "automatic"
}
}
] )

The Aggregation operation includes the new field : gear in the output document

{ _id: 1,
model: "Ford",
specs: { capacity: 5, wheels: 4 , doors:4, gear: "automatic"}
}

{ _id: 2,
model: "Toyota",
specs: { capacity: 5, wheels: 2 , doors: 2 , gear: "automatic"}
}
Overwriting an existing field

If $addFields includes the existing field then the value provided in the $addField will replace the existing field value. Consider the below record for collection studentBranch

{ _id: 1, name: "Mohit Sharma", batch: "Computer Science" }

The $addField includes name as ‘John Smith” 

db.studentBranch.aggregate( [
  {
    $addFields: { "branch": "Java Programming" }
  }
] )

Then the aggregation operation changes the branch value for the student

{ _id: 1, name: "Mohit Sharma", batch: "Java Programming" }
Add $addField to an Array 

$addFields allow to add new element into an Array using the $concatArrays

 $concatArrays returns the concatenated array as the result

{ $concatArrays: [ <array1>, <array2>, ... ] }

consider the collection item with the below given documents

{ "_id" : 1, item: [ "icecream" ], type: [ "butterscotch", "strawberry" ] }
{ "_id" : 2, item: [ "shakes"] , type: ["apple", "banana" ] }

Add new element to type  as “chocolate”

 db.items.aggregate([
{ $match: { _id: 1 } },
{ $addFields: { type: { $concatArrays: [ "$type", [ "chocolate" ] ] } } }
])

The aggregation operation includes “chocolate” as type

{ "_id" : 1, item: [ "icecream" ], type: [ "butterscotch", "strawberry" , "chocolate"] }
{ "_id" : 2, item: [ "shakes"] , type: ["apple", "banana" ] }

$set stage:  The $set is an alias for $addFields 

{ $set: { <newField1>: <expression1>, <newField2>: <expression2>,... } }

Point to Consider for $set Stage:

  • $set appends new fields to existing documents
  • An aggregation operation can include one or more $set stages
  • $set can be added to embedded documents having arrays using dot notation
  • $set can be added to an existing array field using $concatArrays

MongoDB Aggregation pipeline operation

MongoDB performs the Aggregation operations where group values from multiple documents are combined and perform various operations to return a single result. MongoDB provides three ways to perform aggregation:

  • Aggregation Pipeline
  • Map-Reduce Function
  • Single Purpose Aggregation methods

The blog covers the Aggregation pipeline usage in detail.

Aggregation Pipeline in MongoDB

The Aggregation pipeline is a framework where documents are processed through multi-stage pipeline and perform various operations to transform the documents to return the aggregated results.  

Points to Consider for MongoDB Aggregation Pipeline

  1. Aggregation Pipeline consist of stages
  2. Each Pipeline Stage performs operation on the documents 
  3. Pipeline stage can produce multiple documents for the received input documents or can perform document filtration
  4. Pipeline stages can appear multiple times in the pipeline
  5. Pipeline stages – $0ut , $merge and $geoNear cannot appear multiple times
  6. Aggregated pipelines can be performed on shared collections

Customer Order Document Aggregation Example

Lets consider that we need to perform the aggregation on the CustomerOrders document and we need to apply the $match and $group stages to return the aggregated results.

Requirement: 

  1. Match the Customer Orders based on Customer Id with status =’InProgress”
  2. Group the aggregated documents based on Customer Id and Total Amount

aggregation-pipeline-example

We can apply the below 2 pipeline stages to perform document aggregation operation

  1. First Stage : Perform $match to filter the customer orders based on status value
  2. Second Stage: Perform $group based on CustId to calculate the aggregated total order amount

Aggregation Syntax:

db.collection.aggregate()  and db.aggregate() methods allows pipeline stages to be performed

db.collection.aggregate( [ { <stage> }, ... ] )
CustomerOrders Aggregation MongoDB Query:

db
.CustomerOrders .aggregate([ { $match: { status: "InProgress" } }, { $group: { _id: "$Custid", total: { $sum: "$Amount" } } } ])

MongoDB Aggregation Pipeline Expressions

Expressions in the MongoDB Aggregation operations helps in evaluating the values by executing the  expression at run-time.  Aggregation operations can perform nested expression which includes

  • Field Paths: allows to access fields in the input documents using $ as prefix . For instance customer.name is the field in the customerDetails Document , then can be accessed using $customer.name
  • Literals:  MongoDB parses string literals with $ as a path to a field.  MongoDB parses Numeric/ Boolean literals in expression objects
  • System Variables: “$<field>” is equivalent to “$$CURRENT.<field>” where the CURRENT is a system variable defaults to the root of the current object
  • Expression Objects:  allows to array of fields with expression as an object 
  • Expression Operators:  Operator expressions are similar to functions which allows array of argument 

MongoDB Aggregation Pipeline Behavior

The aggregation pipeline stages perform operations on a single collection with multiple documents. MongoDB uses the Query Planner to determine if the indexes can be used to improve the pipeline performance. The below given pipeline stage can use index  while processing / scanning the documents in the collection.

$match : uses an index to filter documents if it occurs at the beginning of a pipeline

$sort: uses an index if $sort it is not preceded by a $project, $unwind, or $group stage

$group: uses an index to find the first document in each group based on the given criteria:

  • $group stage is preceded by a $sort stage that sorts the field to group by
  • an index on the grouped field which matches the sort order and
  • the only accumulator used in the $group stage is $first

$geoNear: uses an index  when appear as the first stage in an aggregation pipeline

MongoDB Aggregation Pipeline Easy Filtering

The aggregation pipelines stages with the usage of $sort, $skip and $limit might be helpful when a subset of the documents required from the Collection. 

These stages allows to filter the documents when used at the beginning of the pipeline. 

When a $match pipeline stage is followed by a $sort stage at the start of the pipeline , then optimizer consider it as a single query with a sort operation. In such cases and index can be used also

MongoDB Aggregation Pipeline Limitations

The below given are the limitation for the aggregation operations when used with aggregate commands

  • Result Size restrictions: The aggregate commands when executed returns either the cursor or results in a collection where each document in the result set is set to BSON Document Size (maximum BSON document size is 16 MB) and exceeding this limit will return the error.
  • Memory restrictions: The MongoDB pipeline stages can use 100 MB of maximum RAM and exceeding this limit will return the error. For handling large document processing , use allowDiskUse option to enable aggregation pipeline stages to write data to temporary files 

MongoDB Aggregation Pipeline Optimization

The Aggregation Pipeline Optimization helps in improving the overall pipeline performance. The Aggregation operations passes through the optimization phase where the MongoDB optimizer transforms the aggregation pipeline using the explain option and db.collection.aggregate() method

Refer the below blog for details on MongoDB Aggregation pipeline optimization examples

MongoDB Aggregation Pipeline Optimization Examples

 

 

 


MongoDB Aggregation Pipeline Optimization

The Aggregation Pipeline Optimization helps in improving the overall pipeline performance. The Aggregation operations passes through the optimization phase where the MongoDB optimizer transforms the aggregation pipeline using the explain option and db.collection.aggregate() method.

Aggregation Pipeline Optimization Types

The below given are the optimization types available for Aggregation Pipeline:

  • Project Optimization
  • Pipeline Sequence Optimization
  • Pipeline Coalescence Optimization

Aggregation Pipeline – Project Optimization

The Project Optimization approach allows to determine if the subset of fields in the documents can help in achieving the required results and thus reduces the field data to be passed for the aggregation pipeline

Aggregation Pipeline –  Pipeline Sequence Optimization

The Pipeline sequence optimization allows to perform optimization on the aggregation pipeline which includes projection stage or sequence followed by $match operation. The below given are the possible pipeline sequence optimization types :

($project or $unset or $addFields or $set) + $match Sequence Optimization
  • The aggregation pipeline which includes projection stage ($project or $unset or $addFields or $set )  followed by $match operation
  • The aggregation pipeline could have multiple projection stages
  • There could be multiple $match stage including match filters
  • MongoDB performs optimization by executing $match filters before the projection stage which does not depend

 Consider the Aggregation Pipeline Stages with Sequence Optimization. The $match filter is applied at the end on the required fields from the projection stage . In such cases when the data volume is large , more processing time is consumed and $match filter is applied on the complete document data.

{ $addFields: {
maxSalary: { $max: "$salary" },
minSalary: { $min: "$salary" }
} },
{ $project: {
_id: 1, name: 1, times: 1, maxSalary: 90000, minSalary: 10000,
avgSalary: { $avg: ["$maxSalary", "$minSalary"] }
} },
{ $match: {
name: "Mohit Sharma",
maxSalary: { $lt: 90000},
minSalary: { $gt: 20000 },
avgSalary: { $gt: 45000 }
} }

Now, lets apply the $match filters on the fields which are not dependent on the Projection stage field data to apply sequence optimization 

{ $match: { name: "Mohit Sharma"} },
{ $addFields: {
maxSalary: { $max: "$salary" },
minSalary: { $min: "$salary" }
} },
{ $match: { maxSalary: { $lt: 90000 }, minSalary: { $gt: 20000 } } },
{ $project: {
_id: 1, name: 1, times: 1, maxSalary: 90000, minSalary: 10000,
avgSalary: { $avg: ["$maxSalary", "$minSalary"] }
} },
{ $match: { avgSalary: { $gt:45000 } } }

Multiple $ match filters are applied to filter the stage data based on Name, filter the stage data based on minSalary and maxSalary  and then $match filter which applicable on projection stage data.  

Here , each $match filter is reducing the document which are not applicable based on $match filter and thus improves the overall performance for the aggregation pipeline.

$sort + $match Sequence Optimization

If a sequence with $sort is followed by a $match, the $match moves before the $sort to minimize the No. of objects to sort

Consider the example of MongoDB Aggregation pipeline stage with $sort

{ $sort: { salary : -1 } },
{ $match: { status: 'Active' } }
After the optimization phase
{ $match: { status: 'Active' } },
{ $sort: { salary : -1 } }
$redact + $match Sequence Optimization
  • If the MongoDB aggregation pipeline has the $redact stage followed by $match stage, then it allows the aggregation move the portion of the $match stage before the $redact stage.
  • If  $match stage is added at the start of a pipeline, then aggregation can use an index or can query the collection to filter the documents in the pipeline

Consider the example of MongoDB Aggregation pipeline with below given stage

{ $redact: { $cond: { if: { $eq: [ "$Role", 5 ] }, then: "MANAGER", else: "DEVELOPER" } } },
{ $match: { department: "Finance", location: { $eq: "USA" } } }

In the optimization phase, the MongoDB optimizer transforms it to :


{ $match: { department: "Finance"} },
{ $redact: { $cond: { if: { $eq: [ "$Role", 5 ] }, then: "MANAGER", else: "DEVELOPER" } } },
{ $match: { department: "Finance", location: { $eq: "USA" } } }
$project/$unset + $skip Sequence Optimization

In this case of sequence optimization if a sequence with $project or $unset followed by $skip, then $skip moves before the $project

Consider the example of MongoDB aggregation pipeline with below given stages:

{ $sort: { salary : -1 } },
{ $project: { status: "Active" , name: 1 } },
{ $skip: 5 }

In the optimization phase, the MongoDB optimizer transforms it to :

{ $sort: { salary : -1 } },
{ $skip: 5 },
{ $project: { status: "Active" , name: 1 } }

 

Aggregation Pipeline –  Pipeline Coalescence Optimization

In this optimization scenario, the coalescence occurs after any sequence ordering optimization by placing a pipeline stage before its predecessor.


{ $sort : { age : -1 } },
{ $project : { age : 1, status : 1, name : 1 } },
{ $limit: 5 }

The optimizer calescence the $limit with $sort 

{
"$sort" : {
"sortKey" : {
"id" : -1,
},
"limit" : NumberLong(5)
}
},
{ "$project" : {
"id" : 1,
"status" : "OPEN",
"name" : 1
}
}
$sort + $limit Coalescence

Consider an scenario where a $sort precedes a $limit, then optimizer can coalesce the $limit into the $sort if no intervening stages (e.g. $unwind, $group) modify the number of documents

$limit + $limit Coalescence

Consider an scenario where a $limit is followed by $limit, then both $limit stages can coalesce into a single $limit considering the smaller amount from the two $limit

{ $limit: 5},
{ $limit: 12 }

The optimizer coalesce into a single $limit with the smaller $limit value

{ $limit: 5}
$skip + $skip Coalescence

Consider an scenario where a $skip is followed by $skip, then both $skip stages can coalesce into a single $skip 

{ $skip: 1 },
{ $skip: 7 }

The optimizer coalesce into a single $skip where the skip amount 8 is the sum of the two initial limits 1 and 7

{ $skip: 8}
$match + $match Coalescence

Consider an scenario where a $match is followed by $match, then both $match stages can coalesce into a single $match with an $and

{ $match: { course: "Data Science" } },
{ $match: { status: "Enrolled" } }

The optimizer coalesce into a single $match with an $and

{ $match: { $and: [ { course: "Data Science" }, { "status" : "Enrolled" } ] } }
$lookup + $unwind Coalescence

Consider an example where a $unwind is followed by $lookup and operates on the as field of the $lookup, then optimizer coalesce the $unwind into the $lookup stage to avoid large document creation

{
$lookup: {
from: "FacultyCollectionData",
as: "resultingArray",
localField: "StudentCourse",
foreignField: "AssignedFaculty"
}
},
{ $unwind: "$resultingArray"}

The optimizer coalesce the $unwind into the $lookup stage

{
$lookup: {
from: "FacultyCollectionData",
as: "resultingArray",
localField: "StudentCourse",
foreignField: "AssignedFaculty",
unwinding: { preserveNullAndEmptyArrays: false }
}
}
$sort + $skip + $limit Sequence

Consider a pipeline contains a sequence of $sort followed by a $skip followed by a $limit

{ $sort: { age : -1 } },
{ $skip: 10 },
{ $limit: 5 }

Then optimizer will transforms it into:

{
"$sort" : {
"sortKey" : {
"age" : -1
},
"limit" : NumberLong(15)
//MongoDB increases the $limit amount with the $skip amount when uses reordering

}
},
{
"$skip" : NumberLong(10)
}

 

 


Start Oracle Database Listener in Windows

The Oracle Database listener starts with the Restart of the machine but somehow due to error or manually stopping the listener , we need to restart the Oracle Database Listener. The blog provides the steps to restart Oracle Database Listener in Windows 10

Starting and Stopping the Oracle XE Service in Windows

  1. Run -> services.msc to open the windows services dialog
  2.  Search  for OracleServiceXE

Oracle XE Database Listener in windows

 

3.  Right -Click the OracleServiceXE and select the Option Start , Stop or Pause.

4. Right -Click the OracleServiceXE and select Properties -> Startup Type.  Select one of the below given options 

  • Automatic
  • Manual
  • Disabled from the Startup type list

Validate Oracle Database Connection after listener start 

  1. Navigate to the SQLPlus location in your Oracle Database 
<ORACLE_HOME>\product\18.0.0\dbhomeXE\bin

2. Connect SQL Plus for Database Connection 

Connect system / as sysdba 

3. Open the Pluggable Database XEPDB1 as it does not open automatically

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

4. Stop the Running Oracle Database 

connect through the Sqlplus and execute the below command

SQL> SHUTDOWN IMMEDIATE

 


Oracle XE 18c Database installation guide

The blog provides the step by step installation process for installing the Oracle XE 18c Database in your local machine. The Oracle XE 18c Database can be downloaded from the Oracle Site Link Oracle XE 18c Database Download Link

Pre-requisite for the Oracle XE 18c

  1. RAM: 4 GB  
  2. Oracle XE Memory allocation : 4 GB
  3. Swap Memory : 2 GB
  4. Temp : 2 GB

Open the Directory where Oracle XE 18c Database is downloaded and extracted.  

Execute the Setup.exe file and it opens the Oracle XE Installation Welcome Screen.

Oracle XE18c Database Installation Wizard

Accept the User Agreement for Oracle XE 18c 

Oracle XE 18c Database License Agreement

Select the Installation Directory where Oracle XE 18c Database to be installed

Oracle XE 18c Database Install Path

Enter the Password for the Oracle XE 18c Database 

Oracle XE 18c Database system password

Click Next and Oracle Database installation to be completed. 

Oracle XE 18c Database Installation completed

Click Finish to close the setup 

SQL Developer Connection with Oracle Database

The SQL Developer can be downloaded from Oracle site SQL Developer Download link

Install the SQL Developer in your machine and open it.

Go to Oracle connections , right -click -> New Connection

Enter the Oracle Database details as shown in the screen

SQL Developer Connection with Oracle XE Database

Test the Connection. 

Click Close.  You are now ready to execute oracle commands using SQL Developer.


mongodb how to delete document in a collection ?

The blog provides the steps how to delete documents from collection in mongodb. A record or document in mongodb is removed or deleted using deleteOne() , deleteMany(), remove() and findOneAndDelete() methods which we will discuss in detail with examples.

Delete Document in MongoDB

The below needs to be considered when delete a document in MongoDB

  • MongoDB allows to delete a single document in the collection
  • MongoDB allows to delete multiple documents in the collection
  • MongoDB allows to delete a document by _id
  • MongoDB allows to delete a document based on sort criteria

  MongoDB provides the below given methods for deleting a document in a collection.

S. No Delete Method Delete Method Description
1 db.collection.deleteOne() allows to delete a single document in a collection that matches the delete criteria
2 db.collection.deleteMany() allows to delete multiple document in a collection that matches the delete criteria
3 db.collection.remove() allows to delete a single document or multiple document in a collection that matches the delete criteria
4 db.collection.findOneAndDelete() allows to delete a single document in a collection that matches the delete criteria  and sort criteria

 

Method Name – deleteOne()

Syntax – db.collection.deleteOne(<DELETE_CRITERIA>)

Example: Retrieve the existing documents from the collection oracleappsUsers

> db.oracelappsUsers.find()
{ "_id" : ObjectId("5f13c9df9104ecc5b2149d60"), "name" : "Amit Trivedi", "userType" : "Admin", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f13ce54a3587b86324785d9"), "name" : "Rohit Verma", "userType" : "Admin", "description" : "MongoDB insert document Tutorial", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f13ce7ca3587b86324785da"), "name" : "Mohit Sharma", "userType" : "Admin", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }

Now, delete a single document by executing the below delete document command

> db.oracelappsUsers.deleteOne( { name: "Rohit Verma" } )
{ "acknowledged" : true, "deletedCount" : 1 }

Now, Retrieve the available documents from the collection – oracleappsUsers. It will list down 2 documents only.

> db.oracelappsUsers.find()
{ "_id" : ObjectId("5f13c9df9104ecc5b2149d60"), "name" : "Amit Trivedi", "userType" : "Admin", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f13ce7ca3587b86324785da"), "name" : "Mohit Sharma", "userType" : "Admin", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }

Method Name – deleteMany()

Syntax – db.collection.deleteMany(<DELETE_CRITERIA>)

Example: Retrieve the existing documents from the collection oracleappsUsers

> db.oracelappsUsers.find().pretty()
{
"_id" : ObjectId("5f12fd48d4a587e31cddd7e5"),
"name" : "Mohit Sharma",
"userType" : "Administrator",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}
{
"_id" : ObjectId("5f130f1c3a96070d59afd2c0"),
"name" : "Rohit Verma",
"userType" : "Administrator",
"description" : "MongoDB insert document Tutorial",
"createdBy" : "oracelappshelp.com"
}

Now, delete all the documents from the collection where userType =’Admin’ which results into deletion of multiple documents

> db.oracelappsUsers.deleteMany( { userType: 'Administrator' } )
{ "acknowledged" : true, "deletedCount" : 2 }

Now execute the find() to get the document details. There should not be any document available in the collection.

> db.oracelappsUsers.find().pretty()

Method Name – remove()

Syntax – db.collection.remove(<delete_criteria>, justOne)

where

delete_criteria – is the deletion condition that need to be applied to the document

justOne – is the Boolean value when enabled, allows to delete only 1 document based on delete criteria

The remove() allows to delete a single document or multiple document that matches the delete criteria. The remove() returns an object with the status of the operation

Example: Add New records. Retrieve the existing documents from the collection oracleappsUsers

> db.oracelappsUsers.find()
{ "_id" : ObjectId("5f1546f5b5a69e08eec889b5"), "name" : "Mohit Sharma", "userType" : "Administrator", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f15470db5a69e08eec889b6"), "name" : "Ashish Goyal", "userType" : "Subscriber", "description" : "MongoDB insert document Tutorial", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f154722b5a69e08eec889b7"), "name" : "Praveen Gupta", "userType" : "Subscriber", "description" : "MongoDB insert document Tutorial", "createdBy" : "oracelappshelp.com" }

Now execute the remove() to remove the user with name -“Praveen Gupta”

> db.oracelappsUsers.remove({"name":"Praveen Gupta"})
WriteResult({ "nRemoved" : 1 })

Execute the find() to retrieve the available documents from the collection

> db.oracelappsUsers.find()
{ "_id" : ObjectId("5f1546f5b5a69e08eec889b5"), "name" : "Mohit Sharma", "userType" : "Administrator", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f15470db5a69e08eec889b6"), "name" : "Ashish Goyal", "userType" : "Subscriber", "description" : "MongoDB insert document Tutorial", "createdBy" : "oracelappshelp.com" }

If we need to remove all the documents from the collection then we can execute the below given command. This method execution is similar to TRUNCATE Table SQL in RDBMS where all records will be removed.

> db.oracelappsUsers.remove({})

Method Name – findOneAndDelete()

Syntax – db.collection.findOneAndDelete(<delete_criteria>, justOne)

The findOneAndDelete() allows to delete the document based on the delete criteria and sort criteria and returns the deleted document

db.collection.findOneAndDelete(
   <filter>,
   {
     projection: <document>,
     sort: <document>,
     maxTimeMS: <number>,
     collation: <document>
   }
)
Parameter Parameter Type Parameter Description
filter Document depicts the delete criteria
projection Document depicts the sub set of fields to return
sort Document specifies the sorting order matched as per the filter.
maxTimeMS Number specifies the time for the operation to complete
collation Document allows to specify language-specific rules for string comparison

How to update mongodb document in a collection ?

The tutorial provides the steps to update the document in MongoDB. A record or document is updated in mogodb using update() , updateOne(), updateMany(), replaceOne() which we will discuss in details with examples

Update Document in MongoDB

The below needs to be considered when updating a document in MongoDB

  • MongoDB allows to update a single document in the collection
  • MongoDB allows to update multiple documents in the collection
  • MongoDB allows to replace a single document in the collection
S. No MongoDB Update Methods Update Method Description
1 db.collection.update() The method updates a single document or multiple document which matches the given update criteria
2 db.collection.updateOne() The method updates a single document which matches the given update criteria
3 db.collection.updateMany() The method updates multiple document or multiple document which matches the given update criteria
4 db.collection.replaceOne() The method replaces a single document which matches the given update criteria
5 db.collection.findOneAndReplace() The method replaces a single document which matches the given update criteria
6 db.collection.findOneAndUpdate() The method updates a single document which matches the given update and sorting criteria
7 db.collection.findAndModify() The method modifies and return the single document. This method is useful when need to return the document with the modifications on the update. By default return document does not provide any details on modification being done.
8 db.collection.save() This method allows to insert new document or update the existing document.
9 db.collection.bulkWrite() This method includes Array parameter to allow multiple write operation with the order of execution.

Method Name: update()

Syntax: db.collection.update(<Update_Criteria>,<update>)

The db.collection.update() methods allows to update a single document or multiple documents in a collection.

Example: Retrieve the existing record from the collection – oracleappsUsers

> db.oracelappsUsers.findOne({name: "Mohit Sharma"})
{
"_id" : ObjectId("5f12fd48d4a587e31cddd7e5"),
"name" : "Mohit Sharma",
"userType" : "Admin",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Now update the userType from “Admin” to “Administrator”

> db.oracelappsUsers.update({'userType':'Admin'},{$set:{'userType':'Administrator'}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

Now Retrieve the same record and userType should be updated to ‘Administrator’

db.oracelappsUsers.findOne({name: "Mohit Sharma"})
{
"_id" : ObjectId("5f12fd48d4a587e31cddd7e5"),
"name" : "Mohit Sharma",
"userType" : "Administrator",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Method Name: updateOne()

Syntax: db.collection.updateOne(<Update_Criteria>,<update>, <options>)

The updateOne() allows MongoDB to update a single document in a collection.

Example: Create a new record in collection – oracleappsUsers

> db.oracelappsUsers.insert({
… name: "Amit Srivastava",
… userType: "subscriber",
… description: "MongoDB insert document sample",
… createdBy: "oracelappshelp.com"
… })
WriteResult({ "nInserted" : 1 })

Now Retrieve the inserted document using findOne( )

> db.oracelappsUsers.findOne({name: "Amit Srivastava"})
{
"_id" : ObjectId("5f13c9df9104ecc5b2149d60"),
"name" : "Amit Srivastava",
"userType" : "subscriber",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Now , update the record name from ‘Amit Srivastava’  to ‘Amit Trivedi’ using updateOne()

> db.oracelappsUsers.updateOne(
… {name: 'Amit Srivastava'},
… { $set: { name: 'Amit Trivedi'}}
… )
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

Retrieve the updated document result

db.oracelappsUsers.findOne({name: "Amit Trivedi"})
{
"_id" : ObjectId("5f13c9df9104ecc5b2149d60"),
"name" : "Amit Trivedi",
"userType" : "subscriber",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Method Name: updateMany()

Syntax: db.collection.updateMany(<Update_Criteria>,<update>, <options>)

The method updateMany() allows MongoDB to update multiple documents in a collection.

Example: Retrieve the list of document in a collection using find()

> db.oracelappsUsers.find().pretty()
{
"_id" : ObjectId("5f13c9df9104ecc5b2149d60"),
"name" : "Amit Trivedi",
"userType" : "subscriber",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}
{
"_id" : ObjectId("5f13ce54a3587b86324785d9"),
"name" : "Rohit Verma",
"userType" : "subscriber",
"description" : "MongoDB insert document Tutorial",
"createdBy" : "oracelappshelp.com"
}
{
"_id" : ObjectId("5f13ce7ca3587b86324785da"),
"name" : "Mohit Sharma",
"userType" : "subscriber",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Now Update the userType from ‘Subscriber’ to ‘Admin’ using updateMany()

> db.oracelappsUsers.updateMany(
… {userType: 'subscriber'},
… { $set: { userType: 'Admin'}}
… )
{ "acknowledged" : true, "matchedCount" : 3, "modifiedCount" : 3 }

Retrieve the updated documents from the collection

> db.oracelappsUsers.find().pretty()
{
"_id" : ObjectId("5f13c9df9104ecc5b2149d60"),
"name" : "Amit Trivedi",
"userType" : "Admin",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}
{
"_id" : ObjectId("5f13ce54a3587b86324785d9"),
"name" : "Rohit Verma",
"userType" : "Admin",
"description" : "MongoDB insert document Tutorial",
"createdBy" : "oracelappshelp.com"
}
{
"_id" : ObjectId("5f13ce7ca3587b86324785da"),
"name" : "Mohit Sharma",
"userType" : "Admin",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Method Name: findOneAndReplace()

Syntax:

db.collection.findOneAndReplace(
   <filter>,
   <replacement>,
   {
     projection: <document>,
     sort: <document>,
     maxTimeMS: <number>,
     upsert: <boolean>,
     returnNewDocument: <boolean>,
     collation: <document>
   }
)
Parameter Parameter Type Parameter Description
filter Document depicts the update criteria
replacement Document depicts the document to be replaced but cannot contain Update Operators
projection Document depicts the sub set of fields to return
sort Document specifies the sorting order matched as per the filter.
maxTimeMS Number specifies the time for the operation to complete
upsert Boolean

Default value is False. Replaces the document with the matched filter and returns returnNewDocument  as true .

If does not match replacement, then insert as new document and returns Null.

returnNewDocument Boolean returns the replacement document instead of the original document
collation Document allows to specify language-specific rules for string comparison

Method Name – save()

This method allows to insert new document or update the existing document. This method is deprecated in MongoDB.

Syntax

> db.collection.save(
  <document>,
  {
    writeConcern:<document>
  }
)

Example:

> db.orders.save( { item: "Laptop", qty: 10 , price:4000, status:"Processed" } )

Update Operators in MongoDB

The below given are the update operators in MongoDB.

S. No  Operator Name Operator Description
1 $currentDate set the value of a field to current date as or TimeStamp
2 $inc increments the value
3 $min updates the document when provided value is less than the existing field value
4 $max updates the document when provided value is greater than the existing field value
5 $mul allows to multiply the field value
6 $rename renames the field in a document
7 $set set the value of a field in a document
8 $setOnInsert set the value of a field for the new document
9 $unset removes the value of a field in a document

 


How to query mongodb Document in a collection ?

The tutorial provides the different methods available to Query document in MongoDB collection. A document can be read or queried in mongodb using find() , findOne(), pretty() methods which we will discuss in detail with examples.

Query document methods in MongoDB

Method Name – find() :

Syntax: > db.collectionName.find()

The find() method is the basic method to query the document in a collection. This method lists down all the documents available in a collection.

Insert the document in the Collection – oracleappsUsers

> db.oracelappsUsers.insert({
… name: "Rohit Verma",
… userType: "Subscriber",
… description: "MongoDB insert document Tutorial",
… createdBy: "oracelappshelp.com"
… })
WriteResult({ "nInserted" : 1 })

Query the document with find () in the collection – oracleappsUsers. It will list down all the available documents.

> db.oracelappsUsers.find()
{ "_id" : ObjectId("5f12fd48d4a587e31cddd7e5"), "name" : "Mohit Sharma", "userType" : "Admin", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }
{ "_id" : ObjectId("5f130f1c3a96070d59afd2c0"), "name" : "Rohit Verma", "userType" : "Subscriber", "description" : "MongoDB insert document Tutorial", "createdBy" : "oracelappshelp.com" }

Method Name – pretty

Syntax – >db.collectionName.find().pretty()

The pretty() method is allowed to format the Query document result. The below given is the example for find().pretty()

> db.oracelappsUsers.find().pretty()
{
"_id" : ObjectId("5f12fd48d4a587e31cddd7e5"),
"name" : "Mohit Sharma",
"userType" : "Admin",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}
{
"_id" : ObjectId("5f130f1c3a96070d59afd2c0"),
"name" : "Rohit Verma",
"userType" : "Subscriber",
"description" : "MongoDB insert document Tutorial",
"createdBy" : "oracelappshelp.com"
}

Method Name – findOne()

Syntax – >db.collectionName.findOne(<search_attribute>)

The findOne() method in MongoDB allows to query a single document by providing the document attribute details. The below given example depicts the usage of findOne() to query document in a collection.

> db.oracelappsUsers.findOne({name: "Mohit Sharma"})
{
"_id" : ObjectId("5f12fd48d4a587e31cddd7e5"),
"name" : "Mohit Sharma",
"userType" : "Admin",
"description" : "MongoDB insert document sample",
"createdBy" : "oracelappshelp.com"
}

Query Operators in MongoDB

RDBMS provides various operators to retrieve the data based on specified conditions like Where , <= , >= , AND , OR

MongoDB provides the RDBMS equivalent operators to query the document in a collection.

S. No RDBMS Search Data Query MongoDB Query Document
1 SELECT * from Orders db.orders.find( {} )
2 SELECT * FROM orders WHERE status = “COMPLETED” db.orders.find( { status: “COMPLETED” } )
3 SELECT * FROM orders WHERE status in (“INPROGRESS”, “PENDING”) db.orders.find( { status: { $in: [ “INPROGRESS”, “PENDING” ] } } )
4 SELECT * FROM orders WHERE status = “PENDING” AND price < 2000 db.orders.find( { status: “PENDING”, price: { $lt: 2000 } } )
5 SELECT * FROM orders WHERE status = “PENDING” OR price < 2000 db.orders.find( { $or: [ { status: “PENDING” }, { price: { $lt: 2000 } } ] } )
6 SELECT * FROM orders WHERE status = “PENDING” AND ( price < 3000 OR item LIKE “Laptop%”) db.orders.find( {
status: “PENDING”,
$or: [ { price: { $lt: 3000 } }, { item: /^Laptop/ } ]
} )

How to Insert Document in mongodb collection ?

The tutorial provides the steps to insert document using MongoDB. A document is inserted in mongodb using insert() , insertOne() , insertMany() methods which we will discuss in detail with examples.

Insert Document in MongoDB

MongoDB stores the data into documents. The below points can be considered for the Insert Document in MongoDB

  • MongoDB allows to insert a single document in a collection
  • MongoDB allows to insert multiple documents in a collection
  • If the collection does not exist, MongoDB will automatically creates the collection
  • Each document in a collection requires a unique field (_id) which represents as the Primary Key for the document. MongoDB generates the ObjectId automatically if the _Id is not provided during the Insert Document
  • MongoDB ensures atomicity for the write operations

What is objectId in MongoDB ?

An ObjectId is :

  • An unique key or id
  • it is faster to generate
  • it has 12 bytes length ( 4 byte – timestamp for objectId creation, 5 byte – random value , 3 byte – incrementing counter)
  • ObjectId creation can be accessed by ObjectId.getTimestamp() method

The Insert Document Methods in MongoDB

Option 1: Insert Document 

Syntax –  db.collectionname.insert(document)

This command is the basic command to insert document where collectionname is the name of the collection, insert is the CRUD Operation for inserting single or multiple document. The below given is the sample for inserting document

> db.oracelappsUsers.insert({
… name: "Mohit Sharma",
… userType: "Admin",
… description: "MongoDB insert document sample",
… createdBy: "oracelappshelp.com"
… })
WriteResult({ "nInserted" : 1 })
>

The inserted record can be validated by the below find command. Although we had not provided the _Id  ( unique value) for the inserted document, but MongoDB automatically creates the _id as given below.

>db.oracelappsUsers.find( { name: "Mohit Sharma" } )
{ "_id" : ObjectId("5f12fd48d4a587e31cddd7e5"), "name" : "Mohit Sharma", "userType" : "Admin", "description" : "MongoDB insert document sample", "createdBy" : "oracelappshelp.com" }

Option 2: Insert Single Document

Syntax – db.collection.insertOne()

This command allows to insert the single document in the collection. The below example illustrates the same.

> try {
… db.oracleappshelpproducts.insertOne( { item: "MongoDBMockTest", qty: 1 } );
… } catch (e) {
… print (e);
… };

MongoDB executes the insertOne() for the single document and returns the acknowledgment with the generated objectId (_Id) value.

{
     "acknowledged" : true,
     "insertedId" : ObjectId("5f1302733a96070d59afd2bf")
}
>

Another example for insertOne() where _id is being provided. MongoDB considers the same _id and returns it back with the acknowledgement for the inserted document.

>try {
… db.oracleappshelpproducts.insertOne( { _id: 21, item: "MongoDBTutorialPDF", qty: 1 } );
… } catch (e) {
… print (e);
… };
{ "acknowledged" : true, "insertedId" : 21 }

Option 2: Insert Multiple Document

Syntax – db.collection.insertMany()

This command allows to insert the multiple document in the collection. The below example illustrates the same.

> try {
… db.oracleappshelpproducts.insertMany(
… [
… { _id: 22, item: "SQLServerTutorialPDF", qty: 1 } ,
… { _id: 23, item: "CouchDBTutorialPDF", qty: 1 },
… { _id: 24, item: "CassandraTutorialPDF", qty: 1 }
… ]);
… } catch (e) {
… print (e);
… };

The below given is the return output by MongoDB for the Bulk Insertion of multiple documents.

{ "acknowledged" : true, "insertedIds" : [ 22, 23, 24 ] }
>

Insert Document Error for Duplicate Key in collection

In case the _Id is repeated for the inserted document , MongoDB returns the Duplicate Key error in the collection as given below.

> try {
db.oracleappshelpproducts.insertMany(
[
{ _id: 21, item: "SQLServerTutorialPDF", qty: 1 } ,
{ _id: 21, item: "CouchDBTutorialPDF", qty: 1 },
{ _id: 21, item: "CassandraTutorialPDF", qty: 1 }
]);
} catch (e) {
print (e);
};

>BulkWriteError({
"writeErrors" : [
{
"index" : 0,
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.oracleappshelpproducts index: id dup key: { _id: 21.0 }",
"op" : {
"_id" : 21,
"item" : "SQLServerTutorialPDF",
"qty" : 1
}
}
],
"writeConcernErrors" : [ ],
"nInserted" : 0,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
>

How to Create & Drop collections in MongoDB

The tutorial provides the steps to create a collection and drop a collection using MongoDB. A collection is created in mongodb using createCollection()

Document: A document is single unit or data record similar to a row in RDBMS for storing information. A document is a JOSN object storing data in the form of key-value pairs.

Collection:  A collection is a group of documents for storing data similar to RDBMS tables. MongoDB contains multiple collections in the database similar to RDBMS which contains multiple tables in the database

Create Collection in MongoDB

The MongoDB provides the method – db.createCollection(name, options) to create a collection.

Create Collection Syntax:

db.createCollection(name, options)

Where

name is the collection name

options is the document to specify collection configuration

Argument Argument Type Argument Description
Name String Name of the Collection
Options Document Optional. Configuration details like Memory Size, Indexing
capped Boolean

Optional.  Enables the Capped Collection which are fixed size collections.  When reached max size (in bytes) , capped collection overwrites the old entries

autoIndexId Boolean

Optional.

When enabled , creates index on _id field.

size Number

Optional.

Used in Capped Collection . When enabled, specify max size in bytes

max Number

Optional.

When Enabled, allows to specify max documents in the Capped Collection.

Lets execute a simple collection method call for creating the collection in MongoDB

> use oracleappshelpDB
switched to db oracleappshelpDB
> db.createCollection("oracleappsHelpBCollection")
{ "ok" : 1 }

The OK depicts that the collection – “oracleappsHelpBCollection” is created. We can verify by executing the show collections 

> show collections
oracleappsHelpBCollection
>

Let’s execute create collection method with the Optional arguments

> db.createCollection("oracleappscol", { capped : true, size : 10240, max : 1000 } )
{
"ok" : 0,
"errmsg" : "16: Resource device",
"code" : 8,
"codeName" : "UnknownError"
}
>

Note that we do not need to create collections in MongoDB as it automatically creates the collection when an document is inserted.

The below MongoDB Insert Document command shows the created collections.

> db.oracleappshelpDB.insert({"studentName" : "Mohit Sharma"})
WriteResult({ "nInserted" : 1 })
> show collections
oracleappsHelpBCollection
oracleappshelpDB
>

Drop collection in MongoDB

The MongoDB provides the method – db.collection.drop() to drop a collection.

The MongoDB provides the method – db.collection.drop(name, options) for dropping the collections.

Drop Collection Syntax:

db.Collection.drop()

Where

Collection is the name of the collection.

The below command show collections list down the available collections for the Database – oracleappshelpDB

> show collections
oracleappsHelpBCollection
oracleappshelpDB
>

Now , drop the collection – oracleappshelpDB from the database by executing the below command

> db.oracleappshelpDB.drop()
true
>

MongoDB Database operations- Create Database, Drop Database

The tutorial provides the steps and mongoDB shell command execution to create database in MongoDB, drop database in MongoDB , create and drop statements in MongoDB with examples.

Follow the steps provided in Install MongoDB on Windows Server, Start MongoDB using MongoDB Shell.

Create Database Statement in MongoDB

The MongoDB provides the below given command to create the database. The command will create the New Database if doesn’t exist , else it returns the existing database. MongoDB uses default database as test for storing the documents if no database is created.

Syntax: use DATABASE_NAME

use oracleappshelpDB
switched to db oracleappshelpDB

Command to select the created database oracleappshelpDB

db
oracleappshelpDB

Command to get the list of all database in the MongoDB

show dbs
admin   0.000GB
config  0.000GB
local   0.000GB

The created database oracleappshelpDB is not listed as we need to have atleast 1 document to be inserted

db.movie.insert({"name":"oracleappshelp.com MongoDB Create Database Tutorial"})
WriteResult({ "nInserted" : 1 })
> show dbs
admin             0.000GB
config            0.000GB
local             0.000GB
oracleappshelpDB  0.000GB

Drop Database statement in MongoDB

The below given command can be used for dropping the database from MongoDB

db.dropDatabase() 

As we are using oracleappshelpDB and inserted the document , thus current database in selection is oracleappshelpDB . If we execute the command db.dropDatabase() it will remove the database. In case of no specified database, the command will delete the default database “test”.

Execute the show dbs and use oracleappshelpDB command to switch to the required database which needs to be dropped.

show dbs
admin             0.000GB
config            0.000GB
local             0.000GB
oracleappshelpDB  0.000GB
> use oracleappshelpDB
switched to db oracleappshelpDB

Now, execute db.dropDatabase() command and then show dbs to ensure database is not shown now

> db.dropDatabase()
{ "dropped" : "oracleappshelpDB", "ok" : 1 }
> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB