26MongoDBAggregationFramework

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

What is an aggregation framework ?

The original query language implemented as find() method has a very limited expressiveness. It can only be used to implement a subset of queries that restrict a single collection of BSON documents. More complex queries need cursor programming and application of a general purpose algorithmic programming language. Aggregation framework supposed to include and extend a query language implemented as find() method.

Aggregation framework is a query language that that can be used to transform and to combine data from multiple documents in order to generate new information not available in any single document.

Aggregation framework makes a task of database search much easier and more efficient through specification of a series of operations in an array and processing it in a single call.

Aggregation framework defines an aggregation pipeline. A pipeline is a sequence of operations organized such that the outputs from one operation in a sequence become the inputs to the next operation in a sequence. It is possible to record the intermediate result of processing and later on access use the intermediate results as the inputs to many operations. it is also possible to save the results of many operations in the same collection of BSON documents. In a general case an aggregation frame work can be a Directed Acyclic Graph (DAG) where the nodes represent the operations and the edges represent the data flows.

Aggregation framework ? What is it ?

Aggregation framework is a query language that that can be used to transform and to combine data from multiple documents in order to generate new information not available in any single document
Aggregation framework makes a task of database search much easier and more efficient through specification of a series of operators in an array and processing it in a single call
Aggregation framework defines an aggregation pipeline where the output from each step in the pipeline provides input to the next step

Every step in a pipeline implements a single operation on a collection of documents. Such operation transforms each document included in input collection into a document inserted into an output collection.

A pipeline of operations pass a stream of documents in a collection through a number of operations like filtering, projecting, grouping, sorting, limiting, skipping, and the others.

The same operations can be repeated many times in a pipeline in any order.

Conceptually, aggregation framework in MongoDB is similar to SELECT statements WITH clause in SQL where the intermediate queries create temporary results later on sue by the other intermediate queries until the final result is created.

Some of the aggregation operators that can be used in an aggregation pipeline in MongoDB are similar to SQL SELECT, WHERE, GROUP BY, HAVING, ORDER BY, and JOIN clauses.

Pipelined data processing is on of two basic way how data processing can be parallelised. The other way is parallel processing of partitioned data sets.

Aggregation framework ? What is it ?

Every step in a pipeline executes a single operators on the input documents to transform the input and to generate output document
A pipeline processes a stream of documents through several operators like filtering, projecting, grouping, sorting, limiting, skipping, and the others
The same operators can be repeated many times in a pipeline in any order
Aggregation framework in MongoDB is similar to SQL WITH clause of SELECT statement
Some of the aggregation operators that can be used in an aggregation pipeline in MongoDB are similar to SQL SELECT, WHERE, GROUP BY, HAVING, ORDER BY, and JOIN clauses
Pipelined data processing is on of two basic way how data processing can be parallelised
The other way is partitioned data processing

Aggregation framework

Some of the operators that can be used in an aggregation pipeline:
- $project: Extracts the components of a documents to be placed in an output document (similar to SELECT clause)
- $match: Filters the documents to be processed, similar to find() (and similar to WHERE clause)
- $limit and $skip: Limits and skips the documents to be passed to the next operation
- $unwind: Expands (unnest) an array, generating one output document for each array entry
- $group: Groups documents by a specified key
- $sort and $count: Sorts and counts the documents
- $out: Saves the results from a pipeline to a collection
- $lookup: Joins two collections of documents
- $merge: Merges two collections of documents

MongoDB Aggregation Framework

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

A sample database

A conceptual schema of a database with information about suppliers, products, customers, orders, and details of orders

A sample database

A sample collection orders

A sample database

A sample document, that belongs to a class CUSTOMER

{
        "_id":"ALFKI",
        "CUSTOMER":{
                 "customer code":"ALFKI",
                 "company name":"Alfreds Futterkiste",
                 "contact name":"Maria Anders",
                 "contact title":"Sales Representative",
                 "address":"Obere Str. 57",
                 "city":"Berlin",
                 "region":null,
                 "postal code":"12209",
                 "country":"Germany",
                 "phone":"030-0074321",
                 "fax":"030-0076545",
                 "submits":[ ]
        }
}

A sample database

A sample nested document, that belongs to a class CUSTOMER

{
 "_id":"FAMIA",
 "CUSTOMER":{
               "customer code":"FAMIA",
                ...  ...  ...  ...  ...  ...
               "submits":[
                            {
                             "ORDER":{
                                        "order id":328,
                                         ...  ...  ...  ...  ...
                                        "consists of":[
                                                         {
                                                          "ORDER DETAIL":{
                                                                            "product name":"Louisiana Fiery Hot Pepper Sauce",
                                                                            ...  ...  ...  ...  ...
                                                                           }
                                                         },
                                                         {
                                                          "ORDER DETAIL":{
                                                                            "product name":"Raclette Courdavault",
	                                                                    ...  ...  ...  ...  ...
                                                                           }
                                                         }
                                                        ]
                                       }
                            }
	                   ] } }

The present slide shows a sample BSON document that implements an instance of object in a class SUPPLIER. A key "_id" is associated with value "Karkki Oy". The same value is associated with a key "company name". A property "company name" is an identifier of a class SUPPLIER.

A key "SUPPLIER" is associated with a sub-document that contains a description of an instance of object in a class SUPPLIER. A key "supplies" is associated with an array that contains the descriptions of products supplied by a supplier. A product is represented by a sub-document associated with a key "PRODUCT".

A value associated with a key "product name" uniquely identifies each product. Such identification can be later on enforced by a unique index created over the values associated with a key "product name".

Note that a value associated with a key "product name" is used in a BSON object given on the previous slide s link to a product description sub-document nested with SUPPLIER sub-document.

A sample database

A sample nested document, that belongs to a class SUPPLIER

{
 "_id":"Karkki Oy",
 "SUPPLIER":{
               "company name":"Karkki Oy",
               "contact name":"Anne Heikkonen",
               "contact title":"Product Manager",
               "address":"Valtakatu 12",
               ...  ...  ...  ...  ...  ...
               "supplies":[
                             {
                              "PRODUCT":{
                                           "product name":"Maxilaku",
                                           "category name":"Confections",
                                            ...  ...  ...  ...  ... 
                                          }
                             },
                             {
                              "PRODUCT":{
                                           "product name":"Valkoinen suklaa",
                                           "category name":"Confections",
                                           ...  ...  ...  ...  ...
                                          }
                             }

                            ]
              }
}

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

The first operator on a list is $project operator. The operator generalizes a projection implemented through the second argument of find() method.

An operator $project extracts the components of subdocuments, renames the components, and performs the operations on components.

$project operator implements such projection in the same way the second argument of find() method.

db.orders.aggregate([ {$project:{"CUSTOMER.company name":1,"_id":0}} ])

The sample results are listed below.

{ "CUSTOMER" : { "company name" : "Alfreds Futterkiste" } }

{ "CUSTOMER" : { "company name" : "Ana Trujillo Emparedados y helados" } }

