21MongoDBQueryLanguage

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

MongoDB query language is based on a concept of pattern matching.

A query is expressed as a pattern and all documents that match the pattern are included in an answer.

A pattern must be a well formed BSON document.

A method "find()" is used to match a pattern with the documents in a collection. In an example below a BSON document

{"_id":"ALFKI"}

represents a pattern where a document identifier, i.e. a key "_id" is associated with a value (string) "ALFKI". A patterns is used as an argument of a method "find()" applied to a collection orders:

db.orders.find({"_id":"ALFKI"})

All documents that match the pattern i.e. a a document that has a key "_id" associated with a value "ALFKI" is included in the result of a query.

Matching of an empty pattern (empty document) { } with a collection "orders" returns as the result an entire collection, i.e. all documents in. a collection "orders".

The following query finds all documents in a collection "orders"

db.orders.find({})

It is possible to limit the total number of documents included into an answer. For example, a method "limit()" with with an argument 3 restricts the total number of documents selected from a collection "orders" to the first 3 documents.

db.orders.find({}).limit(3)

MongoDB query language

MongoDB query language is based on a concept of pattern matching
A query is expressed as a BSON pattern and all documents that match the pattern are included in an answer
A method find() can be used to match a pattern with the documents in a collection orders
Matching of an empty pattern { } with a collection orders returns an entire collection
Finding the first 3 documents in a collection orders

MongoDB Query language

A sample database

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

A conceptual schema given in the previous slide is transformed in a process of logical design into a logical schema of a sample database.

A logical schema consists of a single collection "orders" with two types of documents:

- the documents that contain information about suppliers and products supplied by suppliers and

- the documents that contain information about customers, orders submitted by customers and contents of orders submitted by customers.

A document that contains information about a supplier and products supplied by a supplier has a two-level nested structure.

Information about a supplier is included in the root of a hierarchy and information about products supplied by a supplier is nested at a lower level of a hierarchy within a description of a supplier. In a logical schema, such two-level nested structure is represented by a vertically oriented one-to-many association between supplier component of a document and nested products component of a document.

A document that contains information about a customer, orders submitted by a customer and contents of each order has a three-level nested structure.

Information about a customer is included in the root of a hierarchy. Information about the orders submitted by a customer is nested within a description of a customer. Information about the contents of each order is nested within a description of an order at the third level in a hierarchy. In a logical schema, such three level nested structure is represented by a vertically oriented sequence of one-to-many associations between the descriptions of customers, orders, and contents of each order.

A fact that both types of documents CUSTOMER and SUPPLIER are included in a single collection "orders" represent by inclusion of both hierarchies into a blob with a label "orders" in its left upper corner.

A formal specification of a transformation of conceptual schema into a logical schema of a collection will be presented in the next presentation.

A sample database

A sample collection orders

This slide presents a sample document from a collection "orders". The documents describes a customer identified by a customer code "ALFKI". The same value is associated with a key "_id" in order to allow for fast search over a customer code as "_id" is automatically indexed by the system.

The topmost level in the documents a key "CUSTOMER" is associated with a document containing a description of a customer. Inclusion of a key "CUSTOMER" allows for support of "self-describing" structure of a document with information about a customer. It measn that no information from a conceptual schema is lost in a process of its transformation into a logical schema.

A description of a customer (a document associated with a key "CUSTOMER") contains the values of properties such as customer code, company name, contact name , ... etc. The orders submitted by a customer are represented by the documents within an array associated with a key "submits". In the present example a customer submitted no orders and an array of orders is empty.

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" : [ ]
        }
}

The current slide shows a skeleton of a document describing a customer who submitted several orders. To save space and to avoid very small font some of key: value pars are replaced with ... ... ... ... ... .

The orders submitted by a customer are represented by the documents associated with a key "ORDER" and stored in an array associated with a key "submits". A description of an order is represented by a document associated with a key "ORDER DETAIL" and stored in an array associated with a key "consists of". The keys "submits" and "consists of" represent information about associations in a conceptual schema. The keys "CUSTOMER", "ORDER" and "ORDER DETAILS" represent a class "CUSTOMER", a class 'ORDER" and an association class "ORDER DETALS" in a conceptual schema. One-to-many association is implemented as an array of documents associated with a key taken from a name of the association. More information about formal process of logical design that transforms a conceptual schema into a logical schema of a collection is provided in the next presentation.

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 current slide shows a skeleton of a document describing a supplier who supplies a number of products. To save space and to avoid very small font some of key: value pars are replaced with ... ... ... ... ... .

