Skip to main content

Aggregation Pipeline Operators in MongoDB

1- Pipeline Aggregation Stages

To retrieve data according to user MongoDB provide aggregate operators, using this operators we can reshape array, sort the records, filter the records etc. 

Pipeline stages appear in an array. Documents pass through the stages in sequence.

db.collection.aggregate( [ { <stage> }, ... ] )

$project: This operator use to reshape the each document for the next pipeline such as adding new field removing existing field. Continue...

$match: This operator use to filters the documents stream. If specific condition(s) match then it will allow to document to pass next pipeline.  Continue...

$redact: This operator use to restrict content of document based on information stored in the documents themselves(by MongoDB) you may say to change and give new form of each document in the stream by restricting the content for each document based on information stored in the documents themselves.  Continue...

$limit: This operator use to pass n documents to next pipe line where n is the specified limit. Continue...

$skip: This operator use to skip first to n documents and remaining will be passed in next pipeline. n= skip number.  Continue...

$unwind: De-constructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.  More detail with example.

$group: This operator is use to groups the documents by some specified expression and group the documents for each distinct grouping. According to MongoDB doc "Groups input documents by a specified identifier expression and applies the accumulator expression(s), if specified, to each group. Consumes all input documents and outputs one document per each distinct group. The output documents only contain the identifier field and, if specified, accumulated fields" Continue..

$sort: This operator use to sort on specified key it change only order without modify the document. Continue...

$geoNear: This operator use to retrieve data based on proximity to a geospatial point. The output documents include an additional distance field and can include a location identifier field. More detail with example.

$out: This operator use to write the resulting document of the aggregation pipeline to a specified collection. The $out operator must be the last stage in the pipeline. The $out operator lets the aggregation framework return result sets of any size. Continue...

$lookup: This is new feature of MongoDB version 3.2

$lookup perform the left outer joint between two collections in same database. For the each input document $lookup add a new array fields whose element matches from joined collection. Continue...

2-Boolean Aggregation Operators

$and, $or and $not operator is known as Boolean aggregate operator in mongodb.

$and: This operator returns the value if it’s all expression evaluate true. Its take any number of expression. It is same like AND operator in SQL Server. Continue...

$or: This operator return the value if one expression true from it’s all expression. Its takes any number of expression. $or operator same as like OR operator in SQL Server. Continue...

$not: This operator returns the Boolean value. $not return the Boolean value to opposite of its expression. Continue...

3-Set Operator Aggregate 

Guys if you are aware with SET theory in mathematics then you can understand easily about Set Operator Aggregation. Set operators assume to array like sets and perform the set operation on arrays. Operation returns a unique entry sets after filter all the duplicates. The order of the elements in the output array is unspecified. If the set contains a nested array element, the expression evaluates it on top level.

Name and description of set operators

$setEquals:  Its returns true or false, if the input sets (array) have same distinct element returns true if not then false. $setEquals accept two or more expression. Continue...

$setIntersection: Its returns common elements appear in all input sets and accept any number of documents.

$setUnion: Its returns the all element set appears in input sets and accept any number of expression. Continue...

$setDifference: It’s accept only two argument expression and returns the set of element who appears in first not in second. Continue...

$setIsSubset: Its returns true or false, $setIsSubset accepts only two argument expression only and returns true if all elements of first expression belong to second expression. Continue...

$anyElementTrue: Its returns true or false $anyElementTrue accepts only one argument expression and returns true if evaluate expression true otherwise false. Continue...

$allElementsTrue:  Its returns true or false, $allElementsTrue accepts only one argument expression and returns true if no element of a set evaluate to false. Continue...

Popular posts from this blog

What is difference between UNION and UNION ALL in SQL Server

We use UNION and UNION ALL operator to combine multiple results set into one result set.
UNION operator is used to combining multiple results set into one result set but removes any duplicate rows. Basically, UNION is used to performing a DISTINCT operation across all columns in the result set. UNION operator has the extra overhead of removing duplicate rows and sorting result.
UNION ALL operator use to combine multiple results set into one result set but it does not remove any duplicate result. Actually, this does not remove duplicate rows so it is faster than the UNION operator. If you want to combine multiple results and without duplicate records then use UNION otherwise UNION ALL is better.
Following some rules for using UNION/UNION ALL operator
1.The number of the column should be the same in the query's when you want to combine them. 2.The column should be of the same data type. 3.ORDER BY clause can be applied to the overall result set not within each result set.
4.Column name of …

Dropdownlist selectedindexchanged event is not firing

<asp:DropDownList ID="ddlSource" runat="server" DataSourceID="SqlDataSource1" DataTextField="vcSuplierNm" ViewStateMode="Enabled DataValueField="vcSuplierCode" EnableViewState="true" AppendDataBoundItems="true" OnSelectedIndexChanged="ddl_OnSelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>

Add property ViewStateMode="Enabled" and EnableViewState="true"
in drop DropDownList

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])


{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…

$group (aggregation) in MongoDB

IF we want to group document by specific expression and want to output for each distinct grouping of document here we have to use $group. IF you familiar with Relational Database like SQL Server, It's work same like GROUP BY clause.
Output document contains the _id field which contains the distinct group by key also output document contains computed fields which grasp the value of some accumulator expression grouped by the _id.

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... }

Accumulator Operator:
$num : It's returns sum of the numeric value. It can be use in $project also in mongodb 3.2 version.
$avg  : It's returns the average of numeric values. It can be use in $project also in mongodb 3.2 version.
$first : It's returns a value from the first document for each group.
$last : It's returns a value from the first document for each group.
$max : It's returns the highest expression value for each group.…

Add a column, with a default value, to an existing table in SQL Server

You can do it by edit of table design, if you want do it with query see following.
Note: Use WITH VALUES to update existing null-able rows.
Note: WITH VALUES handles the NOT NULL part

Merge and Merge join transformation in SSIS