{ "CUSTOMER" : { "company name" : "Antonio Moreno Taquería" } }

... ... ...

Please note that a sample projection explained above preserves the nested sub-documents. It means that the outcomes from projection still include "CUSTOMER" key associated with the values being key-value pairs. For example { "company name" : "Alfreds Futterkiste" }. We might expect to get from $project operator only unnested key-value pairs. Good news is that $project operator implements an extended functionality unnesting the projected results.

The next example shows an extended the functionality of $project operator with the transformations of projected data.

In the next example we select company name of each customer and skip document identifier and remove the nestings.

db.orders.aggregate([ {$project:{"Company":"$CUSTOMER.company name","_id":0}} ])

The sample results are listed below.

{ "Company" : "Alfreds Futterkiste" }

{ "Company" : "Ana Trujillo Emparedados y helados" }

{ "Company" : "Antonio Moreno Taquería" }

... ... ...

Please note $ sign in front of a path "$CUSTOMER.company name". $ sign in front of a path means that a value associate with a a key at the end of path replaces a path in the results. In the present case a value associated with a key "company name" at the end of a path "$CUSTOMER.company name" replaces a path in a key-value pair {$project:{"Company":"$CUSTOMER.company name"}}. The outcomes consist of key-vale pairs like { "Company" : "Company name" }.

Taking a value associated with key at the end of a path preceded with $ sign unnests a document and removes the higher levels of nesting from the results. It is why, in the example above, the results consist only of key-value pairs { "Company" : "Alfreds Futterkiste" }.

More examples of the unnesting of BSON documents with $ sign applied to a path are given in the next slides.

$project

Operator $project extracts components of subdocuments, renames components, and performs operators on components

Select company name of each customer and skip document identifier

  
db.orders.aggregate([ {$project:{"CUSTOMER.company name":1,"_id":0}} ])

  
{ "CUSTOMER" : { "company name" : "Alfreds Futterkiste" } }
{ "CUSTOMER" : { "company name" : "Ana Trujillo Emparedados y helados" } }
{ "CUSTOMER" : { "company name" : "Antonio Moreno Taquería" } }
  ...            ...         ...

Select company name of each customer and skip document identifier and remove nestings

  
db.orders.aggregate([ {$project:{"Company":"$CUSTOMER.company name","_id":0}} ])

  
{ "Company" : "Alfreds Futterkiste" }
{ "Company" : "Ana Trujillo Emparedados y helados" }
{ "Company" : "Antonio Moreno Taquería" }
  ...            ...         ...

In the present slide $keyname" syntax is used to refer to a value associated with a key "keyname" in the aggregation framework.

We start from the following query implemented with $project operator.

Select customer addresses and remove the nestings.

db.orders.aggregate([ {$project:{"Customer address":"$CUSTOMER.address","_id":0}} ])

The sample results are the following.

{ "Customer address" : "Obere Str. 57" }

{ "Customer address" : "Avda. de la Constitución 2222" }

{ "Customer address" : "Mataderos 2312" }

... ... ...

A path prefixed with $ sign "$CUSTOMER.address" provides a value associated with a key "address" and associates such value with key "Customer address".

Please also note that "_id":0 eliminates document identifier from the answer.

The next query selects a name of each customer and concatenates it with a code of customer.

db.orders.aggregate([ {$project:{"Customer address":{$concat:["$CUSTOMER.address", "-","$CUSTOMER.customer code"]}}}])

The sample results are the following.

{ "_id" : "ALFKI", "Customer address" : "Johan Strauss Str. 23-ALFKI" }

{ "_id" : "ANATR", "Customer address" : "Avda. de la Constitución 2222-ANATR" }

{ "_id" : "ANTON", "Customer address" : "Mataderos 2312-ANTON" }

... ... ...

The query uses two paths prefixed with $sign: "$CUSTOMER.address" and "$CUSTOMER.customer code" to get the values

associated with the keys "address" and "customer code". Then each address of a customer is concatenated with a customer code.

The result of concatenation becomes a value associated in the answer with a key "Customer address":

$project

"$keyname" syntax is used to refer to a value associated with a key "keyname" in the aggregation framework

Select customer addresses and remove nestings

    
db.orders.aggregate([ {$project:{"Customer address":"$CUSTOMER.address","_id":0}} ])

{ "Customer address" : "Obere Str. 57" }
{ "Customer address" : "Avda. de la Constitución 2222" }
{ "Customer address" : "Mataderos  2312" }
  ...            ...         ...

Select a name of each customer and concatenate it with its code

    
db.orders.aggregate([ {$project:{"Customer address":{$concat:["$CUSTOMER.address",
                                                              "-",
                                                              "$CUSTOMER.customer code"]}}}])

{ "_id" : "ALFKI", "Customer address" : "Johan Strauss Str. 23-ALFKI" }
{ "_id" : "ANATR", "Customer address" : "Avda. de la Constitución 2222-ANATR" }
{ "_id" : "ANTON", "Customer address" : "Mataderos  2312-ANTON" }
  ...            ...         ...

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

An operator $match selects the documents that satisfy a given condition. It works in the sam way as the first argument of find() method.

We start from the following query.

Find suppliers located in a city Sandvika.

db.orders.aggregate([{$match:{"SUPPLIER.city":"Sandvika"}}])

A query pattern {"SUPPLIER.city":"Sandvika"} is a value associated with a key $match (operator $match).

In the second query we show to pass the results obtained from one operator as an input to another operator.

Find suppliers located in a city Sandvika and display company name, city, and the names of product supplied

