document database

EE5178 Module 14 NoSQL -

MongoDB Query Language
Ming-Ling Lo
2025.05
MongoDB Query
Language Overview

2
Installation Information
● Use MongoDB on local machine
○ Editions Use this for class work
■ Community edition: free use
● Download: [Link]
■ Enterprise edition: add LDAP and Kerberos support, on-disk encryption, auditing,
etc..
○ Start up the MongoDB
■ If you install MongoDB as a service -- Mongodb will startup automatically when you
power up your PC
■ If you do not install MongoDB as a service -- must start Mongo manually
■ To start the Mongodb shell: "C:\Program Files\MongoDB\Server\4.4\bin\[Link]" (or
change “C:\Program Files\MongoDB\” to your own installation path)
● Use cloud-based MongoDB Atlas:
○ Follow instructions on MongoDB website (Free use after registration)

3
Usage Information
● Use MongoDB on local machine
○ Use Compass
■ It comes together with MongoDB server installation package
■ Will start up automatically after installation.
■ Go to “Open MongoDB Shell” and you can start typing MQL queries.
■ This is the easiest way to start using location installation
○ Use MongoDB Shell This is my installation
path, (change “D:\Program
■ Download [Link] Files\MongoDB\Shell\” to
■ Installing and starting up the server, start the Mongodb shell by: your own installation path)
"D:\Program Files\MongoDB\Shell\[Link]"
■ When the shell ask you for a connection string, type: mongodb://localhost:27017/
● Use cloud-based MongoDB Atlas
○ Follow the instruction on the web page; should be straightforward

4
Using the Shell and Query Documentation
● In the shell, you can always get help by typing: help and [Link]()
○ And find out more from there
● To practice example MongoDB query language usages (example sessions)
○ [Link]
● For more detailed information: [Link]

> help general help

[Link]() help on db methods
[Link]() help on collection methods
show dbs show database names
show collections show collections in current database
………..

5
MongoDB Data Model
● The MongoDB Data model consists of
○ Databases: analogous to RDB databases Unique and powerful feature of MongoDB

○ Collections: analogous to RDB tables

○ Documents: analogous to RDB records; isomorphic to JSON document
○ Views: include read-only view, on-demand materialized views
● Rich language support
MongoDB SQL Notes

Database Database

Collection Table

View View MongoDB supports On-demand materialized views*

Document Row
6
MongoDB Query Language
● MongoDB Query Language
○ The query language of MongoDB (a.k.a. called MQL)
● Three key features of MQL
○ Queries
■ Constructed using JSON-like documents.
■ The query documents specify the criteria for selecting data documents.
■ JSON-like documents are used both to store data and to express queries
○ Operators
■ Embedded operators in query documents can implement various kinds of queries
○ Pipelines
■ Expressed as an array in JSON document.
■ Enable the implementation of very powerful queries

7
 One can create an empty collection
explicitly using “[Link]()”.
Mainly used to create a collection with
Creating Database and Collection specific options
db, collection ,document a = 3+5 , a a

● Object creation in MongoDB - lazy evaluation

○ Object creation in MongoDB is based on the lazy evaluation principle
○ One need not create databases or collections before using them
○ Accessing a new database or collection name will cause the name to be known to the system
○ Insert data into a collection will cause the collection and its containing database to be created,
if not already
● Example:
○ Assume NewDB and NewCollection both do not exist.
○ “use NewDB”
■ Causes “NewDB” to be known as the database of future operations.
■ No database is really created at this time.
○ “[Link]( { myVal: 1 } )” causes three things to happen:
■ (1) NewDB is created
■ (2) NewCollection is created inside NewDB
■ (3) document {myVal: 1} is created and inserted into NewCollection

8
Collections
● Collection Shema
○ In MongoDB, a collection can work with or without a schema
○ Without schema (default): within a collection:
■ The documents can have the different shapes and different fields
■ The same field name can have different data types across different documents.
○ With schema: enforce JSON schema validation for a collection
■ MongoDB supports draft 4 of JSON Schema, including core specification and validation
specification, with some differences. For details, see Extensions and Omissions.
■ For more information about JSON Schema, see the official website.
■ Use “[Link]()” with the $jsonSchema operator to enforce schema
● UUID
○ Each collection is assigned an immutable UUID.
■ Remains the same across all members of a replica set and shards of the collection
○ To retrieve the UUID of a collection: [Link]()

9
Example of [Link]("students", {
validator: {
Collection with $jsonSchema: {
bsonType: "object",
Schema Validation title: "Student Object Validation",
required: [ "address", "major", "name", "year" ],
properties: {
the following command to create name: {
a students collection and use bsonType: "string",
description: "'name' must be a string and is required"
the $jsonSchema operator to },
set schema validation rules: year: {
bsonType: "int",
minimum: 2017,
maximum: 3017,
description: "'year' must be an integer in [ 2017, 3017 ] and is required"
},
gpa: {
bsonType: [ "double" ],
description: "'gpa' must be a double if the field exists"
}
}
}
}
})
10
Views
● Read-only, and can be defined on
○ Aggregation pipeline → powerful
○ Collections, other views.
● Purposes
○ To exclude private or confidential data from a collection.
○ To add computed fields to a collection
○ To join data from two different related collections.
● On-demand Materialized views: a powerful mechanism in MongoDB
○ Are really “incremental aggregation pipelines”
● Views can be created by
[Link](
[Link]( "<viewName>",
"<viewName>", {
"<source>", "viewOn" : "<source>",
[<pipeline>], "pipeline" : [<pipeline>],
{ "collation" : { <collation> } "collation" : { <collation> }
}) })