The products supplied by a supplier are represented by the documents associated with a key "PRODUCT" and stored in an array associated with a key "supplies". A keys "supplies" represent information about associations in a conceptual schema. The keys "SUPPLIER" and "PRODUCT" represent a class 'CUSTOMER" and a class "PRODUCT" in a conceptual schema. One-to-many association is implemented as an array of documents associated with a key taken from a name of the association. More information about formal process of logical design that transforms a conceptual schema into a logical schema of a collection is provided in the next presentation.

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 Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

We start from the simple queries. Assume that a default database has been selected with "use" command and that the database contains a collection "orders" described in the previous slides

To find the total number of documents in a collection "orders" we apply a method ""count()" a collection orders in the following way.

db.orders.count()

To find information about all customers we must construct a query template such that all documents that have a key "CUSTOMER" in the topmost level of nesting of the document match the template. To check if a give key exists in a document we use an operator "$exists" associated with a value "true". Then a document {$exists:true} is associated as a value with a key "CUSTOMER".

db.orders.find({"CUSTOMER":{$exists:true}})

All documents in a collection "orders" that have a key "CUSTOMER" at the topmost level of nesting match a query template.

In the same way we create a query template that matches all documents in a collection "orders" that have a key "SUPPLIER" at the topmos leel ina document,

i.e that contain information about a supplier.

db.orders.find({"SUPPLIER":{$exists:true}})

All documents in a collection "orders" that have a key "SUPPLIER" at the topmost level of nesting match a query template.

In the next query we find all information about the customers living in Germany. A query template containth a path "CUSTOMER.country" that is associated with a string "Germany".

It means that whenever a document has a key "CUSTOMER" and a document associted with the key has a key "country" associated with a string

"Germany" then such document matches a query template.

db.orders.find({"CUSTOMER.country":"Germany"})

In the next query we find information about supliers living ina city Oviedo.

A query template consists of a key "SUPPLIER" to find the suppliers and a key "city" associated with a tring "Oviedo". All documents

that have a path "SUPPLIER.city" asociated with a value "Oviedo" are include into an answer.

db.orders.find({"SUPPLIER.city":"Oviedo"})

In the last query on the presnet slide we find all information about the suppliers who live in the Netherlands and have a contact title Accounting Manager

This time we construct two paths "SUPPLIER.country" associated with a value "Netherlands" and "SUPPLIER. contact title" associated with a value "Accountig Manager".

All documents that hve such paths asscitaed with the appropriate values are included into an answer.

db.orders.find({"SUPPLIER.country":"Netherlands","SUPPLIER.contact title":"Accounting Manager"})

Note that one of the ways to express a condition being a conjunction of elementary conditions is to provide two or more paths associate with the values and separated with a comma.

It is a template of a document that has both paths associted with the given values.

Simple queries

Find total number of documents in a collection

Find all information about all customers

db.orders.find({"CUSTOMER":{$exists:true}})

Find all information about all suppliers

db.orders.find({"SUPPLIER":{$exists:true}})

Find all information about the customers living in Germany
Find all information about the suppliers living in a city of Oviedo
Find all information about the suppliers who live in the Netherlands and have a contact title Accounting Manager

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

Now we look at the ways how to express elementary conditions and how to create Booleam expressions in a language of query templates of MongoDB.

Assume that we would like to express a condition like 'fnd all document where a key "key" is associated with a value "value", i.e. an elementary condition "key=value".

The simplesr way is to create a key value pair "key":"value". Then by defalut a relational operator is "=". If we do not want to use a default operator then wecan express it is

explicitly with "$eq" in the following way.

{"key":{$eq:"value"}}

Note that "key" must be associated with an atomic value or with a document. It is why in this case we must provide a document {$eq:"value"} to be associated with "key".

It is possible to use different operator to express other condititin different from equality condition. For example, a comparison "key" > "value" can be expressed in the following way with "$gt" operator.

{"key":{$gt:"value"}}

A disjunction of elementary conditions can be expressed with an operator "$or" associated with an array of documents representing the terms of disjunction.

For example a disjunction ("key1"="value1") or ("key2"="value2") is represented in the following way.

{$or:[{"key1":"value1"},{"key2":"value2"}]}

The same method can be used to represent a conjunction. An operator "$and" is associated with an array of elementary terms of conjunction