db.orders.aggregate([{$match:{"SUPPLIER.city":"Sandvika"}},

{$project:{"SUPPLIER.company name":1,"SUPPLIER.city":1,"SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

Implementation of aggregation framework consists of two components:

selection implemented as {$match:{"SUPPLIER.city":"Sandvika"}}

and projection implemented as {$project:{"SUPPLIER.company name":1,"SUPPLIER.city":1,"SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}

The results from selection are processed by selection. Each time a document satisfies a query template of $match operator it is passed to $project operator.

In the same moment projection implemented as $project operator is preformed together with selection implemented as $match operator.

It allows for pipeline-based parallelization of query processing and data transformations./

The next example implements a query that finds suppliers located in Germany supplying a product Rossle Sauerkraut. The query displays company name, city, and the names of product supplied.

db.orders.aggregate([{$match:{"SUPPLIER.country":"Germany"}},

{$match:{"SUPPLIER.supplies.PRODUCT.product name":"Rossle Sauerkraut"}},

{$project:{"SUPPLIER.company name":1,"SUPPLIER.city":1, "SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

Data processing pipeline consists of three step. First, JSON documents are filtered with an operator $match and a query template {"SUPPLIER.country":"Germany"} to find

suppliers located in Germany. Next the documents are filtered with an operator $match and a query template {$match:{"SUPPLIER.supplies.PRODUCT.product name":"Rossle Sauerkraut"}}

to find suppliers supplying a product Rossle Sauerkraut. Finally an operator $project extracts from the complete documents information about company name, city, and product names supplied by supplier.

Note, that a complex query template implementing a conjunction of selection conditions can be decomposed into a sequence/pipeline of $match operators.

Such implementation is possible when the selection conditions are connected with and operation. A similar decomposition is possible for $project operator however we have to be careful about

a sequence of projections,

$match

Operator $match selects the documents that satisfy a given condition

Find suppliers located in a city Sandvika

db.orders.aggregate([{$match:{"SUPPLIER.city":"Sandvika"}}])

Find suppliers located in a city Sandvika and display company name, city, and the names of product supplied

  
db.orders.aggregate([{$match:{"SUPPLIER.city":"Sandvika"}},
                     {$project:{"SUPPLIER.company name":1,"SUPPLIER.city":1,
                                "SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

Find suppliers located in Germany supplying a product Rossle Sauerkraut and display company name, city, and the names of product supplied

  
db.orders.aggregate([{$match:{"SUPPLIER.country":"Germany"}},
                     {$match:{"SUPPLIER.supplies.PRODUCT.product name":"Rossle Sauerkraut"}},
                     {$project:{"SUPPLIER.company name":1,"SUPPLIER.city":1,
                                "SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

The present slide demonstrates incorrect decomposition of operator $project into a pipeline of $project operators. We consider the following query.

Find suppliers located in a city Sandvika and display company name, city, and the names of product supplied

A solution given below is an example of incorrect implementation of projection.

db.orders.aggregate([{$match:{"SUPPLIER.city":"Sandvika"}},

{$project:{"SUPPLIER.company name":1}},

{$project:{"SUPPLIER.city":1}},

{$project:{"SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

The first projection reduces a document of a supplier located in a city Sandvika to information about company name. Then it is impossible to further project such result on

city because a name of city is not included in a name of company.

It is interesting that processing of the query does not fail and it provides an empty result. If a projection operator cannot find a path to be projected on

then it returns an empty result.

{ "SUPPLIER" : { } })

$match

Find suppliers located in a city Sandvika and display company name, city, and the names of product supplied

Incorrect implementation of projection

  
db.orders.aggregate([{$match:{"SUPPLIER.city":"Sandvika"}},
   	             {$project:{"SUPPLIER.company name":1}},
	             {$project:{"SUPPLIER.city":1}},
	             {$project:{"SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

  
{ "SUPPLIER" : {  } })

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

$limit, $skip and $sample

Operator $limit passes a given number of documents through a pipeline
Operator $skip eliminates a given number of documents from a pipeline
Operator $sample randomly picks a given number of documents from a pipeline

Find the first two suppliers

  
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:2}])

Find all suppliers except the first two

  
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$skip:2}])

The operators $limit and $skip vcan be combined to list a given fragment of documents from a collection. For example we would like to find the third and the fourth supplier.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:4},

{$skip:2}])

Operator $match is used first to select all suppliers. Then operator $limit is used to take the first 4 suppliers. Finally operator $skip skips the first two suppliers.

Another way is to skip the first two suppliers with $skip and later on take only the first two suppliers with $limit (please see below).

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$skip:2},

{$limit:2}])

In the last example we find all customers located in France and list randomly selected two customers.

db.orders.aggregate([{$match:{"CUSTOMER.country":"France"}},

{$sample:{size:2}}])

$limit, $skip and $sample

Find the third and the fourth supplier

  
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:4},
                     {$skip:2}])

  	    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$skip:2},
                     {$limit:2}])

Find all customers located in France and list the sample 2 documents

  
db.orders.aggregate([{$match:{"CUSTOMER.country":"France"}},
                     {$sample:{size:2}}])

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

$count

An operator$count counts the total number of documents in a pipeline

List the total number of documents in a collection orders

  
db.orders.aggregate([{$count:"Total documents"}])

   
{ "Total documents" : 117 }

Find the total number of suppliers located in Canada

   
db.orders.aggregate([{$match:{"SUPPLIER.country":"Canada"}},
                     {$count:"Total suppliers in Canada"}]))

   
{ "Total suppliers in Canada" : 2 }

An operator $sort sorts the documents in a collection.

For example we would like to display the names of companies of all suppliers located in Canada and sort the names in ascending order

A query can be implemented in the following way.

db.orders.aggregate([{$match:{"SUPPLIER.country":"Canada"}},

{$project:{"SUPPLIER.company name":1,"_id":0}},

{$sort:{"SUPPLIER.company name":1}}])

In the first stage of processing an operator $match finds all documents describing suppliers living in Canada.

{$match:{"SUPPLIER.country":"Canada"}}

Then in the next stage we use $project operator get the names of companies of the suppliers from Canada.

{$project:{"SUPPLIER.company name":1,"_id":0}}

Finally, the names of suppliers are sorted in an ascending order (a value 1 associated with a key "SUPPLIER.company name" indicates an ascending order).

{$sort:{"SUPPLIER.company name":1}}

Is there any difference between the solution given above and the following solution.

db.orders.aggregate([{$sort:{"SUPPLIER.company name":1}},

{$match:{"SUPPLIER.country":"Canada"}},

{$project:{"SUPPLIER.company name":1,"_id":0}} ]))

A new solution, sorts all documents first and performs restrictions and projection later (see below)

db.orders.aggregate([{$sort:{"SUPPLIER.company name":1}},

{$match:{"SUPPLIER.country":"Canada"}},

{$project:{"SUPPLIER.company name":1,"_id":0}} ]))

A new solution is not as good from performance point of view as the original solution. Why the a new solution is worse than the original one ?

It is because sorting is performed on entire collection of documents. We also sort the suppliers from the countries other than Canada. It will take more time because the total number of supplier from Canada is significantly lower than the total number of suppliers. And in the second case we sort all suppliers. It is a very interesting observation because by choosing an appropriate order of operators in a pipeline we can improve performance of query processing. A general principle of optimization applied in many database systems, also in relational database systems is to reduce he amount of data processed as soon as possible such that more complex operations do not need to process the huge amounts of data. For example, the query optimizers of relational systems always try to create processing plans were selections and projections are processed before join.

In the next example we implement our own maximum function. In this example we find a company name and postal code of supplier that has the largest postal code. A solution is based on an idea that if we sort the documents in descending order by postal code then the first document in a sequence after sorting must have the largest postal code. A solution is implemented in the following way.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$project:{"SUPPLIER.company name":1,"SUPPLIER.postal code":1,"_id":0}},

{$sort:{"SUPPLIER.postal code":-1}},

{$limit:1}])))

$match operator filters out all documents that do not contain information about suppliers. Then, $project operator removes from the descriptions of suppliers all keys except company name and postal code. Then, $sort operator sorts the documents in descending order (please see {"SUPPLIER.postal code":-1} ). Finally $limit operator takes the first document from the sorted sequence of documents, i.e. the document with the larges postal code.

$sort

An operator $sort sorts the documents

Display the names of companies of all suppliers located in Canada and sort the names in ascending order