11
Documents
● Expressed in JSON format, stored in BSON format
● Serve several important purposes
○ Store data
○ Express all sorts of conditions in MongoDB query operations
■ Query filter condition, update condition, delete condition, index condition, etc.
○ Convey the execution results of an MQL operations
● Each document stored in a collection requires a unique _id field
○ Acts as a primary key.
● When inserting a document into collection, the user can
○ Specify _id explicitly
■ The _id value can be of any BSON data type, except for array.
○ Insert without specifying _id
■ MongoDB automatically generates an ObjectId for the _id field.

12
MongoDB Basic
CRUD Operations

13
High-Level Commands - DB Note: mongoDB commands are
case sensitive

● MongoDB’s philosophy of data definition

○ Do no require separate data definition commands.
○ A database/collection is created when data is stored into it for the first time.

MongoDB SQL Action in MongoDB

show dbs Show databases Show all databases

Create database test In MongoDB, a data object is created

Need not when you first store something into it
create db first
use test Use test Connect to db ‘test’. In MongoDB you
can connect to a DB before it is really
created

db Show currently used db

14
High-Level Commands - Collection
MongoDB SQL Action

show collections Show tables Show all tables in current db

[Link]()
[Link]()

Create table inventory… RDB must create table first

[Link]([ Insert into inventory Insert rows/docs into table/collection

Need not { ...}, ...} ]); values(...);
create
collection first [Link]({}) Select * from inventory; Retrieve all data from table/collection
[Link]({}).pretty()

[Link]( { status: "D" Select * from inventory Retrieve row/document where

} ); where status=”D”; condition is met.

select project
[Link]({} ,{name :1})
15
CRUD - Create (Insert) Document
● Create (Insert)
The whole command is actually
○ [Link](<doc>) a JavaScript function call
○ [Link]([<doc1>,<doc2>,...])

16
CRUD - Read (Find) Document Conditions may contain operators.
Operators are of the form $xxx

● Read (Find)
○ [Link]( <query>, <projection>, <options> )
○ [Link]( <query>, <projection>, <options> )

Write name of collection Write select Write projection

here. Do not really write conditions here field names here
down “collection”!
Operators
18

0 1 show
1: project this field; 0: don’t project this field
17
CRUN - A Closer Look at MongoDB Commands
● The parameters in MongoDB function call are often optional
● Example: using find() as example
○ To find user documents in the “users” collection:
The function [Link]( <query>, <projection>, <options> )
Can be called as
[Link]() → all user docs
[Link](<query>) → user docs matching query condition
[Link](<query>, <projection>) → matching condition and project
[Link](<query>, <projection>, <options>) → with additional options

18
CRUD - Update Document
● UPDATE (update, replace)
○ [Link](<filter>, <update>, <options>)
○ [Link]( <filter>, <update>, <options> )
○ [Link]( <filter>, <replacement doc>, <options> )
● One vs Many
○ UpdateOne(): Update the first document satisfying the filter condition
○ UpdateMany(): update all documents satisfying the filter condition
● Any field “updated” but previously did not exists will be created Field update operators:
● Why both update and replace? $currentDate
○ Update → it’s still the same document (same _ID) $inc
○ Replace → a different document (different _ID) $min
$max
$mul
$rename
$set
$setOnInsert
18 status "reject"
status $unset 19
_id age
CRUD - Delete Document
● DELETE (delete)
○ [Link]. deleteOne(<filter>, <options>)
○ [Link]. deleteMany(<filter>, <options>)

20
Deeper Look at MQL and MQL Shell ✋wait a minute
● MongoDB is designed based on JavaScript
● MongoDB Query Language is actually compatible with JavaScript code
○ All MQL commands are valid JS statements
● MongoDB Shell can actually accept most JavaScript language
○ For example, you can write something like the following in the shell prompt:
let ageToFind = 30;
let cityToMatch = "Paris";
let query = { age: ageToFind, city: cityToMatch };
[Link].find(query);
○ Or even the following
function greetUser(user) { Very useful if you are a
print("Hello, " + [Link] + "!"); JavaScript programmer!
}
[Link].findOne({ city: "London" }).forEach(greetUser);

21
More on Query Filter Documents
Consider the following example
{
“_id” : ObjectId(“5e009………”), System generates ID automatically
“Item” : “postcard”,
“qty” : “A”,
“status” : “A”,
“size” : {
“h” : 10, Nested document
“w” : “15.25,
“uom” : “cm”
}
“tags” : [
“blue”
]
}

22
 When the field is a simple word, you can do without quote signs.
Need quote signs When the field is of the form “x.y”, you need the quote sign

More on Query Filter Documents (2)

MongoDB Similar SQL Action

[Link]( { "[Link]": "cm" } ) Requires a join Retrieve data from nested document → use
dot expression.

[Link]( { size: { h: 14, w: 21, uom: AND in where clause Multiple condition from nested document →
"cm" } } ) comma in filter doc.

[Link]( {}, { item: 1, status: 1 } ); “Select item, status from inventory” Select with projection
[Link]( {}, { _id: 0, item: 1, status: 2nd mongoDB query excludes system
1 } ); generated ID.

[Link]( { status: { $in: [ "A", "D" ] } “SELECT * FROM inventory WHERE $in operator
}) status in ("A", "D")”

[Link]( { status: "A", qty: { $lt: 30 } “SELECT * FROM inventory WHERE $lt operator
}) status = "A" AND qty < 30”

