Querying and Indexing a MongoDB Database

find(QUERY_OBJECT)

This method accepts a query object, actually a query object. If left empty, all documents will be returned.

findOne(QUERY_OBJECT)

findOne() is used to select only one document.

Filtering Data

To query, or filter, data we can include a query object in our find() or findOne() methods.

When we start adding key/value pairs to the query document, we begin restricting our search. This works in a straightforward way for most types: numbers match numbers, booleans match booleans, and strings match strings. Querying for a simple type is as easy as specifying the value that you are looking for. For example, to find all documents where the value for age is 27, we can add that key/value pair to the query document:

> db.users.find({"age":27})

If we have a string we want to match, such as a "username" key with the value "joe", we use that key/value pair instead:

> db.users.find({"username":"joe"})

Multiple conditions can be strung together by adding more key/value pairs to the query document, which gets interpreted as "condition1 AND condition2 AND … AND conditionN." For instance, to get all users who are 27-year-olds with the username "joe," we can query for the following:

> db.users.find({"username":"joe","age":27})

Example:

db.posts.find( {"category": "News"} )

Projection

Both find methods accept a second parameter called projection.

This parameter is an object that describes which fields to include in the results.

Note: This parameter is optional. If omitted, all fields will be included in the results.

Example:

This example will only display the title and date fields in the results.

db.posts.find({}, {title: 1, date: 1})

Notice that the _id field is also included. This field is always included unless specifically excluded.

We use a 1 to include a field and 0 to exclude a field.

Example:

This time, let's exclude the _id field.

db.posts.find({}, {_id: 0, title: 1, date: 1})

Note: You cannot use both 0 and 1 in the same object. The only exception is the _id field. You should either specify the fields you would like to include or the fields you would like to exclude.

Example:

Let's exclude the category field. All other fields will be included in the results.

db.posts.find({}, {category: 0})

We will get an error if we try to specify both 0 and 1 in the same object.

Limitations on MongoDB Queries

There are some restrictions on queries. The value of a query document must be a constant as far as the database is concerned. (It can be a normal variable in your own code.) That is, it cannot refer to the value of another key in the document. For example, if we were keeping inventory and we had both in_stock and num_sold keys, we couldn't compare their values by querying the following:

> db.stock.find({"in_stock":"this.num_sold"}) // doesn't work

There are ways to do this (see $where Queries), but you will usually get better performance by restructuring your document slightly, such that a normal query will suffice. In this example, we could instead use the keys initial_stock and in_stock. Then, every time someone buys an item, we decrement the value of the in_stock key by one. Finally, we can do a simple query to check which items are out of stock:

> db.stock.find({"in_stock": 0})

Query: Comparisons

"$lt", "$lte", "$gt", and "$gte" are all comparison operators, corresponding to <, <=, >, and >=, respectively. They can be combined to look for a range of values. For example, to look for users who are between the ages of 18 and 30, we can do this:

> db.users.find({"age":{"$gte":18,"$lte":30}})

This would find all documents where the age field was greater than or equal to 18 AND less than or equal to 30.

To query for documents where a key's value is not equal to a certain value, you must use another conditional operator, "$ne", which stands for "not equal." If you want to find all users who do not have the username joe, you can query for them using this:

> db.users.find({"username":{"$ne":"joe"}})

Actually, "$ne" can be used with any type.

> start = new Date("01/01/2007")
> db.users.find({"registered":{"$lt":start}})

{ dateCreated: { $gt: new Date('2000-06-22') } }

{ $and: [ { scores: 75, name: "Greg Powell" } ] }

> db.raffle.find({"ticket_no":{"$in":[725,542,390]}})

> db.users.find({"user_id":{"$in":[12345,"joe"]}})

> db.raffle.find({"ticket_no":{"$nin":[725,542,390]}})

> db.raffle.find({"$or":[{"ticket_no": 725},{"winner": true}]})

> db.raffle.find({"$or":[{"ticket_no":{"$in":[725,542,390]}}, ... {"winner":true}]})

{ $or: [ { version: 4 }, { name: "Andrea Le" } ] }

> db.users.find({"id_num":{"$mod":[5,1]}})

> db.users.find({"id_num":{"$not":{"$mod":[5,1]}}})

{ name: { $not: { $eq: "Andrea Le" } } }

Type-Specific Queries

MongoDB has a wide variety of types that can be used in a document. Some of these types have special behavior when querying.

> db.c.find()
{"_id":ObjectId("4ba0f0dfd22aa494fd523621"),"y":null}
{"_id":ObjectId("4ba0f0dfd22aa494fd523622"),"y":1}
{"_id":ObjectId("4ba0f148d22aa494fd523623"),"y":2}