db.orders.aggregate([{$match:{"SUPPLIER.country":"Canada"}},
                     {$project:{"SUPPLIER.company name":1,"_id":0}},
                     {$sort:{"SUPPLIER.company name":1}}])

Why the following solution is worse than the one above ?

   
db.orders.aggregate([{$sort:{"SUPPLIER.company name":1}},
                     {$match:{"SUPPLIER.country":"Canada"}},
                     {$project:{"SUPPLIER.company name":1,"_id":0}} ]))

Find a company name and postal code of supplier that has the largest postal code

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$project:{"SUPPLIER.company name":1,"SUPPLIER.postal code":1,"_id":0}},
                     {$sort:{"SUPPLIER.postal code":-1}},
                     {$limit:1}])))

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

An operator $project can be used to unnest the nested sub-documents. Unfortunately, it applies only to the single nested sub-documents. For example, if a number of documents are nested in an array then it is impossible to use $project to unnest all documents in an array in one go.

An operation $unwind can be used to unnest the sub-documents included in an array. An operation $unwind creates a separate document for each element (sub-document) of a given array. A document is replicated for each element of an array, i.e. an array is unnested.

As a simple example consider a query that lists the names of products supplied by the first 2 suppliers.

An implementation of the query with $project operator only is the following

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:2},

{$project:{"SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

The sample results are listed below. Please note that the nestings of sub-documents in an array associated with a key "supplies" are not removed. In the present implementation no nestings are removed because an operator $project is applied to a path "SUPPLIER.supplies.PRODUCT.product name"that is not prefixed with $ sign. So, it is like a result we may get when applying a "find()" method.

{ "SUPPLIER" : { "supplies" : [ { "PRODUCT" : { "product name" : "Côte de Blaye" } } ] } }

{ "SUPPLIER" : { "supplies" : [ { "PRODUCT" : { "product name" : "Sasquatch Ale" } },

{ "PRODUCT" : { "product name" : "Steeleye Stout" } },

{ "PRODUCT" : { "product name" : "Laughing Lumberjack Lager" } } ] } }

In the next slide we improve a solution a bit with a path "SUPPLIER.supplies.PRODUCT.product name" prefixed with $ sign.

$unwind

An operator $unwind creates a separate document for each element of a given array
A document is replicated for each element of an array, i.e. an array is unnested

List the names of products supplied by the first 2 suppliers

    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:2},
                     {$project:{"SUPPLIER.supplies.PRODUCT.product name":1,"_id":0}}])

    
{ "SUPPLIER" : { "supplies" : [ { "PRODUCT" : { "product name" : "Côte de Blaye" } } ] } }
{ "SUPPLIER" : { "supplies" : [ { "PRODUCT" : { "product name" : "Sasquatch Ale" } },
                                { "PRODUCT" : { "product name" : "Steeleye Stout" } },
                                { "PRODUCT" : { "product name" : "Laughing Lumberjack Lager" } } ] } }

We implement the same query as in the previous slide. List the names of products supplied by the first 2 suppliers.

This time we unnest the sub-documents included in in an array associated with a key "product". Please note "$SUPPLIER.supplies.PRODUCT.product name" used as a value associated with a key "product" in a query. Still array itself is not unnested.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:2},

{$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}])

The results are the following.

{ "product" : [ "Côte de Blaye" ] }

{ "product" : [ "Sasquatch Ale", "Steeleye Stout", "Laughing Lumberjack Lager" ] }

The last solution unnests the values included in an array with an operator $unwind. First, we restrict a collection to the documents with information about suppliers. Then we take the first two documents.

Then an operator $unwind replicates the first two documents as many times as many elements are included in an array associated with a key "supplies". The first document is replicated one time because a supplier supplies only one product "Côte de Blaye". The second document is replicated 3 times because the second supplier5 supplies 3 products: "Sasquatch Ale", "Steeleye Stout" and "Laughing Lumberjack Lager".

Then the product names are unnested with "$SUPPLIER.supplies.PRODUCT.product name".

A complete implementation is given below.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:2},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}])

The results are the following.

{ "product" : "Côte de Blaye" }

{ "product" : "Sasquatch Ale" }

{ "product" : "Steeleye Stout" }

{ "product" : "Laughing Lumberjack Lager" }

$unwind

List the names of products supplied by the first 2 suppliers

    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:2},
                     {$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}])

    
{ "product" : [ "Côte de Blaye" ] }
{ "product" : [ "Sasquatch Ale", "Steeleye Stout", "Laughing Lumberjack Lager" ] }

    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:2},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}])

    
{ "product" : "Côte de Blaye" }
{ "product" : "Sasquatch Ale" }
{ "product" : "Steeleye Stout" }
{ "product" : "Laughing Lumberjack Lager" }

We extend an example from the previous slide with the names of suppliers (companies) and the names of products supplied by the first 2 suppliers.

A solution that does not unnest an array is the following.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:2},

{$project:{"supplier":"$SUPPLIER.company name","product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}])

The sample results are the following.

{ "supplier" : "Aux joyeux ecclesiastiques", "product" : [ "Côte de Blaye" ] }

{ "supplier" : "Bigfoot Breweries", "product" : [ "Sasquatch Ale", "Steeleye Stout", "Laughing Lumberjack Lager" ]

Note that a supplier "Aux joyeux ecclesiastiques" supplies only one product [ "Côte de Blaye" ] and that a supplier "Bigfoot Breweries" supplies three products [ "Sasquatch Ale", "Steeleye Stout", "Laughing Lumberjack Lager" ]

Now, we apply an operator $unwind to unnest the arrays associated with a key "supplies" with {$unwind:"$SUPPLIER.supplies"}. Note that this time $project operator unnests company name and product name.

A complete solution is the following.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:2},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"supplier":"$SUPPLIER.company name","product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}])

The final results are the following.

{ "supplier" : "Aux joyeux ecclesiastiques", "product" : "Côte de Blaye" }

{ "supplier" : "Bigfoot Breweries", "product" : "Sasquatch Ale" }

{ "supplier" : "Bigfoot Breweries", "product" : "Steeleye Stout" }

{ "supplier" : "Bigfoot Breweries", "product" : "Laughing Lumberjack Lager" }

A supplier "Aux joyeux ecclesiastiques" is associated with one product "Côte de Blaye" while a supplier "Bigfoot Breweries" is replicated 3 times and each replication is associated with one product.

Note that the final result of unnesting an array and unnesting the sub-documents looks like a relational table with two columns "supplier" and "product".

$unwind

List the names of suppliers (companies) and the names of products supplied by the first 2 suppliers

    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:2},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "product":"$SUPPLIER.supplies.PRODUCT.product name",
                                "_id":0}}])

    
{ "supplier" : "Aux joyeux ecclesiastiques", "product" : [ "Côte de Blaye" ] }
{ "supplier" : "Bigfoot Breweries", "product" : [ "Sasquatch Ale", "Steeleye Stout", "Laughing Lumberjack Lager" ]

    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$limit:2},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "product":"$SUPPLIER.supplies.PRODUCT.product name",
                                "_id":0}}])

    
{ "supplier" : "Aux joyeux ecclesiastiques", "product" : "Côte de Blaye" }
{ "supplier" : "Bigfoot Breweries", "product" : "Sasquatch Ale" }
{ "supplier" : "Bigfoot Breweries", "product" : "Steeleye Stout" }
{ "supplier" : "Bigfoot Breweries", "product" : "Laughing Lumberjack Lager" }