For example a conjunction ("key1"="value1") and ("key2"="value2") is represented in the following way.

{$and:[{"key1":"value1"},{"key2":"value2"}]}

If one of the terms is a subexpression then such subexpression is represented as a document where a Boolean operator is associated with an array of lementary terms, i.e. key-value pairs.

For example, a Boolean expression (("key1"="value1") or ("key2"="value2")) and ("key3"="value3") i represnetd in the following way.

{$and:[{$or:[{"key1":"value1"},{"key2":"value2"}]},{"key3":"value3"}]}

It is a conjunction of two terms: {"key3":"value3"} and {$or:[{"key1":"value1"},{"key2":"value2"}]}. The second term is a disjunction

that also consists of two terms: {"key1":"value1"}, {"key2":"value2"}.

Queries with Boolean operations

Comparison "key"="value"
Comparison "key" > "value"
Disjunction ("key1"="value1") or ("key2"="value2")
Conjunction ("key1"="value1") and ("key2"="value2")

Boolean expression (("key1"="value1") or ("key2"="value2")) and ("key3"="value3")

 	      
{$and:[{$or:[{"key1":"value1"},{"key2":"value2"}]},{"key3":"value3"}]}

Queries with Boolean operations

Negation of a comparison "key" not ="value"
Negation of an expression not (("key1"="value1") or ("key2"="value2"))
Negation nor ("key1"="value1")

In this slide we look at the implementations of queries with Boolean operators.

First we implement a query that finds all information about the suppliers who live in the Netherlands and have a contact title Accounting Manager

This time we use an operator "$and" to implement a query.

A condition "... live in the Netherlands..." is implemented as a key-value pair {"SUPPLIER.country":"Netherlands"} and a condition "... have a contact title Accounting Manager..."

is implemented as a key-value pair {"SUPPLIER.contact title":"Accounting Manager"}. The implementations of elementary terms are included in an arraay

associated with and operator "$and".

db.orders.find({$and:[{"SUPPLIER.country":"Netherlands"},{"SUPPLIER.contact title":"Accounting Manager"}]})

In the next query we replace an operator "$and" with an operator "$or". We implement a query that finds all information about the suppliers who live in the Netherlands or have a contact title Accounting Manager

db.orders.find({$or:[{"SUPPLIER.country":"Netherlands"},{"SUPPLIER.contact title":"Accounting Manager"}]})

Next, we implement a query that finds all information about the customers who live in France or in Germany. This time we do not use "$or"operator. Instead we use a set membership operator "$in".

db.orders.find({"CUSTOMER.country":{$in:["France","Germany"]}})

Queries with Boolean operations

Find all information about the suppliers who live in the Netherlands and have a contact title Accounting Manager

 
db.orders.find({$and:[{"SUPPLIER.country":"Netherlands"},
	              {"SUPPLIER.contact title":"Accounting Manager"}]})

Find all information about the suppliers who live in the Netherlands or have a contact title Accounting Manager

 	    
db.orders.find({$or:[{"SUPPLIER.country":"Netherlands"},
	             {"SUPPLIER.contact title":"Accounting Manager"}]})

Find all information about the customers who live in France or in Germany

In this slide we look at the implementations of queries with negated Boolean operators.

First we implement a query that finds all information about the customers who do not live in Germany. If a customer does not live in Geremany thena path "CUSTOMER.country" must be associated with a value not equla to a string "Germany".

Such condition can be expressed as a negation of "$eq"m operator with and operator "$not" in the following way.

db.orders.find({"CUSTOMER.country":{$not:{$eq:"Germany"}}})

In the next query we find all information about the customers who do not both live in Netherlands or have a contact title Accounting Manager.

Such can aslo be expressed as find all information about the customers who do not live in Netherlands and do not have a contact title Accounting Manager.

It means that we should implement a search condition $and:[ {"SUPPLIER.country":{$not:{$eq:"Netherlands"}}},{"SUPPLIER.contact title":{$not:{$eq:"Accounting Manager"}}}].

Application of De Morgan's Law (not p and not q) being equivalent to not(p or q) allows fro simplification of a search condition with an operator $nor.

db.orders.find({$nor:[{"SUPPLIER.country":"Netherlands"},{"SUPPLIER.contact title":"Accounting Manager"}]})

An operator $nor can also be used to implement a query find all information about the customers who do not live in Germany in a simple way as follows.

