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

Query to find stored procedures by nested stored procedure name

Problem: Suppose we have a stored procedure which has been used in several stored procedure, I mean stored procedure usp_proc1 is nested in many stored procedures like below
BEGIN DECLARE@ResultTABLE ( IDINT, NAMEVARCHAR(50), [ADDRESS]VARCHAR(255) ) INSERTINTO@Result EXECusp_proc1@Name='codefari' END
So I want to find all those queries who containing usp_proc1
Solution: There is a lot of solutions, I'm giving some of them below. If you want to get the only name of the stored procedures then use the following query. Using join query on system tables syscomments and sysobjects we can get the stored procedures name which containing the particular table, nested procs or any other string.
SELECTDISTINCTo.name FROMsyscommentss INNERJOINsysobjectsoONs.id=o.id

Check for changes to an SQL Server table?

Problem: Suppose your team is working on the under-development project so it might be possible continuous work on the database and perform changes in Table, Stored procedure as per requirement, and daily you have to update the testing server database as per changes are done in developing server database then how it is possible to trace those changes. There are a lot of solutions for this problem which is listed below Solution 1: For SQL Server 2000, 2005 and above use the CHECKSUM command SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMYour_Table_NameWITH (NOLOCK); That will return the same number each time its run as long as the table contents haven't changed. Unfortunately CHECKSUM does not work always properly to detect changes. It is only a primitive checksum and no CRC calculation. Therefore you can't use it to detect all changes, e. g. symmetrical changes result in the same CHECKSUM! Solution 2: 1.Run the following query. Before executing query replace DB_Name with your database name…

Merge and Merge join transformation in SSIS

MERGE TRANSFORMATION
Using Merge Transformation we can combine two sorted data-set into single data-set basically Merge Transformation used to combines rows from two sorted data flows into one sorted data flow. Following tasks you may perform using Merge Transformation: 1.Suppose we have a scenario like, we need to merge data from a database table and excel means we want to merge data from two different data sources. For such type of scenario, you can use Merge Transformation. 2.If we want to merge data from two same structured tables but exists two different servers. 3.Sometimes we get an error due to data in a row, after correcting errors in the data we can re-merge rows easily. See below explanations may help you to understand Merge Transformation: I do evaluate here, you already know about the data source, data conversion, data flow, task flow, control flow etc. Note:Before Merge transformation, we need to sort the data using Sort Transformation. After sorting data add data path to Merge…