In the present slide we implement a query that lists the total number of products supplied by the first 2 suppliers.

First we restrict a collection "orders" to information about suppliers. Next we pick the first two documents and we unnest the values in the arrays associated with a key "supplies" with $unwind operator.

We get 4 documents and we unnest the documents to get the names of products only with a projection {$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}}.

Finally we count the total number of documents.

A complete implementation is the following.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$limit:2},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}},

{$count:"Total number of products supplied by the first 2 suppliers"}])

The final result is the following.

{ "Total number of products supplied by the first 2 suppliers" : 4 }

$unwind

List the total number of products supplied by the first 2 suppliers

   
 db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                      {$limit:2},
                      {$unwind:"$SUPPLIER.supplies"},
                      {$project:{"product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}},
                      {$count:"Total number of products supplied by the first 2 suppliers"}])

    
{ "Total number of products supplied by the first 2 suppliers" : 4 }

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

An operator $group groups the documents and applies the aggregation functions to each group.

We start from a simple example that finds the total number of suppliers in a collection "orders".

A simple solution picks all documents with information about supplies and applies an operator $count to counts the total number of documents with information about suppliers.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},{$count:"total"}])

Is it possible to count the total number of suppliers in a different (maybe a bit more complicated) way ?

We can apply an operator $group to entire collection to create one group of all suppliers and then apply a function that counts the total number of suppliers by adding

one to the total result for each element in a group.

A sample implementation is given below,

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},{$group:{"_id": null,"total":{$sum:1}}}])}

An operator $group performs grouping of a collection of documents obtained from an operator $match. Operator $group has two arguments: a grouping key use for grouping and

an aggregation function that supposed to be computed over all elements in each group. In the present case a grouping is is null and it means that one group that

contains all documents in a collection is created. The second argument is an operator $sum that initializes the result with zero and adds 1 to the results for each document in a group ({$sum:1}).

The next two examples shows how $group operator can be used to eliminate duplicates of the result and to implement $distinct operator in our own way.

The next example finds the names of countries, the suppliers come from. A simple implementation applies $match and $project operators together with

a sign $ in from of a path "SUPPLIER.country" to unnest country names.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},{$project:{"country":"$SUPPLIER.country","_id":0}}])

Unfortunately a solution listed above does not eliminate duplicates when more than one supplier comes from the same country. in fact we would like to find the distinct names of countries, the suppliers come from. Elimination of duplicates can be implemented as a side effect of grouping.

first, we fin suppliers and then project information about suppliers on the countries. At this point we may have duplicated names of the countries. To eliminate the duplicates we group the countries using country name as a grouping key {"_id":"$country"}. Then as a side effect of grouping each group must contain only unique names of the countries. The second argument of an operator $group, aggregation is not needed in this case.

A sample implementation is given below.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},{$project:{"country":"$SUPPLIER.country","_id":0}}, {$group:{"_id":"$country"}}]))

$group

An operator $group groups the documents and applies the aggregation functions to each group

Find the total number of suppliers

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$count:"total"}])

  
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$group:{"_id": null,"total":{$sum:1}}}])}

Find the names of countries, the suppliers come from

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$project:{"country":"$SUPPLIER.country","_id":0}}])

Find the distinct names of countries, the suppliers come from

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$project:{"country":"$SUPPLIER.country","_id":0}},
                     {$group:{"_id":"$country"}}]))

The present slide show two example of application of $group operator with an aggregation function (counting). Firs we find distinct country names together with the total number of suppliers per country.

Grouping is performed by the names of countries associated with a key "country" at the end of a path "$SUPPLIER.country" ("_id":"$SUPPLIER.country).

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}}, {$group:{"_id":"$SUPPLIER.country","total":{$sum:1}}}])

Counting (application of aggregation function) is performed by adding one to a temporary counter fo each identical county name in a group.

The sample results are the following.

{ "_id" : "Italy", "total" : 2 }

{ "_id" : "Sweden", "total" : 1 }

{ "_id" : "Germany", "total" : 3 }

... ... ...

Note that a key "_id" is not a unique identifier of a document because the results are no save in a database.

In the next example we find in a bit more effective way distinct country names together with the total number of suppliers per country

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$project:{"country":"$SUPPLIER.country","_id":0}},

{$group:{"_id":"$country","total":{$sum:1}}}])

This time grouping is not performed over the complete documents with information about suppliers. First, we project the documents on country name and then we perform grouping.

As grouping is implemented as sorting or hashing then it can be done faster on a smaller number of data blocks that contain only the names of countries and not complete documents.

{ "_id" : "Italy", "total" : 2 }

{ "_id" : "Sweden", "total" : 1 }

{ "_id" : "Germany", "total" : 3 }

... ... ...

$group

Find distinct country names together with the total number of suppliers per country

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$group:{"_id":"$SUPPLIER.country","total":{$sum:1}}}])

{ "_id" : "Italy", "total" : 2 }
{ "_id" : "Sweden", "total" : 1 }
{ "_id" : "Germany", "total" : 3 }	      
  ...            ...         ...

Find distinct country names together with the total number of suppliers per country

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$project:{"country":"$SUPPLIER.country","_id":0}},
                     {$group:{"_id":"$country","total":{$sum:1}}}])

{ "_id" : "Italy", "total" : 2 }
{ "_id" : "Sweden", "total" : 1 }
{ "_id" : "Germany", "total" : 3 }	      
  ...            ...         ...

In the next example we find the total number of products supplied by each supplier. Information about the products supplied by suppliers is kept in the arrays associated with the paths "SUPPLIER.supplies"

Before counting we unnest the arrays at the end of path "$SUPPLIER.supplies" with $unwind operation. Then we project the results on "$SUPPLIER.company name" and

on "$SUPPLIER.supplies.PRODUCT.product name". An implementation of

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"supplier":"$SUPPLIER.company name", "product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}} ])

The sample results are the following,.

{ "supplier" : "Aux joyeux ecclesiastiques", "product" : "Côte de Blaye" }

{ "supplier" : "Bigfoot Breweries", "product" : "Sasquatch Ale" }

{ "supplier" : "Bigfoot Breweries", "product" : "Steeleye Stout" }

{ "supplier" : "Bigfoot Breweries", "product" : "Laughing Lumberjack Lager" }

... ... ...

Now we add grouping to the previous solution. To find the total number of products supplied by each supplier a grouping must be performed by the the values associated with a key supplier {$group:{"_id":"$supplier","total":{$sum:1}}. Counting is performed in the same way as before. A complete solution is given below.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"supplier":"$SUPPLIER.company name", "product":"$SUPPLIER.supplies.PRODUCT.product name","_id":0}},