db.orders.find({$nor:[{"CUSTOMER.country":"Germany"}]})

It is consistent with the following transfromations of Boolean expression

First p is equivalent to p or false, then not (p or false) is equlivalent to not(p) and true, which is equivalent to not(p).

Queries with Boolean operations

Find all information about the customers who do not live in Germany
Find all information about the customers who do not both live in Netherlands or have a contact title Accounting Manager
Find all information about the customers who do not live in Netherlands and do not have a contact title Accounting Manager
Find all information about the customers who do not live in Germany

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

Before presentation of queries on nested documents we review a logical schema of a sample database.

A logical schema consists of a single collection "orders" that contains two types of documents:

- the documents that contain information about suppliers and products supplied by suppliers

- the documents that contain information about customers, orders submitted by customers and contents of orders submitted by customers.

A document that contain information about a supplier and products supplied by a supplier is a two-level nested structure.

Information about a supplier is included in the root of a hierarchy and information about products supplied by a supplier is nested at a lower level of a hierarchy within a description of a supplier. In logical schema, such two-level nested structure is represented by a vertically oriented one-to-many association between supplier component of a document and nested products component of a document.

A document that contain information about a customer, orders submitted by a customer and contents of each order is three-level nested structure.

Queries on nested documents

A sample collection orders

The present slide show three queries that use "long paths" to formulate search condition. We start from a query that finds all information about suppliers who supply a product named Laughing Lumberjack Lager.

To create a condition where a key "product name" is associated with a string "Laughing Lumberjack Lager" we must provide a path in a nested document describing a supplier from root to a key "product name".

To ctreate such path we look at a logical schema of a sample collection given in the previous slide (slide 18). To get to a product name we must pass thropugh a class "SUPPLIER" then through an association "supplies" then access a clss "PRODUCT" and pick a property "product name".

Such "travel" through the classes and association contributes to a path "SUPPLIER.supplies.PRODUCT.product name". To create a search condition we associate a path with a string "Laughing Lumberjack Lager".

The final version of a query is the following.

db.orders.find({"SUPPLIER.supplies.PRODUCT.product name":"Laughing Lumberjack Lager"})

The next query retrieves information about suppliers living in London who supply a product named Chai.

We construct to paths. The first one implements condition "living in London" and the second implements a condition :"who supply product named Chai. The first condition needs a short path "SUPPLIER.city" associated with a string "London.

The second condition needs a bit longer path "SUPPLIER.supplies.PRODUCT.product name" associated with a string "Chai". Then we build a conjunction of both paths. The final version of the query is as follows.

db.orders.find({$and:[{"SUPPLIER.city":"London"},{"SUPPLIER.supplies.PRODUCT.product name":"Chai"}]})

The next query is an extension of the previous query,. We would like to find all information about suppliers living in London who supply a product named Chai or a product named Chang.

The first path implementing a condition "living in London" is the same as before. Then, we create to identical paths "SUPPLIER.supplies.PRODUCT.product name" and associate the first one

with a string "Chai" and the second with a string "Chang". The paths are joined using "$or" operator and later on joined with the first path using $and operator. A complete query is given below.

db.orders.find({$and:[{"SUPPLIER.city":"London"},{$or:[{"SUPPLIER.supplies.PRODUCT.product name":"Chai"},{"SUPPLIER.supplies.PRODUCT.product name":"Chang"}]}]})

Application of set membership operator "$in" allows for simplification of a query due to elimination pf repeated path "SUPPLIER.supplies.PRODUCT.product name" in the first query.

db.orders.find({$and:[{"SUPPLIER.city":"London"},{"SUPPLIER.supplies.PRODUCT.product name":{$in:["Chai","Chang"]}}]})

Queries on nested documents

Find all information about suppliers who supply a product named Laughing Lumberjack Lager

 
db.orders.find({"SUPPLIER.supplies.PRODUCT.product name":"Laughing Lumberjack Lager"})

Find all information about suppliers living in London who supply a product named Chai

 
db.orders.find({$and:[{"SUPPLIER.city":"London"},
                      {"SUPPLIER.supplies.PRODUCT.product name":"Chai"}]})

Find all information about suppliers living in London who supply a product named Chai or a product named Chang

 
db.orders.find({$and:[{"SUPPLIER.city":"London"},
                      {$or:[{"SUPPLIER.supplies.PRODUCT.product name":"Chai"},
                            {"SUPPLIER.supplies.PRODUCT.product name":"Chang"}]}]})