[Link]( { $or: [ { status: "A" }, { SELECT * FROM inventory WHERE ‘$or’ operator. AND is express with comma,
qty: { $lt: 30 } } ] } ) status = "A" OR qty < 30 OR requires explicit $or operator
23
Query Filter Document on Arrays

Consider the example:

[Link]([
{ item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
{ item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
{ item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
{ item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
{ item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
]);

Note that in this example both the color tags and the dimension of the
inventory items are expressed as arrays

24
 SQL array

Query Filter Documents on Arrays (2)

MongoDB SQL Action

[Link]( { tags: ["red", "blank"] } ) Exact: find document with a “tags” array ["red", "blank"]

[Link]( { tags: { $all: ["red", "blank"] } } ) Contain: find document with a “tags” array which contains
“red” and “bank” (can have other tags)

[Link]( { tags: "red" } ) Contain: when there is only element in the condition

[Link]( { dim_cm: { $gt: 25 } } ) dim_cm: [ 14, 21 ] Find document whose “dim_cm” contains one element > 25

[Link]( { dim_cm: { $gt: 20, $lt: 15 } } ) Both conditions are met. Not necessarily by the same
[Link]( { dim_cm: { $gt: 15, $lt: 20 } } ) element

[Link]( { dim_cm: { $elemMatch: { $gt: 22, $lt: Same element matches both conditions
30 } } } )

[Link]( { "dim_cm.1": { $gt: 25 } } ) Condition for array index position. “dim_cm.1” means the 2nd
position of “dim_cm” array.
25
Query Filter Documents for Null and Non-existence
MongoDB SQL Action

[Link]( { item: null } ) No exact match in SQL Find document which either does not contain “item”
or whose “item” is null.

[Link]( { item : { $type: 10 } Select * from inventory Return only the document whose “item” is null.
}) where type is null; Type 10 is BSON type # for null

[Link]( { item : { $exists: No match in SQL Return only the document which does not contain
false } } ) an ‘item’ field.

[Link]({}) Select * from inventory

26
More on Updates
MongoDB SQL Action

[Link]( 1. Use $set operator to update

{ item: "paper" }, 2. Any field “updated” but previously did not exists
{ will be created
$set: { "[Link]": "cm", status: "P" }, 3. Update/create the value of “lastModified” field to
$currentDate: { lastModified: true } the currentDate
}
)

[Link](...) All document satisfying the condition will be updated

[Link]( Replace the old document with the new document (with
{ item: "paper" }, different _id)
{ item: "paper", instock: [ { warehouse: "A",
qty: 60 }, { warehouse: "B", qty: 40 } ] }
)

27
 go DB feature
Important Mon
Text Search on Documents
MongoDB Action

[Link]( [ Insert information about coffee shops

{ _id: 1, name: "Java Hut", description: "Coffee and cakes" },
{ _id: 2, name: "Burger Buns", description: "Gourmet
hamburgers" },
{ _id: 3, name: "Coffee Shop", description: "Just coffee" },
{ _id: 4, name: "Clothes Clothes Clothes", description: "Discount
clothing" }, …… ])

[Link]( { name: "text", description: "text" } ) Create text index based both “name” and
“description” fields

[Link]( { $text: { $search: "java coffee shop" } } ) Find shops with words “java” or “coffee” or “shop”

[Link]( { $text: { $search: "\"coffee shop\"" } } ) Find shops containing the phase “coffee shop”.

[Link]( { $text: { $search: "java coffee -shop" } }, { score: { Find all documents containing “java coffee”, but
$meta: "textScore" } } ).sort( { score: { $meta: "textScore" } } ) not “shop”, sort the result by their matching score
28
MongoDB Aggregation
Pipelines

29
Aggregation in MongoDB
● Aggregation
○ Group values from multiple documents,
○ Perform a variety of operations on the grouped data,
○ To return a single result.
● MongoDB provide three ways to perform aggregation:
○ Single Purpose Aggregation Operations
■ Similar to SQL aggregations
■ Less flexible / powerful, but easy for quick uses
○ Map-Reduce:
■ Follow the map-reduce paradigm, can/should be replaced by map-reduce aggregation
pipelines
○ Aggregation Pipeline
■ ⇒ A flexible and powerful way to do aggregation p o rt a n t M o n goDB feature
Im
30
Single Purpose Aggregate Operations
● Single purpose aggregation operations
○ [Link](<options>)
○ [Link](<filter>, <options>)
○ [Link](<field>, <filter>, <options>)

MongoDB Similar SQL Action

[Link]( {} ) Select count(*) from orders Use metadata instead of actually

counting documents

[Link]( {} ) Select count(*) from orders Similar to find() but only count
the # of matching documents

[Link](“cust_id”) Select distinct cust_id from

orders
31
Aggregation Pipeline
● An aggregation pipeline
○ Consists of one or more stages that process documents.
● Each stage
○ Take a collection of documents as input
○ Performs transformation on the input collection.
■ E.g. filter, group, or calculate values from the documents.
○ Output a set of documents, and pass documents to the next stage., if exists.
● Output of pipeline stage
○ A pipeline stage does not necessarily produce one output document for each input document
○ A pipeline stage may filter out documents, generate one document for a group of documents
○ Some stages may even generate new documents.
○ For any stage, the number of output document can be less than, equal to, or more than the
number of input documents.

32
 3 "hello"

Aggregation Expression
$add $multiply

$ "$price" price "$[Link]"

● An aggregation pipeline stage may contain aggregation expression in its

specification of computation
● Aggregation expression (a.k.a aggregation pipeline expression)
○ Some aggregation pipeline stages accept expressions. Operators calculate values based on
input expressions.
○ In the MongoDB Query Language, you can build expressions from the following components:
■ Constants, e.g. 3
■ Aggregation operators (a.k.a expression operators), e.g. $add
■ Field path expressions, "$<[Link]>"
○ Aggregation expression can contain additional nested aggregation expressions.

33
Syntax of Pipeline Stages and Expressions
● [Link]([
{ $match: { status: "A" } },
{ $group: { _id: "$cust_id", total: { $sum: "$amount" } } }
]) Aggregation expression
Pipeline of two stages

● [Link]([
{ $project: {item: 1, total: {$add: ["$price", "$fee"]}}}
])
Aggregation expression

Pipeline of two stages

one
Note: there are $ signs here, these are
called field paths expression ($: 把值拿
來用)

34
Aggregation Pipeline Example
● [Link]([
{ $match: { status: "A" } },
{ $group: { _id: "$cust_id", total: { $sum: "$amount" } } }
])
output
● 1st Stage: $match
○ Filters out the documents with the status= "A" and passes to the next stage
● 2nd Stage: $group
○ Groups the documents by the cust_id, and calculate the sum of the amount for each cust_id

MongoDB Similar SQL Note

[Link]([ Select sum(amount) as total Has more operators and is

{ $match: { status: "A" } }, from orders more powerful than RDB
{ $group: { _id: "$cust_id", total: { $sum: "$amount" } } } where status = “A”
]) group by cust_id
35
More on Aggregate Pipeline Stage
● The same pipeline stages can appear multiple times in the pipeline
○ Exception: $out, $merge, and $geoNear
● All available stages
○ There is a large number of available stages
[Link]
tion-pipeline-operator-reference
○ No need to memorize all, but need to know the basic (and the interesting) ones

36
Basic and Interesting Stages (Nonexhaustive)
● Reshaping documents
○ $addFields, $project, $replaceWith
○ $set: add new fields, $unset: delete fields
○ $unwind: break an array, for each element in an array, create a new document
● Basic Aggregate
○ $group, $counts
● Statistics $bucket 0-10 10-20
○ $bucket, $bucketAuto $bucketAuto
know these!
Make sure you
$collStats
○ $collStats
$sample
○ $sample
● Select/Join/Output
○ $match: select, $seach: full text search
○ $lookup: left outer join
○ $unionWith: union of documents from with another collection
○ $merge: incorporate various outputs into a collection
○ $out: output into a collection
○ $sort: sort the output

37
 Aggregation Expression Examples
Expression operators

[Link]( [ [Link]([
{ _id: 1, startTemp: 50, endTemp: 80 }, {
{ _id: 2, startTemp: 40, endTemp: 40 }, $project: { delta: { $abs: { $subtract: [ "$startTemp", "$endTemp" ] } } }
{ _id: 3, startTemp: 90, endTemp: 70 }, }
{ _id: 4, startTemp: 60, endTemp: 70 } ])
])

Output Stage operator Aggregation expression

{ "_id" : 1, "delta" : 30 } $project
{ "_id" : 2, "delta" : 0 }
{ "_id" : 3, "delta" : 20 }
$project delta
{ "_id" : 4, "delta" : 10 }

38
 use the $accumulator and $function
aggregation operators to define custom
More on Aggregation Expressions aggregation expressions in JavaScript.

● Aggregation expression
○ Of document structure
○ Can be nested
○ Can include field paths, literals, system variables, and expression operators.
● Field Paths
○ For accessing fields in the input documents.
○ To specify a field path, prefix the field name or the dotted field name with a dollar sign “$”
■ E.g. "$user" specifies the field path for the user field
■ E.g. "$[Link]" specifies the field path to "[Link]" field.
● Literals
○ Literals can be of any type, representing the written value
○ Note: MongoDB parses string literals that start with a dollar sign $ as a path to a field and
numeric/boolean literals in expression objects as projection flags. To avoid parsing literals, use the
$literal expression.
○ $literal format: { $literal: <value> }. E.g.: { $literal: { $add: [ 2, 3 ] } } is evaluated to { "$add" : [ 2, 3 ] }

39
Aggregation Variables (System Variables)
Variable Brief Description
● Aggregation variables
$$NOW Returns the current datetime value, which remains constant across all members
○ MongoDB provides
of the deployment and throughout the aggregation pipeline. (Available in 4.2+)
various aggregation
system variables for $$CLUSTER_ Returns the current timestamp value, which is same across all members of the
TIME deployment and remains constant throughout the aggregation pipeline. For
use in expressions. To replica sets and sharded clusters only. (Available in 4.2+)
access variables,
$$ROOT References the root document, i.e. the top-level document.
prefix the variable
name with $$. $$CURRENT References the start of the field path, defaults to ROOT but can be changed.

$$REMOVE Allows for the conditional exclusion of fields. (Available in 3.6+)

$$DESCEND One of the allowed results of a $redact expression.

$$PRUNE One of the allowed results of a $redact expression.

$$KEEP One of the allowed results of a $redact expression.

40
 MongoDB uses the terms aggregation expression
operators, aggregation operators, and expression
Expression Operators operators interchangeably.

● Operator expressions are of the form

○ { <operator>: [ <argument1>, <argument2> ... ] }, or
○ { <operator>: <argument> }
● Full list of expression operators
○ Way too many:
[Link]
xpressions

41
 Categories of Expression Operators
Category Example Category Example

Arithmetic $abs, $add, $ceil Miscellaneous $rand, $sampleRate

Array $arrayElemAt, $filter, $map, $reduce Set $setEquals, $setDifference, $setUnion

Boolean $and, $or, $not Object $mergeObjects, $objectToArray, $setField

Comparison $eq, $gt, $gte, $lt, $lte, $ne String $concat, $split, $substr

Conditional $cond, $ifNull, $switch Text $meta

Custom aggreg. ( $accumulator, $function, $where: Trigonometry $sin, $cos, $tan

server-side JS ode) deprecated starting in MongoDB 8.0.
MongoDB logs a warning. Type $convert, $isNumber, $toBool

Data Size $binarySize, $bsonSize Variable $let: set variable in the scope of subexpression

Date $hour, $dayOfMonth, $dayOfWeek Window return values from a defined span of documents

Literal $literal Accumulators See next pages (important!)

42
Accumulator Operators Used in $Group-Like Stages
● Can be used in $bucket, $bucketAuto, $group, $setWindowFields
○ Maintain their state as documents progress through the aggregation pipeline.
○ Return totals, maxima, minima, and other values.

Name Description

$min Returns the lowest expression value for each group.

$max Returns the highest expression value for each group.

$avg Returns an average of numerical values. Ignores non-numeric values.

$stdDevPop Returns the population standard deviation of the input values.

$stdDevSamp Returns the sample standard deviation of the input values.

$sum Returns a sum of numerical values. Ignores non-numeric values.

43
 Accumulator Operators Used in $Group-Like Stages
Name Description

$accumulator Returns the result of a user-defined accumulator function. (deprecated)

$addToSet Returns an array of unique expression values for each group. Order of the array elements is undefined.

$push Returns an array of expression values for each group.

$mergeObjects Returns a document created by combining the input documents for each group.

$first, $firstN Returns a value from the first (n) document for each group. Order is only defined if the documents are in a defined
order.

$last, $lastN Returns a value from the last (n) document for each group. Order is only defined if the documents are in a defined
order.

$bottom, $bottomN, ……
$top, topN

44
Example of Vertical Aggregation ($Group-like)
Input collection:
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:12:00Z") }

[Link]( Output:
[ { "_id" : "xyz", "avgAmount" : 37.5, "avgQuantity" : 7.5 }
{ { "_id" : "jkl", "avgAmount" : 20, "avgQuantity" : 1 }
$group: { "_id" : "abc", "avgAmount" : 60, "avgQuantity" : 6 }
{ group by id CREATE TABLE sales ( INSERT INTO sales (id, item, price,
_id: "$item", id INT, quantity, date) VALUES
item VARCHAR(10), (1, 'abc', 10, 2, '2014-01-01 08:00:00'),
avgAmount: { $avg: { $multiply: [ "$price", "$quantity" ] } }, price DECIMAL(10, 2), (2, 'jkl', 20, 1, '2014-02-03 09:00:00'),
avgQuantity: { $avg: "$quantity" } quantity INT, (3, 'xyz', 5, 5, '2014-02-03 09:05:00'),
date DATETIME (4, 'abc', 10, 10, '2014-02-15 09:00:00'),
} ); (5, 'xyz', 5, 10, '2014-02-15 09:12:00');
}
SELECT
] item,
AVG(price * quantity) AS avgAmount,
) AVG(quantity) AS avgQuantity
FROM
sales
GROUP BY 45
item;
Accumulators Used in Other Stages
● Used in the $project, $addFields, and $set stages
○ Available for use in these stages but not as accumulators. When used in these stages, these
operators do not maintain their state and can take as input either a single argument or multiple
arguments. For details, refer to the specific operator page.
Name Description

$min Returns the lowest expression value for each group.

$max Returns the highest expression value for each group.

$avg Returns an average of numerical values. Ignores non-numeric values.

$sum Returns a sum of numerical values. Ignores non-numeric values.

$stdDevPop, Returns the population (sample) standard deviation of the input values.
$stdDevSamp

… …
46
Example of Horizontal Aggregation ($Project-like)
Input collection:
{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 }
{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 }
{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }

: : item : startTemp endTemp

[Link]([
{ $project: { quizAvg: { $avg: "$quizzes"}, labAvg: { $avg: "$labs" },
examAvg: { $avg: [ "$final", "$midterm" ] } } }
])

Output:
{ "_id" : 1, "quizAvg" : 7.666666666666667, "labAvg" : 6.5, "examAvg" : 77.5 }
{ "_id" : 2, "quizAvg" : 9.5, "labAvg" : 8, "examAvg" : 87.5 }
{ "_id" : 3, "quizAvg" : 4.666666666666667, "labAvg" : 5.5, "examAvg" : 74 }

47
 Aggregate Pipeline Example (1)
Example; membership data of a sports club
Query: List usernames ordered by joined month

{ [Link]( {
_id : "jane", [ "month_joined" : 1,
"name" : "ruth"
joined : ISODate("2011-03-02"), { $project : },
likes : ["golf", "racquetball"] {month_joined: { $month : "$joined" , {
} name : "$_id", _id : 0 } "month_joined" : 1,
{ }, "name" : "harold"
},
_id : "joe", { $sort : { month_joined : 1 } } {
joined : ISODate("2012-07-02"), ] "month_joined" : 1,
likes : ["tennis", "golf", "swimming"] ) "name" : "kate"
} }
{
…... "month_joined" : 2,
"name" : "jill"
}

Data in MongoDB Aggregate pipeline Query output

48
 Aggregate Pipeline Example (2)
Query: Number of people joined each month Note: in $group, “_id” specifies the group key

{ [Link]( {
_id : "jane", [ "_id" : { "month_joined" : 1 },
joined : ISODate("2011-03-02"), { $project : { month_joined : { $month : "number" : 3
likes : ["golf", "racquetball"] "$joined" } } } , },
} { $group : { _id : {
{ {month_joined:"$month_joined"} , "_id" : { "month_joined" : 2 },
_id : "joe", number : { $sum : 1 } } }, "number" : 9
joined : ISODate("2012-07-02"), { $sort : { "_id.month_joined" : 1 } } },
likes : ["tennis", "golf", "swimming"] ] {
} ) "_id" : { "month_joined" : 3 },
…... "number" : 5
Note: Here each _id is a document with }
one fields: month_joined

Data in MongoDB Aggregate pipeline Query output

49
 This query would be very hard to implement
Aggregate Pipeline Example (3) in relational databases!

$unwind: deconstructs an array field from the input

Query: Find top-5 most popular sports in your sport club documents to output a document for each element.

{ [Link]( {
_id : "jane", [ "_id" : "golf", "number" : 33
},
joined : ISODate("2011-03-02"), { $unwind : "$likes" }, {
likes : ["golf", "racquetball"] { $group : { _id : "$likes" , number : { "_id" : "racquetball", "number" : 31
} $sum : 1 } } }, },
{ { $sort : { number : -1 } }, {
"_id" : "swimming", "number" : 24
_id : "joe", { $limit : 5 } },
joined : ISODate("2012-07-02"), ] {
likes : ["tennis", "golf", "swimming"] ) "_id" : "handball", "number" : 19
} },
{
…... "_id" : "tennis", "number" : 18
}

Data in MongoDB Aggregate pipeline Query output

50
Map-Reduce
● An important data processing paradigm
○ For condensing large volumes of data into useful aggregated results.
○ Used to be implemented in large parallel computing hardware/software framework.
○ Hadoop was a very famous implementation of Map-Reduce
● Map-Reduce paradigm contains the following elements:
○ Query phase: select documents in the input collection that match query condition
○ Map phase: apply the map function to each selected input document. The map function emits
key-value pairs.
○ Reduce phase: for each key, collect all values matching that key and condenses the values
into aggregated data.
○ Finalize: Optionally, the output of the reduce function may pass through a finalize function for
further condensation or processing
○ Output: output the result (For MongoDB: stores the results in a collection.)

51
Basic Map-Reduce:
Example
● Starting in MongoDB 5.0, Map and reduce steps may
map-reduce is deprecated. involve many nodes. System
● Map-Reduce function should be takes care of distributed
implemented using aggregation processing for you
pipeline - for better performance
and usability.
● Useful aggregation pipeline
stages: $group, $merge, and
others.

52
 Map-Reduce Example 1 (mapReduce Method)
Example: a sales database
Query: Return the total revenue for each customer
[Link]([
{ _id: 1, cust_id: "Ant O. Knee", ord_date: new Date("2020-03-01"), price: 25, items: [ { sku: "oranges",
qty: 5, price: 2.5 }, { sku: "apples", qty: 5, price: 2.5 } ], status: "A" }, var mapFunction1 = function() {
{ _id: 2, cust_id: "Ant O. Knee", ord_date: new Date("2020-03-08"), price: 70, items: [ { sku: "oranges",
qty: 8, price: 2.5 }, { sku: "chocolates", qty: 5, price: 10 } ], status: "A" }, emit(this.cust_id, [Link]);
{ _id: 3, cust_id: "Busby Bee", ord_date: new Date("2020-03-08"), price: 50, items: [ { sku: "oranges", qty: };
10, price: 2.5 }, { sku: "pears", qty: 10, price: 2.5 } ], status: "A" }, var reduceFunction1 = function(keyCustId,
{ _id: 4, cust_id: "Busby Bee", ord_date: new Date("2020-03-18"), price: 25, items: [ { sku: "oranges", qty:
10, price: 2.5 } ], status: "A" }, valuesPrices) {
{ _id: 5, cust_id: "Busby Bee", ord_date: new Date("2020-03-19"), price: 50, items: [ { sku: "chocolates", return [Link](valuesPrices);
qty: 5, price: 10 } ], status: "A"}, };
{ _id: 6, cust_id: "Cam Elot", ord_date: new Date("2020-03-19"), price: 35, items: [ { sku: "carrots", qty:
10, price: 1.0 }, { sku: "apples", qty: 10, price: 2.5 } ], status: "A" }, [Link](
{ _id: 7, cust_id: "Cam Elot", ord_date: new Date("2020-03-20"), price: 25, items: [ { sku: "oranges", qty: mapFunction1,
10, price: 2.5 } ], status: "A" }, reduceFunction1,
{ _id: 8, cust_id: "Don Quis", ord_date: new Date("2020-03-20"), price: 75, items: [ { sku: "chocolates",
qty: 5, price: 10 }, { sku: "apples", qty: 10, price: 2.5 } ], status: "A" }, { out: "map_reduce_example" }
{ _id: 9, cust_id: "Don Quis", ord_date: new Date("2020-03-20"), price: 55, items: [ { sku: "carrots", qty: 5, )
price: 1.0 }, { sku: "apples", qty: 10, price: 2.5 }, { sku: "oranges", qty: 10, price: 2.5 } ], status: "A" }, db.map_reduce_example.find().sort( { _id:
{ _id: 10, cust_id: "Don Quis", ord_date: new Date("2020-03-23"), price: 25, items: [ { sku: "oranges", qty: 1})
10, price: 2.5 } ], status: "A" }
])

Map-reduce command
Data in MongoDB implementation 53
 Map-Reduce Example 1-1 (Pipeline)
Return the total revenue for each customer $out: takes the documents returned by the aggregation
pipeline and writes them to a specified collection.

Same as previous slides [Link]([ { "_id" : "Ant O. Knee", "value" : 95 }

{ $group: { _id: "$cust_id", value: { $sum: { "_id" : "Busby Bee", "value" : 125 }
"$price" } } }, { "_id" : "Cam Elot", "value" : 60 }
{ $out: "agg_alternative_1" } { "_id" : "Don Quis", "value" : 155 }
])

db.agg_alternative_1.find().sort( { _id: 1 } )

Data in MongoDB Aggregate pipeline Query output

54
 Map-Reduce Example 2 (MapReduce Method)
Calculate total quantity, number of orders, and average quantity per
order for each product. Only consider orders after 2020-03-01

Same as previous slides var mapFunction2 = function() { [Link](

for (var idx = 0; idx < [Link]; idx++) { mapFunction2,
var key = [Link][idx].sku; reduceFunction2,
Same as previous slides var value = { count: 1, qty: [Link][idx].qty }; {
emit(key, value); out: { merge: "map_reduce_example2" },
}
}; query: { ord_date: { $gte: new
var reduceFunction2 = function(keySKU, countObjVals) { Date("2020-03-01") } },
reducedVal = { count: 0, qty: 0 }; finalize: finalizeFunction2
for (var idx = 0; idx < [Link]; idx++) { }
[Link] += countObjVals[idx].count; );
[Link] += countObjVals[idx].qty;
} db.map_reduce_example2.find().sort( { _id: 1
return reducedVal; })
};
var finalizeFunction2 = function (key, reducedVal) {
[Link] = [Link]/[Link];
return reducedVal;
};

Data in MongoDB Map-reduce command

implementation 55
 would be extremely hard to
Map-Reduce Example 2-1 (Pipeline) implement in relational databases!

Calculate total quantity, number of orders, and average quantity per

order for each product. Only consider orders after 2020-03-01

Same as previous [Link]( [ { "_id" : "apples", "value" : { "count" : 4, "qty" :

{ $match: { ord_date: { $gte: new Date("2020-03-01") } } }, 35, "avg" : 8.75 } }
slides { "_id" : "carrots", "value" : { "count" : 2, "qty"
{ $unwind: "$items" }, : 15, "avg" : 7.5 } }
{ $group: { _id: "$[Link]", qty: { $sum: "$[Link]" }, { "_id" : "chocolates", "value" : { "count" : 3,
orders_ids: { $addToSet: "$_id" } } }, "qty" : 15, "avg" : 5 } }
{ $project: { value: { count: { $size: "$orders_ids" }, qty: { "_id" : "oranges", "value" : { "count" : 7,
"$qty", avg: { $divide: [ "$qty", { $size: "$orders_ids" } ] } } } }, "qty" : 63, "avg" : 9 } }
{ $merge: { into: "agg_alternative_3", on: "_id", { "_id" : "pears", "value" : { "count" : 1, "qty" :
10, "avg" : 10 } }
whenMatched: "replace", whenNotMatched: "insert" } }
])
db.agg_alternative_3.find().sort( { _id: 1 } )

Data in
MongoDB Aggregate pipeline Query output
$match: filters the documents to pass only the $merge: merge the results of the aggregation pipeline to a specified
documents that match the specified condition(s) to collection. The $merge operator must be the last stage in the pipeline. If
the next pipeline stage. the specified collection does not exist, create the collection. 56
Incremental Map-Reduce
● If the map-reduce data set is constantly growing
○ ⇒ you may want to perform an incremental map-reduce rather than performing the
map-reduce operation over the entire data set each time.
● To perform incremental map-reduce:
○ 1. Run a map-reduce job over the current collection and output the result to a separate
collection.
○ 2. When you have more data to process, run subsequent map-reduce jobs with:
■ the query parameter that specifies conditions that match only the new documents.
■ the out parameter that specifies the reduce action to merge the new results into the
existing output collection.

57
 var mapFunction = function() { var finalizeFunction = function(key,
var key = [Link]; reducedValue) {
Incremental var value = { total_time: [Link], count: 1, if ([Link] > 0)
reducedValue.avg_time =
avg_time: 0 };
MapReduce emit( key, value );
reducedValue.total_time / [Link];

Example };
};
return reducedValue;

Collect computer user sessions information: total time,

number of session, average time per session
[Link](
mapFunction,
[Link]( { userid: "a", ts:
var reduceFunction = function(key, values) { reduceFunction,
ISODate('2011-11-03 14:17:00'), length: 95 } ); {
[Link]( { userid: "b", ts: var reducedObject = { total_time: 0, count:0, out: "session_stats",
ISODate('2011-11-03 14:23:00'), length: 110 } ); avg_time:0 }; finalize: finalizeFunction
[Link]( { userid: "c", ts: }
ISODate('2011-11-03 15:02:00'), length: 120 } ); )
[Link](function(value) {
[Link]( { userid: "d", ts: reducedObject.total_time += value.total_time;
ISODate('2011-11-03 16:45:00'), length: 45 } );
[Link] += [Link]; [Link](
[Link]( { userid: "a", ts:
}); mapFunction,
ISODate('2011-11-04 11:05:00'), length: 105 } ); reduceFunction,
[Link]( { userid: "b", ts: return reducedObject; {
ISODate('2011-11-04 13:14:00'), length: 120 } ); }; query: { ts: { $gte: ISODate('2020-03-05
[Link]( { userid: "c", ts: 00:00:00') } },
ISODate('2011-11-04 17:00:00'), length: 130 } ); out: { reduce: "session_stats" },
[Link]( { userid: "d", ts: finalize: finalizeFunction
ISODate('2011-11-04 15:37:00'), length: 65 } ); }
); Incremental
Map-reduce 58
 Incremental Map-Reduce Example (2) (Pipeline)
Collect computer user sessions information: total time, number of
The time of the first new data
session, average time per session (Aggregate Pipeline implementation)

Same as previous [Link]([ count: { $sum: 1 } 1 { "_id" : "a", "value" : {

{ $match: { ts: { $gte: ISODate('2020-03-05 00:00:00') } } }, "total_time" : 200, "count" :
slides { $group: { _id: "$userid", total_time: { $sum: "$length" }, count: { $sum: 1 }, 2, "avg_time" : 100 } }
avg_time: { $avg: "$length" } } }, { "_id" : "b", "value" : {
{ $project: { value: { total_time: "$total_time", count: "$count", avg_time: "$avg_time" } } },
{ $merge: { "total_time" : 230, "count" :
into: "session_stats_agg", 2, "avg_time" : 115 } }
whenMatched: [ { $set: { { "_id" : "c", "value" : {
"value.total_time": { $add: [ "$value.total_time", "$$[Link].total_time" ] }, "total_time" : 250, "count" :
"[Link]": { $add: [ "$[Link]", "$$[Link]" ] }, 2, "avg_time" : 125 } }
"value.avg_time": { $divide: [ { $add: [ "$value.total_time", "$$[Link].total_time" ] }, { "_id" : "d", "value" : {
{ $add: [ "$[Link]", "$$[Link]" ] } ] } "total_time" : 110, "count" :
} } ], 2, "avg_time" : 55 } }
whenNotMatched: "insert"
}}
])
db.session_stats_agg.find().sort( { _id: 1 } )

Data in MongoDB Aggregate pipeline Query output

59
 Incremental aggregation
Incremental Map-Reduce Example (3) pipelines are also called an
On-demand materialized views
Optional: To avoid having to modify the aggregation pipeline's $match date condition
each time you run, you can wrap the aggregation in a helper function
Helper function
Same as previous updateSessionStats = function(startDate) { { "_id" : "a", "value" : {
[Link]([ "total_time" : 200, "count" :
slides { $match: { ts: { $gte: startDate } } },
{ $group: { _id: "$userid", total_time: { $sum: "$length" }, count: { $sum: 1 }, avg_time: { $avg: 2, "avg_time" : 100 } }
"$length" } } }, { "_id" : "b", "value" : {
{ $project: { value: { total_time: "$total_time", count: "$count", avg_time: "$avg_time" } } }, "total_time" : 230, "count" :
{ $merge: { 2, "avg_time" : 115 } }
into: "session_stats_agg",
whenMatched: [ { $set: { { "_id" : "c", "value" : {
"value.total_time": { $add: [ "$value.total_time", "$$[Link].total_time" ] }, "total_time" : 250, "count" :
"[Link]": { $add: [ "$[Link]", "$$[Link]" ] }, 2, "avg_time" : 125 } }
"value.avg_time": { $divide: [ { $add: [ "$value.total_time", "$$[Link].total_time" ] }, { { "_id" : "d", "value" : {
$add: [ "$[Link]", "$$[Link]" ] } ] }
} } ], "total_time" : 110, "count" :
whenNotMatched: "insert" 2, "avg_time" : 55 } }
}}
]);
};
updateSessionStats(ISODate('2020-03-05 00:00:00'))

Data in MongoDB Aggregate pipeline Query output

60
What Really Happened
● In MongoDB, all queries and commands are actually written as JSON
documents
● And send as argument to methods as such runCommand() and
adminCommand()
○ To run a command against the current database, use [Link]():
■ [Link]( { <command> } )
○ To run an administrative command against the admin database, use [Link]():
■ [Link]( { <command> } )
● The commands we saw were actually “helper functions”, a coat of software to
make the query coding look “nicer”
○ Helper functions: [Link](), [Link](), ….

61
Example: Real Find, Insert and Delete
[Link]( [Link]( [Link](
{ { {
find: <collection>, insert: <collection>, delete: <collection>,
filter: <document>, documents: [ <document>, <document>, deletes: [
sort: <document>, <document>, ... ], {
projection: <document>, ordered: <boolean>, q : <query>,
hint: <document or string>, maxTimeMS: <integer>, limit : <integer>,
skip: <int>, writeConcern: { <write concern> }, collation: <document>,
limit: <int>, bypassDocumentValidation: <boolean>, hint: <document|string>
batchSize: <int>, comment: <any> },
singleBatch: <bool>, } ...
comment: <any>, ) ],
maxTimeMS: <int>, comment: <any>,
readConcern: <document>, let: <document>, // Added in MongoDB 5.0
max: <document>, ordered: <boolean>,
min: <document>, writeConcern: { <write concern> },
returnKey: <bool>, maxTimeMS: <integer>
showRecordId: <bool>, }
tailable: <bool>, )
oplogReplay: <bool>,
noCursorTimeout: <bool>,
awaitData: <bool>,
…….
}
) 62