{$group:{"_id":"$supplier","total":{$sum:1}}}])

The sample results are consistent with our expectations.

{ "_id" : "Aux joyeux ecclesiastiques", "total" : 1 }

{ "_id" : "Bigfoot Breweries", "total" : 3 }

... ... ...

$group

Find the total number of products supplied by each supplier

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "product":"$SUPPLIER.supplies.PRODUCT.product name",
                                "_id":0}} ])

{ "supplier" : "Aux joyeux ecclesiastiques", "product" : "Côte de Blaye" }
{ "supplier" : "Bigfoot Breweries", "product" : "Sasquatch Ale" }
{ "supplier" : "Bigfoot Breweries", "product" : "Steeleye Stout" }
{ "supplier" : "Bigfoot Breweries", "product" : "Laughing Lumberjack Lager" }
  ...            ...         ...

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "product":"$SUPPLIER.supplies.PRODUCT.product name",
                                "_id":0}},
                     {$group:{"_id":"$supplier","total":{$sum:1}}}])

{ "_id" : "Aux joyeux ecclesiastiques", "total" : 1 }
{ "_id" : "Bigfoot Breweries", "total" : 3 }
  ...            ...         ...

The next example shows how to group the documents by company name and how to perform summation of prices of products supplied by each supplier. The query finds the total prices of products per supplier.

Like in the previous example we first extract the names of suppliers associated with the prices of products supplied by each suppler. To do so we unnest an array with the names of products supplied by each supplier and then we project the results on company name and price.

An implementation is given below.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"supplier":"$SUPPLIER.company name", "price":"$SUPPLIER.supplies.PRODUCT.unit price","_id":0}} ])

The results look like two column table with the names of companies in one column and prices from supplied products in the other.

{ "supplier" : "Aux joyeux ecclesiastiques", "price" : 263.5 }

{ "supplier" : "Bigfoot Breweries", "price" : 14 }

{ "supplier" : "Bigfoot Breweries", "price" : 18 }

{ "supplier" : "Bigfoot Breweries", "price" : 14 }

... ... ...

To find the total process of products supplied by each company we apply an operator $group To the results obtained in the previous step.

A complete implementation is the following.

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},

{$unwind:"$SUPPLIER.supplies"},

{$project:{"supplier":"$SUPPLIER.company name","product":"$SUPPLIER.supplies.PRODUCT.product name","price":"$SUPPLIER.supplies.PRODUCT.unit price", "_id":0}},

{$group:{"_id":"$supplier","total":{$sum:"$price"}}}])

The final results are the following.

{ "_id" : "Aux joyeux ecclesiastiques", "total" : 263.5 }

{ "_id" : "Bigfoot Breweries", "total" : 46 }

... ... ...

$group

Find the total prices of products per supplier

db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "price":"$SUPPLIER.supplies.PRODUCT.unit price",
                                "_id":0}} ])

{ "supplier" : "Aux joyeux ecclesiastiques", "price" : 263.5 }
{ "supplier" : "Bigfoot Breweries", "price" : 14 }
{ "supplier" : "Bigfoot Breweries", "price" : 18 }
{ "supplier" : "Bigfoot Breweries", "price" : 14 }
  ...            ...         ...

   
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "product":"$SUPPLIER.supplies.PRODUCT.product name",
                                "price":"$SUPPLIER.supplies.PRODUCT.unit price",
                                "_id":0}},
                     {$group:{"_id":"$supplier","total":{$sum:"$price"}}}])

{ "_id" : "Aux joyeux ecclesiastiques", "total" : 263.5 }
{ "_id" : "Bigfoot Breweries", "total" : 46 }
  ...            ...         ...

$group

Find the averages of all prices of products per supplier

    
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "price":"$SUPPLIER.supplies.PRODUCT.unit price",
                                "_id":0}},
                     {$group:{"_id":"$supplier","average":{$avg:"$price"}}}])}

  
{ "_id" : "Aux joyeux ecclesiastiques", "average" : 263.5 }
{ "_id" : "Bigfoot Breweries", "average" : 15.333333333333334}
  ...            ...         ...

$group

Find the minimum and maximum prices of products per supplier

        
db.orders.aggregate([{$match:{"SUPPLIER":{$exists:true}}},
                     {$unwind:"$SUPPLIER.supplies"},
                     {$project:{"supplier":"$SUPPLIER.company name",
                                "price":"$SUPPLIER.supplies.PRODUCT.unit price",
                                "_id":0}},
                     {$group:{"_id":"$supplier","min":{$min:"$price"},"max":{$max:"$price"}}}])

  
{ "_id" : "Aux joyeux ecclesiastiques", "min" : 263.5, "max" : 263.5 }
{ "_id" : "Bigfoot Breweries", "min" : 14, "max" : 18 }
  ...            ...         ...

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

An operator $out saves the results of processing in a collection. A strict rule that applies to all aggregation framework operators is such that each operator acts on a collection of BSON documents and produces on output a collection of bSON documents. Such rule allows for saving the intermediate results of processing in a collection of BSON documents.

In the present example we find the customer code and the names of products purchased by a customer with a customer code AROUT and save the result in a collection "purchases".

An implementation is the following

db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},

{$project:{"Code":"$CUSTOMER.customer code",

"Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}} ])

An implementation finds a document with customer code equal to "AROUT" and projects the document on customer code and product name. The customer submitted one order that consisted of fife products: "Gorgonzola Telino", "Grandma's Boysenberry Spread", "Konbu", "Mozzarella di Giovanni" and "Tofu". All orders submitted by a customer are kept in an array of sub-document "ORDER" and the items included in each order are alo kept in an array of sub-documents ORDER DETAIL. It is why a key "purchased" in an answer is associated with an array of arrays of sub-documents

{ "_id" : "AROUT","Code" : "AROUT", "Purchased" : [

[

"Gorgonzola Telino",

"Grandma's Boysenberry Spread",

"Konbu",

"Mozzarella di Giovanni",

"Tofu"

]

}

To save the result in a new collection "purchases" we apply an operator $out, see the next slides.

$out

An operator $out saves the results of processing in a collection

Find the customer code and the names of products purchased by a customer with a customer code AROUT and save the result in a collection "purchases"

   
db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},
                     {$project:{"Code":"$CUSTOMER.customer code",                        
                       "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}} ])

{ "_id" : "AROUT","Code" : "AROUT", "Purchased" : [
                                                    [
                                                      "Gorgonzola Telino",
                                                      "Grandma's Boysenberry Spread",
                                                      "Konbu",
                                                      "Mozzarella di Giovanni",
                                                      "Tofu"
                                                    ]
                                                  ]
}

To eliminate the first level of nesting at "ORDER" level we apply an operator $unwind in the following way.

db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},

{$project:{"Code":"$CUSTOMER.customer code", "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}},

{$unwind:"$Purchased"}])

The result contains only one document because the customer submitted only one order. Still an order consists of five products and an array of five product names is assigned to a key "Purchased".

"Gorgonzola Telino",

"Grandma's Boysenberry Spread",

"Konbu",

"Mozzarella di Giovanni",

"Tofu"

]

}

$out

Find the customer code and the names of products purchased by a customer with a customer code AROUT and save the result in a collection "purchases"

   
db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},
                     {$project:{"Code":"$CUSTOMER.customer code",                        
                          "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}},
                     {$unwind:"$Purchased"}])