db.orders.find({$and:[{"SUPPLIER.city":"London"},
                      {"SUPPLIER.supplies.PRODUCT.product name":{$in:["Chai","Chang"]}}]})

The next query finds all information about suppliers living in London who supply a product named Chai and a product named Chang. The only difference between the present query and the previous one

is operator "$and" used now. A document that describes a supplier who supplies both products Chai and Chang must have both paths "SUPPLIER.supplies.PRODUCT.product name" associated with a string "Chai" and

"SUPPLIER.supplies.PRODUCT.product name" associated with a string "Chang". A complete query is the following.

db.orders.find({$and:[{"SUPPLIER.city":"London"},{"SUPPLIER.supplies.PRODUCT.product name":"Chai"},{"SUPPLIER.supplies.PRODUCT.product name":"Chang"}]})

The next query finds all information about suppliers who supply at least one product. To be a supplier who supplies at least one product a documnt that represents a supplier must

have a key "supplies" and such key must be associated with a nonempty array. A condition that equires existence of a key "supplies"

can be expressed in a well know way as {"SUPPLIER.supplies":{$exists:true}}. The second condition requeires an array associted witha key "supplies"

to be not empty, i.e. {"SUPPLIER.supplies":{$ne:[]}}. Then we implement a conjunction of these conditions and a query is ready.

db.orders.find({$and:[{"SUPPLIER.supplies":{$exists:true}},{"SUPPLIER.supplies":{$ne:[]}}]})

The next query finds all supplier who supply no products. In this case the second condition must associate a path "SUPPLIER.supplies" with an empty array.

db.orders.find({$and:[{"SUPPLIER.supplies":{$exists:true}},{"SUPPLIER.supplies":{$eq:[]}}]})

We assume that if a supplier supplies no products then a document representing such supplier must have a key "supplies".

It is possible that a document that contains information about a supplier does not have exit

a key "supplies". In such a caseit is a matter of interpretation what does a missing key "supplies". Does it mean that

a supplies supplies no products ? or maybe it means. that we do not know if a supplier supplies no products. Interpretation of a missing key is up to a database designer.

It is a similar situation as with interpretation of NULL in the entries of relational tables.

In the last example, we create a long path to find all information about customers who submitted an order for at least one

product called Flotemysost. Again we look at a logical schema and we find that to gett to a product name in a class "ORDER DETAIL" we must bass through a class

"CUSTOMER", then through an association "submits", then through a class "ORDER", then through and association "consists of", then through a class "ORDER DETAIL

to get to a key "product name". So a path is "CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name" and it should be associated with a string

"Flotemysost". A complete query is given below.

db.orders.find({"CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name":"Flotemysost"})

Queries on nested documents

Find all information about suppliers living in London who supply a product named Chai and a product named Chang

db.orders.find({$and:[{"SUPPLIER.city":"London"},
                      {"SUPPLIER.supplies.PRODUCT.product name":"Chai"},
                      {"SUPPLIER.supplies.PRODUCT.product name":"Chang"}]})

Find all information about suppliers who supply at least one product

 
db.orders.find({$and:[{"SUPPLIER.supplies":{$exists:true}},
                      {"SUPPLIER.supplies":{$ne:[]}}]})

Find all information about suppliers who do not supply any products

 
db.orders.find({$and:[{"SUPPLIER.supplies":{$exists:true}},
                     {"SUPPLIER.supplies":{$eq:[]}}]})

Find all information about customers who submitted an order for at least one product Flotemysost

 
db.orders.find({"CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.product name":"Flotemysost"})

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

In this section we show how to express the conditions on the values being arrays.

Assume that we have a key "array" and such key is associated with an array of numbers. A condition that compares

a value associated with a key "array" on equality with a given array of numbers [1,2,3,4,5] is the following.

{"array":{$all:[1,2,3,4,5]}}

All documents that have a key "array" associated with an array of numbers [1,2,3,4,5] validates well agains a template given above.

The next condition that is frequently used is verification if an array contains a given element.

For example if we would like to check if an array associated with a key "array" contains an element that satisfies a given condition

The following example checks if an array associated with a key "array" contains at least one element equal to 2.

{"array":{$elemMatch:{$eq:2}}}

The following example checks if an array associated with key "array" contains at least one element that is greater than 2 and less than 4.