> db.c.find({"y":null})
{"_id":ObjectId("4ba0f0dfd22aa494fd523621"),"y":null}

> db.c.find({"z":null})
{"_id":ObjectId("4ba0f0dfd22aa494fd523621"),"y":null}
{"_id":ObjectId("4ba0f0dfd22aa494fd523622"),"y":1}
{"_id":ObjectId("4ba0f148d22aa494fd523623"),"y":2}

> db.c.find({"z":{"$eq":null,"$exists":true}})

{ email: { $regex: "andrea_le" } }

> db.users.find({"name":{"$regex":/joe/i}})

> db.users.find({"name":/joey?/i})

> db.foo.insertOne({"bar":/baz/})
> db.foo.find({"bar":/baz/})
{
"_id":ObjectId("4b23c3ca7525f35f94b60a2d"),
"bar":/baz/
}

{ scores: { $elemMatch: { $gt: 80, $lt: 90 } } }

{ "school.name": "Northwestern" }

$where Queries*

Cursors*

Creating an Index using the node.js driver (createIndex())

o create an index using the Node.JS driver, use createIndex().

collection.createIndex( { <key and index type specification> }, function(<err>, <result>) {
   console.log(<result>);
   callback(<result>);
} )

This example creates a single key ascending index on the name field:

collection.createIndex( { name : 1 }, function(err, result) {
   console.log(result);
   callback(result);
} )

Seeing/Monitoring Indexes

To see what indexes exist on your collection, including indexes that are currently being built, run the db.collection.getIndexes() method:

db.collection.getIndexes()

To check if your index is being built, use the $currentOp aggregation stage to return information about active operations on your database. To run $currentOp in mongosh, use the db.aggregate() method on the admin database.

The following aggregation pipeline uses the $match stage to return information about an active operation that builds a descending index on the name field:

db.getSiblingDB("admin").aggregate( [
   { $currentOp : { idleConnections: true } },
   { $match : {"command.createIndexes": { $exists: true } } }
] )

MongoDB marks index builds in various stages, including waiting on commit quorum, as an idle connection by setting the active field to false. The idleConnections: true setting includes these idle connections in the $currentOp output.

Specifying an Index Name

When you create an index, you can give the index a custom name. Giving your index a name helps distinguish different indexes on your collection. For example, you can more easily identify the indexes used by a query in the query plan's explain results if your indexes have distinct names.

To specify the index name, include the name option when you create the index:

db.<collection>.createIndex(
   { <field>: <value> },
   { name: "<indexName>" }
)

Before you specify an index name, consider the following:

• Index names must be unique. Creating an index with the name of an existing index returns an error.
• You can't rename an existing index. Instead, you must drop and recreate the index with a new name.

Default Index Names

If you don't specify a name during index creation, the system generates the name by concatenating each index key field and value with underscores. For example: { score : 1 } will produce an index named score_1, while { content : "text", "description.tags": "text" } will produce an index named content_text_description.tags_text.

Procedure

A blog collection contains data about blog posts and user interactions.

Create a text index on the content, users.comments, and users.profiles fields. Set the index name to InteractionsTextIndex:

db.blog.createIndex(
   {
     content: "text",
     "users.comments": "text",
     "users.profiles": "text"
   },
   {
     name: "InteractionsTextIndex"
   }
)

Creating an Index using the *** driver (???)

Creating an Index using the *** driver (???)

Running a Query with the $search Operator

To use our search index, we will use the $search operator in our aggregation pipeline.

Example:

db.movies.aggregate([
  {
    $search: {
      index: "default", // optional unless you named your index something other than "default"
      text: {
        query: "star wars",
        path: "title"
      },
    },
  },
  {
    $project: {
      title: 1,
      year: 1,
    }
  }
])

The first stage of this aggregation pipeline will return all documents in the movies collection that contain the word star or wars in the title field.

The second stage will project the title and year fields from each document.

Comparison

The following operators can be used in queries to compare values:

• $eq: Values are equal
• $ne: Values are not equal
• $gt: Value is greater than another value
• $gte: Value is greater than or equal to another value
• $lt: Value is less than another value
• $lte: Value is less than or equal to another value
• $in: Value is matched within an array

Logical

The following operators can logically compare multiple queries.

• $and: Returns documents where both queries match
• $or: Returns documents where either query matches
• $nor: Returns documents where both queries fail to match
• $not: Returns documents where the query does not match

Evaluation

The following operators assist in evaluating documents.

• $regex: Allows the use of regular expressions when evaluating field values
• $text: Performs a text search
• $where: Uses a JavaScript expression to match documents

Update