{ "_id" : "AROUT","Code" : "AROUT", "Purchased" : [
                                                    "Gorgonzola Telino",
                                                    "Grandma's Boysenberry Spread",
                                                    "Konbu",
                                                    "Mozzarella di Giovanni",
                                                    "Tofu"
                                                  ]
}

The final implementation of a query that finds the customer code and the names of products purchased by a customer with a customer code AROUT and save the result in a collection "purchases" is give in the present slide. To unnest the anmes of purchased products we apply $unnest operator twice.

db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},

{$project:{"Code":"$CUSTOMER.customer code", "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}},

{$unwind:"$Purchased"},

{$unwind:"$Purchased"}])

The results are given below. Please note that a customer "AROUT" submitted one order fro 5 products. it is why customer code is repeated 5 times with each of the products purchased.

{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Gorgonzola Telino" }

{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Grandma's Boysenberry Spread" }

{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Konbu" }

{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Mozzarella di Giovanni" }

{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Tofu" }

Finally we use $ out operator to save the results in a new collection "purchases", Please note that a key "_id" must be eliminated ({$project:{"_id":0}}) before saving the results in a collection "purchases".

db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},

{$project:{"Code":"$CUSTOMER.customer code", "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}},

{$unwind:"$Purchased"},

{$project:{"_id":0}},

{$out:"purchases"}])

To verify the final result we apply a method find() with a method pretty().

db.purchases.find().pretty()

$out

Find the customer code and the names of products purchased by a customer with a customer code AROUT and save the result in a collection "purchases"

   
db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},
                     {$project:{"Code":"$CUSTOMER.customer code",                        
                          "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}},
                     {$unwind:"$Purchased"},
                     {$unwind:"$Purchased"}])

{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Gorgonzola Telino" }
{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Grandma's Boysenberry Spread" }
{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Konbu" }
{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Mozzarella di Giovanni" }
{ "_id" : "AROUT", "Code" : "AROUT", "Purchased" : "Tofu" }

      
db.orders.aggregate([{$match:{"CUSTOMER.customer code":"AROUT"}},
                     {$project:{"Code":"$CUSTOMER.customer code",                        
                          "Purchased":"$CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name"}},
                     {$unwind:"$Purchased"},
                     {$unwind:"$Purchased"},
                     {$project:{"_id":0}},
                     {$out:"purchases"}])

  
db.purchases.find().pretty()

The present slide and the next slide show how to implement a query that finds the names, categories, and unit prices of all products supplied and saves the result in a collection "products".

We start from finding the complete descriptions of all products. The query creates key-value pairs { "product" : [ { "product name" : "Chef Anton's Cajun Seasoning", ...} ] }, ...

db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT", "_id":0 }} ])

The first key-value is the following.

{ "product" : [ { "product name" : "Chef Anton's Cajun Seasoning",

"category name" : "Condiments",

"quantity per unit" : "48 - 6 oz jars",

"unit price" : 25,

"units in stock" : 53,

"units on order" : 0,

"reorder level" : 0,

"discontinued" : "N"

... ... ...

Next, we unnest the arrays associated with a key "product" with $unwind operator

db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT", "_id":0 }},

{$unwind:"$product"} ])

$out

Find the names, categories, and unit prices of all products supplied and save the result in a collection "products"

    
db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT",
                                "_id":0 }} ])

   
{ "product" : [ { "product name" : "Chef Anton's Cajun Seasoning",
                  "category name" : "Condiments",
                  "quantity per unit" : "48 - 6 oz jars",
                  "unit price" : 25,
                  "units in stock" : 53,
                  "units on order" : 0,
                  "reorder level" : 0,
                  "discontinued" : "N"
                },
  ...           ...         ...

    
db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT",
                                "_id":0 }},
                     {$unwind:"$product"} ])

Now, we can project the results on product name, category product belongs to and price. An extended implementation is given below.

db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT", "_id":0 }},

{$unwind:"$product"},

{$project:{"name":"$product.product name", "category":"$product.category name","price":"$product.unit price"}} ])

The sample results look like a flat table with three columns: "name", "category" and "price".

{ "name" : "Côte de Blaye", "category" : "Beverages", "price" : 263.5 }

{ "name" : "Sasquatch Ale", "category" : "Beverages", "price" : 14 }

{ "name" : "Steeleye Stout", "category" : "Beverages", "price" : 18 }

... ... ...

Finally we save the results in a collection "products" and we lists the contents of the collection with find() and pretty().

db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT", "_id":0 }},

{$unwind:"$product"},

{$project:{"name":"$product.product name","category":"$product.category name","price":"$product.unit price"}},

{$out:"products"}])

db.products.find().pretty()

$out

Find the names, categories, and unit prices of all products supplied and save the result in a collection "products"

    
db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT",
                                "_id":0 }},
                     {$unwind:"$product"},
                     {$project:{"name":"$product.product name",
                                "category":"$product.category name",
                                "price":"$product.unit price"}} ])

{ "name" : "Côte de Blaye", "category" : "Beverages", "price" : 263.5 }
{ "name" : "Sasquatch Ale", "category" : "Beverages", "price" : 14 }
{ "name" : "Steeleye Stout", "category" : "Beverages", "price" : 18 }
  ...           ...         ...

        
db.orders.aggregate([{$project:{"product":"$SUPPLIER.supplies.PRODUCT",
                                "_id":0 }},
                     {$unwind:"$product"},
                     {$project:{"name":"$product.product name",
                                "category":"$product.category name",
                                "price":"$product.unit price"}},
                     {$out:"products"}])

db.products.find().pretty()

MongoDB Aggregation Framework

Outline

Aggregation framework ? What is it ?
Sample database
Aggregation operators

$project
$match
$limit, $skip and $sample
$sort and $count
$unwind
$group
$out
$lookup

An operator $lookup performs a left outer join to an unsharded collection in the same database to filter in documents from the "joined" collection for processing.

Assume that a collection "purchases" created earlier looks like below.

{ "_id" : ObjectId("61342804f26c5a766e0e1c6f"),"Code" : "AROUT", "Purchased" : "Gorgonzola Telino" }

{ "_id" : ObjectId("61342804f26c5a766e0e1c70"),"Code" : "AROUT", "Purchased" : "Grandma's Boysenberry Spread" }

A collection "products" created earlier looks like below.

{ "_id" : ObjectId("61342773f26c5a766e0e1c1a"),"name" : "Gorgonzola Telino","category" : "Dairy Products","price" : 12.5 }