{"array":{$elemMatch:{$gt:2,$lt:4}}}

The following example checks if an array contains a document that satisfies a condition such that it contains

key "key" associated with a value 2.

{"array":{$elemMatch:{"key":{$eq:2}}}}

In the next example, we check if an aray contains a document that satisfies a condition where a value associated with a key "key" is greater than 2 and less than 4.

{"array":{$elemMatch:{"key":{$gt:2,$lt:4}}}}

Testing if an array contains 5 elements can be done in the following way.

{"array":{$size:5}}

Testing if an array contains no elements, i.e. an array is empty can be done in the following way

{"array":{$size:0}}

or in the following way.

{"array":[]}

Queries on arrays

Array equal to [1,2,3,4,5]

Array includes an element that satisfies a condition

{"array":{$elemMatch:{$eq:2}}}

{"array":{$elemMatch:{$gt:2,$lt:4}}}

Array includes a document satisfies a condition

{"array":{$elemMatch:{"key":{$eq:2}}}}

{"array":{$elemMatch:{"key":{$gt:2,$lt:4}}}}

Size of an array

{"array":{$size:5}}

{"array":{$size:0}}

{"array":[]}

Now we look at more complex queries using conditions involving arrays and elements in arrays.

We start from a query "find all information about customers who submitted an order that contains only a product Boston Crab Meat

at unit price 14.7 in quantity 20 with discount equal to 0.

In this case we compare the contents of an array associated with a key "consists of" with a given document.

An implementation of the query is the following.

{$eq:[{"ORDER DETAIL":{"product name":"Boston Crab Meat","unit price":14.7,"quantity":20,"discount":0}}]}})

Next we implement a query that finds all information about suppliers such that the second supplied product is named Chang.

The second supplied product means that we have to check if an element with index 1 in an array associated with a key "supplies" is a product

that has a name Chang. The second element has an index 1 because the first element has an index 0.

A query is implemented in the following way.

db.orders.find({"SUPPLIER.supplies.1.PRODUCT.product name":"Chang"})

We extend the previous query and we try to find all information about suppliers such that the first supplied product is named Chai

and the second supplied product is named Chang.

A sample solution is given below.

db.orders.find({$and:[{"SUPPLIER.supplies.0.PRODUCT.product name":"Chai"},

{"SUPPLIER.supplies.1.PRODUCT.product name":"Chang"}]})

Queries on arrays

Find information about all customers who submitted an order that contains only a product Boston Crab Meat at unit price 14.7 in quantity 20 with discount equal to 0

db.orders.find({"CUSTOMER.submits.ORDER.consists of":
	                          {$eq:[{"ORDER DETAIL":{"product name":"Boston Crab Meat",
                                                         "unit price":14.7,
                                                         "quantity":20,
                                                         "discount":0}}]}})

Find information about all suppliers such that their second supplied product is named Chang

Find information about all suppliers such that their first supplied product is named Chai and the second supplied product is named Chang

 
db.orders.find({$and:[{"SUPPLIER.supplies.0.PRODUCT.product name":"Chai"},
                      {"SUPPLIER.supplies.1.PRODUCT.product name":"Chang"}]})

In the next query we find information about all customers who purchased at least one product with discount greater than 0.2

At least one product means that any element of an array associated with a key "consists of" can be greater than 0.2. In such situation

there is no need to provide an index in an array. A sample implementation of the query is the following.

db.orders.find({"CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.discount":{$gt:0.2}})

The last query can also be implemented with "$elemMatch" operator in the following way.

db.orders.find({"CUSTOMER.submits.ORDER.consists of":{$elemMatch:{"ORDER DETAIL.discount":{$gt:0.2}}}})

In such implementation an array of documents associated with a key "consists of" must contain a document that has a path "ORDER DETAIL.discount" associated with a value greater than 0.2.

In the next query we find information about all customers who purchased at least one product with discount equal to 0.25 and quantity equal to 16. We use operator "$elemMatch" associated with a template of a document that has a path "ORDER DETAIL.discount" associated with 0.25 and a path "ORDER DETAIL.quantity" associated with 16. The query is implemented in the following way.

db.orders.find({"CUSTOMER.submits.ORDER.consists of":{$elemMatch:{"ORDER DETAIL.discount":{$eq:0.25}, "ORDER DETAIL.quantity":{$eq:16}}}})

