Querying Embedded Documents in MongoDB Arrays

MongoDB 201: Querying MongoDB Data Querying Embedded Documents in MongoDB Arrays

In the previous tutorial in this course, you learned how to query MongoDB documents that contain fields defined with the Array data type.

An Array field can contain zero or more values (an array) of any supported type, including the Object type, which is used for embedded documents.

However, the examples in the last section were limited to arrays that contained only String values, making the querying process fairly straightforward.

When an array contains embedded documents, you must be careful how you define your queries so you don’t end up with unexpected or misleading results.

Although this can be said of any query, documents embedded in an array present additional challenges. If you don’t get the query right, you could easily retrieve the wrong data.

In this section, you’ll learn how to create find statements that query a collection based on the embedded documents in an array.

To build and run these queries, you’ll use IntelliShell and Visual Query Builder, both Studio 3T features. Although Visual Query Builder tends to be best suited to simpler queries when it comes to embedded documents, it is still important to understand how it differs from querying arrays that contain other types of values.

Querying embedded documents in a MongoDB array

Querying documents based on an array that contains embedded documents is a little different from querying other types of array values.

Although many of the fundamentals are the same, the way you define your search conditions can vary, depending on what you’re trying to achieve.

Suppose you have a MongoDB database that includes the warehouses collection, which stores information about the inventories available at your company’s warehouses.

Now suppose you add the following three documents to the collection:

db.warehouses.insertMany( [
  {
    _id: 1,
    warehouse : "warehouse1",  
    inventory : [
      { product: "widgetA", in_stock: 24 },
      { product: "widgetB", in_stock: 137 },
      { product: "widgetC", in_stock: 99 } ]
  },
  {
    _id: 2,
    warehouse : "warehouse2",
    inventory : [
      { product: "widgetA", in_stock: 99 },
      { product: "widgetC", in_stock: 12 },
      { product: "widgetG", in_stock: 122 } ]
  },
  {
    _id: 3,
    warehouse : "warehouse3",  
    inventory : [
      { product: "widgetC", in_stock: 89 },
      { product: "widgetD", in_stock: 99 },
      { product: "widgetF", in_stock: 43 } ]
  } ] );

Each document includes the inventory field, which is defined with the Array data type, as indicated by the set of square brackets that follow the field name.

The brackets enclose the embedded documents that make up the array’s values.

Each embedded document includes the product field and in_stock field, along with their respective values.

Now suppose you need to know which warehouses have at least 90 widgetC products in stock. In an attempt to discover this information, you might try the following find statement:

db.warehouses.find({
  "inventory.product": "widgetC", 
  "inventory.in_stock": { $gte: 90 } } );

The statement specifies that the product value must equal widgetC and the in_stock value must be at least 90.

The second search condition uses the greater than or equal to ($gte) comparison operator to specify the acceptable number of products.

Unfortunately, the find statement returns all three documents, even though the first document is the only one whose warehouse has at least 90 widgetC products.

This is because each document includes the widgetC product and each one includes a quantity that is greater than or equal to 90.

The search engine is indifferent to whether the product value and in_stock value are in the same embedded document, only that the array as a whole contains these values.

In an attempt to rectify this situation, you might try the following find statement, which separates the name of the array from the embedded document elements:

db.warehouses.find({ 
   inventory: { product: "widgetC", in_stock: { $gte: 90 } } } );

This time, the statement returns no documents because the search engine is looking for an exact match, which is not possible with the $gte comparison operator.

To match the values exactly, you would need to use a statement such as the following, which specifies the exact product amount:

db.warehouses.find({ 
  inventory: { product: "widgetC", in_stock: 99 } } );

Although the statement returns the first document, as it should, this approach is effective only if you know the exact number of items in stock, which was not your original intent.

This approach also requires that you specify all elements in the array in the order they’re listed. If a document’s elements are in a different order, the document will not be included in the search results, even if the field values match.

A better approach is to use the $elemMatch operator, which returns documents containing an array element that matches the specified criteria. The following syntax shows how to use the operator:

{ <array_field>: { $elemMatch: { <search_condition> [ ,...n ] } } }

First, you specify the array field, then the $elemMatch keyword, and finally one or more search conditions, with everything enclosed in the appropriate curly braces. For example, to find warehouses that have at least 90 widgetC products, you would run the following find statement:

db.warehouses.find(
  { inventory: { $elemMatch: 
    { product: "widgetC", in_stock: { $gte: 90 } } } } );

The argument passed into the find method starts with the array field name (inventory), followed by the $elemMatch operator and two search conditions, which specify the target product name and number of items.

For a document to be returned, at least one of the array’s embedded documents must match the two search conditions.

In this case, only the first document will be returned because it represents the only warehouse that has at least 90 widgetC products on hand.