{ "_id" : ObjectId("61342773f26c5a766e0e1c22"),"name" : "Grandma's Boysenberry Spread","category" : "Condiments","price" : 25 }

... ... ...

The collections have the semantically compatible keys "Purchased" and "name" both associated with the names of products. The keys will be used by $lookup operator to join the bSON documents from then collections.

$lookup

An operator $lookup performs a left outer join to an unsharded collection in the same database to filter in documents from the "joined" collection for processing

A collection purchases created earlier

       
{ "_id" : ObjectId("61342804f26c5a766e0e1c6f"),
  "Code" : "AROUT", "Purchased" : "Gorgonzola Telino" }
{ "_id" : ObjectId("61342804f26c5a766e0e1c70"),
  "Code" : "AROUT", "Purchased" : "Grandma's Boysenberry Spread" }
  ...           ...         ...

A collection products created earlier

   
{ "_id" : ObjectId("61342773f26c5a766e0e1c1a"),
  "name" : "Gorgonzola Telino","category" : "Dairy Products","price" : 12.5 }
{ "_id" : ObjectId("61342773f26c5a766e0e1c22"),
  "name" : "Grandma's Boysenberry Spread","category" : "Condiments","price" : 25 }
  ...           ...         ...

First we compute a left outer join of a collection "purchases" with a collection "products" over a condition "purchases.Purchased" = "products.name"

db.purchases.aggregate([{$lookup: {from:"products", localField:"Purchased",foreignField:"name", as:"result"}} ])

A method "aggregate()" is applied to a collection "purchases". An operator $lookup acts on a collection "products". A key used from a collection "purchases" is determined as a "localField' and a key used from a collection "products" is determined as foreign Field. The results are listed below.

{ "_id" : ObjectId("61342804f26c5a766e0e1c6f"),"Code" : "AROUT","Purchased" : "Gorgonzola Telino","result" : [

{"_id" : ObjectId("61342773f26c5a766e0e1c1a"),

"name" : "Gorgonzola Telino",

"category" : "Dairy Products",

"price" : 12.5}]

}

{ "_id" : ObjectId("61342804f26c5a766e0e1c70"),"Code" : "AROUT","Purchased" : "Grandma's Boysenberry Spread","result" : [

{ "_id" : ObjectId("61342773f26c5a766e0e1c22"),

"name" : "Grandma's Boysenberry Spread",

"category" : "Condiments",

"price" : 25} ]

}

... ... ...

A collection "purchases" contains information about the names of products purchased by the customers. A complete description of the respective product as associated with a key "result" and it is included in an array.

$lookup

A left outer join of a collection purchases with a collection products over a condition purchases.Purchased = products.name

       
db.purchases.aggregate([{$lookup: {from:"products",
                                   localField:"Purchased",
                                   foreignField:"name",
                                   as:"result"}} ])

   
{ "_id" : ObjectId("61342804f26c5a766e0e1c6f"),
  "Code" : "AROUT","Purchased" : "Gorgonzola Telino",
   "result" : [ {"_id" : ObjectId("61342773f26c5a766e0e1c1a"),
                 "name" : "Gorgonzola Telino",
                 "category" : "Dairy Products",
                 "price" : 12.5}
              ]
}
{ "_id" : ObjectId("61342804f26c5a766e0e1c70"),
  "Code" : "AROUT","Purchased" : "Grandma's Boysenberry Spread",
  "result" : [ { "_id" : ObjectId("61342773f26c5a766e0e1c22"),
                 "name" : "Grandma's Boysenberry Spread",
                 "category" : "Condiments",
                 "price" : 25}
             ]
}
  ...           ...         ...

This slide shows how to compute a left outer join of a collection "products" with a collection "purchases" over a condition products.name = purchases.Purchased

db.products.aggregate([{$lookup: {from:"purchases",localField:"name",foreignField:"Purchased",as:"result"}} ])

The query find all purchases for each product. This time a method "aggregate()" is applied to a collection "products". An operator $lookup acts on a collection "purchases". A key used from a collection "products" is determined as a "localField" and a key used from a collection "purchases" is determined as "foreignField". The results are listed below.

{"_id" : ObjectId("61342773f26c5a766e0e1c22"), "name" : "Grandma's Boysenberry Spread","category" : "Condiments", "price" : 25,

"result" : [{"_id" : ObjectId("61342804f26c5a766e0e1c70"),"Code" : "AROUT","Purchased" : "Grandma's Boysenberry Spread"}]

}

{"_id" : ObjectId("61342773f26c5a766e0e1c1a"),"name" : "Gorgonzola Telino","category" : "Dairy Products","price" : 12.5,

"result" : [{"_id" : ObjectId("61342804f26c5a766e0e1c6f"),"Code" : "AROUT","Purchased" : "Gorgonzola Telino"}]

}

... ... ...

{"_id" : ObjectId("61342773f26c5a766e0e1c0e"),"name" : "Côte de Blaye","category" : "Beverages","price" : 263.5,

"result" : [ ]

}

{"_id" : ObjectId("61342773f26c5a766e0e1c0f"),"name" : "Sasquatch Ale","category" : "Beverages","price" : 14,

"result" : [ ]

}

... ... ...

A product can be included in zero or more purchases. it is why all purchase of a given product are stored in an array. Please note that the products "Côte de Blaye" and "Sasquatch Ale" have never been purchased and it is why the arrays associated with a key "result" are empty

$lookup

A left outer join of a collection products with a collection purchases over a condition products.name = purchases.Purchased

       
db.products.aggregate([{$lookup: {from:"purchases",
                                  localField:"name",
                                  foreignField:"Purchased",
                                  as:"result"}} ])

   
{"_id" : ObjectId("61342773f26c5a766e0e1c22"), "name" : "Grandma's Boysenberry Spread","category" : "Condiments", "price" : 25,
 "result" : [{"_id" : ObjectId("61342804f26c5a766e0e1c70"),"Code" : "AROUT","Purchased" : "Grandma's Boysenberry Spread"}]
}
{"_id" : ObjectId("61342773f26c5a766e0e1c1a"),"name" : "Gorgonzola Telino","category" : "Dairy Products","price" : 12.5,
 "result" : [{"_id" : ObjectId("61342804f26c5a766e0e1c6f"),"Code" : "AROUT","Purchased" : "Gorgonzola Telino"}]
}
  ...           ...         ...	    	  

{"_id" : ObjectId("61342773f26c5a766e0e1c0e"),"name" : "Côte de Blaye","category" : "Beverages","price" : 263.5,
 "result" : [ ]
}
{"_id" : ObjectId("61342773f26c5a766e0e1c0f"),"name" : "Sasquatch Ale","category" : "Beverages","price" : 14,
 "result" : [ ]
}
  ...           ...         ...

References

MongoDB Manual, Aggregation
Banker K., Bakkum P., Verch S., Garret D., Hawkins T., MongoDB in Action, 2nd ed., Manning Publishers, 2016
Chodorow K. MongoDB The Definitive Guide, O'Reilly, 2013