In the next query we find information about all customers who purchased 4 products in one order. If precisely 4 products are included in one order than the total number of elements in an array associated with a path "CUSTOMER.submits.ORDER.consists of" must be equal to 4. An operator "$size" is used to express a condition on the total number elements in an array.

db.orders.find({"CUSTOMER.submits.ORDER.consists of":{$size:4}})

Queries on arrays

Find information about all customers who purchased at least one product with discount greater than 0.2

 
db.orders.find({"CUSTOMER.submits.ORDER.consists of.ORDER DETAIL.discount":{$gt:0.2}})

 
db.orders.find({"CUSTOMER.submits.ORDER.consists of":{$elemMatch:{"ORDER DETAIL.discount":{$gt:0.2}}}})

Find information about all customers who purchased at least one product with discount equal to 0.25 and quantity equal to 16

 
db.orders.find({"CUSTOMER.submits.ORDER.consists of":{$elemMatch:{"ORDER DETAIL.discount":{$eq:0.25},
	                                                          "ORDER DETAIL.quantity":{$eq:16}}}})

Find information about all customers who purchased 4 products in one order

Queries on arrays

Find information about all customers who purchased more than 4 products in one order

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

A conceptual schema given in the previous slide is transformed in a process of logical design into a logical schema of a sample database.

A logical schema consists of a single collection "orders" with two types of documents:

- the documents that contain information about suppliers and products supplied by suppliers and

- the documents that contain information about customers, orders submitted by customers and contents of orders submitted by customers.

A document that contains information about a supplier and products supplied by a supplier has a two-level nested structure.

A document that contains information about a customer, orders submitted by a customer and contents of each order has a three-level nested structure.

Note that information about the details of products included in each order is represented by a link from a description of an order component to a description of a product. Such link is visualized in a logical schema as a dashed arrow leading from a ORDER DETAIL component of CUSTOMER documents to PRODUCT component of SUPPLIER document.

A formal specification of a transformation of conceptual schema into a logical schema of a collection will be presented in the next presentation.

Projections

A sample collection orders

All queries implemented so far retrieved the complete documents from a collection orders that satisfied certain conditions expressed as the BSON templates. In practice, it is pretty rare that we are interested in all information included in the retrieved documents. Typically we are interested with the values associated with few keys in the documents. To select the individual keys we use the second argument of a method find().

We start from a simple query that finds a company name and contact name for all suppliers. A template "SUPPLIER":{$exists:true}}" used as the first argument of "find()" select all documents from a collection "orders" that have a path "SUPPLIER".The second arguments determines which "key:value" pairs from the selected arguments will be included into an answer. if a key is a associated with a value 1 in the second argument of 'find()" the respective "key:value" pairs will be included into n answer. if the second argument contains at least one "key" associted with 1 then it means all other "key:value" pairs except "_id" are not included into an answer. If all keys in the second argument are associated with 0 it means that all other "key:value" pairs must be included into an answer. If "_id" is associated with 0 then it is not include in into an answer together with a value associated with "_id".

Implementation of a query is given below.

db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.company name":1,"SUPPLIER.contact name":1})

Only the paths SUPPLIER.company name", "SUPPLIER.contact name" and the values associated with the paths are include into an answer. The keys "_id" and the values associated with "_id" are not included into an answer.

In the next query we find all information about suppliers except a company name, contact name and _id.

The second argument of '"find()" method is a document with "_id" associated with 0 and two paths "SUPPLIER.company name" and "SUPPLIER.contact name"also associated with 0. If there is no single key or path associated with one then it means that we would like to select all data from each document except those associated with 0 in the second argument of "find()" method. implementation of the query is the following.

{"_id":0,"SUPPLIER.company name":0,"SUPPLIER.contact name":0})

The next query like the previous one lists information to be excluded. Find all information about suppliers except products supplied by suppliers and "_id" (document identifier).

db.orders.find({"SUPPLIER":{$exists:true}},

{"_id":0,"SUPPLIER.supplies":0}))

Implementation of a query that finds only information about products supplied by suppliers excludes "_id" from the results and includes only the values associated at the end of a path "SUPPLIER.supplies".

db.orders.find({"SUPPLIER":{$exists:true}},

{"_id":0,"SUPPLIER.supplies":1}))

Entire array associated with a key "supplies" is included into an answer.

It is possible to include into an answer only the documents included in an array associated with a path "SUPPLIER.supplies". In such case we must extend a path to "SUPPLIER.supplies.PRODUCT".

db.orders.find({"SUPPLIER":{$exists:true}},

{"_id":0,"SUPPLIER.supplies.PRODUCT":1}))

Projections

Find only a company name and contact name for all suppliers

 
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.company name":1,"SUPPLIER.contact name":1})

Find all information about suppliers except a company name, contact name and _id

 
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.company name":0,"SUPPLIER.contact name":0})

Find all information about suppliers except products supplied by suppliers and _id

Find only information about products supplied by suppliers

 
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.supplies":1}))

 
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.supplies.PRODUCT":1}))

The next query finds only the names of products supplied by suppliers. A path to the names of products "SUPPLIER.supplies.PRODUCT.product name" is associate with 1. A pair "_id:0" excludes document identifiers from an answer.

db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.supplies.PRODUCT.product name":1})

The next query adds one more path associated with a name of category to the second parameters of "find()" method.

Find only the names of products and categories of products supplied by suppliers

db.orders.find({"SUPPLIER":{$exists:true}},

{"_id":0,"SUPPLIER.supplies.PRODUCT.product name":1,

"SUPPLIER.supplies.PRODUCT.category name":1})

In the last query we find only company names of suppliers and the names of products supplied by suppliers. Both paths leading to company names and product name are associated with 1. It means that no other components of the documents describing supplier are required in an answer. Again, "_id" associated with 0 measn that there is no need for dcoument identifier as well.

db.orders.find({"SUPPLIER":{$exists:true}},

{"_id":0,"SUPPLIER.company name":1,

"SUPPLIER.supplies.PRODUCT.product name":1})

Projections

Find only the names of products supplied by suppliers

  
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.supplies.PRODUCT.product name":1})

Find only the names of products and categories of products supplied by suppliers

  
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.supplies.PRODUCT.product name":1,
	                                            "SUPPLIER.supplies.PRODUCT.category name":1})

Find only company names of suppliers and the names of products supplied by suppliers

  
db.orders.find({"SUPPLIER":{$exists:true}},{"_id":0,"SUPPLIER.company name":1,
                                                    "SUPPLIER.supplies.PRODUCT.product name":1})

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

This slide looks at implementation of missing, unknown and not applicable information in BSON documents and about the way we can search

for documents where the keys are associated with nulls or where the keys are missing.

There are two ways to express a fact that the real world object represented by a BSON documents does not have a value of a property or such value is missing. The first way is to associate nothing with a key where nothing is represented by a symbol "null".

For example a fact that some customers have no region information can be represented with a symbol "null" associated with a path "CUSTOMER.region". Then a query that finds all information about the customers who have no region information can be implemented tin the following way.

db.orders.find({"CUSTOMER.region":null})

A negation of a search condition in the previous query, i.e. trying to find all information about the customers who have region information can be implemented in the following way.

db.orders.find({"CUSTOMER.region":{$not:{$eq:null}}})

The other way to express a fact that the real world object does not have a property or a fact that a value of property is missing is do not use a key in a body of a document. For example a query that finds all information about the customers who have PO Box in their description can be expressed in the following way.

db.orders.find({"CUSTOMER.PO Box":{$exists:true}})

The opposite search condition that finds all information about the customers who do not have PO Box in their description can be expressed in the following way.

db.orders.find({"CUSTOMER.PO Box":{$exists:false}})

Queries about nulls and missing keys

Find all information about the customers who have no region information
Find all information about the customers who have region information
Find all information about the customers who have PO Box in their description

Find all information about the customers who do not have PO Box in their description

 
db.orders.find({"CUSTOMER.PO Box":{$exists:false}})

db.orders.find({"CUSTOMER.PO Box":{$not:{$exists:true}}})

MongoDB Query language

Outline

MongoDB query language
A sample database
Simple queries
Queries with Boolean operations
Queries on nested documents
Queries on arrays
Projections
Queries about NULLs and missing keys
Iterations over a cursor

Iterations over a cursor

Create a cursor and display all information about suppliers

 
var cursor = db.orders.find({"SUPPLIER":{$exists:true}})
while(cursor.hasNext())
{ print(tojson(cursor.next())); }

var cursor = db.orders.find({"SUPPLIER":{$exists:true}})
cursor.forEach(printjson)

References

MongoDB Reference, Operators, Query and Projection Operators
Banker K., Bakkum P., Verch S., Garret D., Hawkins T., MongoDB in Action, 2nd ed., Manning Publishers, 2016