Try Studio 3T for Free
  1. Knowledge Base
  2. MongoDB Tutorials
  3. MongoDB Arrays Tutorial: How to Query String Values (With Exercises)

MongoDB Arrays Tutorial: How to Query String Values (With Exercises)

In this tutorial, you’ll learn how to query MongoDB arrays containing String values.

To build and run these queries, you’ll use both IntelliShell and the Visual Query Builder in Studio 3T.

This will allow you to learn the basics of querying an array in MongoDB using the mongo shell, while alternatively seeing how easy it is to build these queries using a drag-and-drop UI.

By the end of this tutorial, you will learn how to:

  • Use IntelliShell to query single and multiple values in a MongoDB array
  • Use Visual Query Builder to query a single array value
  • Use Visual Query Builder to query multiple array values

What you will need:

What is an array in MongoDB?

Documents in a MongoDB database commonly include fields defined with the Array data type.

A field configured with this type can contain zero or more elements that together form a list—or array—of values.

An array is simply a list of values, and an array value can take many forms.

The values might all be the same data type, or they might be different types. For example, an Array field might contain a mix of strings, as in the example below:

{
   cities: [ “London”, “New York”, “San Francisco”]
}

Or an array of embedded documents:

 "cities" : [
        {
            "city" : "London", 
            "country" : "United Kingdom"
        }, 
        {
            "city" : "New York", 
            "country" : "United States"
        }
        {
            "city" : "San Francisco", 
            "country" : "United States"
        }
    ]

Or an array of any MongoDB-supported BSON data type (e.g. Decimal128, Int32, Date, find the full list of the MongoDB-supported BSON types here):

{
   cities: [ “0.128”, “20”, “1972-07-22T09:39:28.000+0000”]
}

In these examples, we would refer to array values like “London”, “0.128”, and “United States” and array embedded fields like “city”, “country” as array elements.

When the documents in your collection contain arrays, you’ll likely want to build queries that can retrieve specific documents based on the values within those arrays.

To do so, you should understand the fundamentals of working with an array to ensure you can retrieve the documents you need when you need them.

Querying MongoDB arrays

We’ll be using the customers collection to learn different concepts.

The documents in this collection contain the interests field, which is an array that includes multiple String values, usually between two and four.

Although the field can contain any number of values and those values can be different types, this particular collection is limited to only a few String values.

The following figure shows a sample of the values in the interests array, as they appear on the Result tab of the customers collection tab.

interests is an example of a MongoDB array field

The figure provides a good cross section of the interests array.

Although most documents include the Technology value, not all do, nor do the values appear in the same order.

If you want to retrieve documents based on a single value in the array, you can use the collection’s find method, specifying the name of the array field and the target value, as shown in the following example:

use sales;
db.customers.find(
  { "interests": "Gaming" } );

If you imported the customers collection to a different database, use this value instead of sales.

The statement calls the find method on the customers collection in the sales database.

The method takes one argument, an embedded document that specifies the field name, followed by the value. In this case, the target field is interests and the target value is Gaming.

The query will return only documents that contain the Gaming value in the interests array.

It does not matter where the Gaming value falls within the array or how many values are included in the array. All that matters is that the array contains the specified element.

Using $elemMatch to query arrays

When working with arrays, you might come across the $elemMatch operator. You’ll certainly see it when using Visual Query Builder to create statements based on array values.

The operator can be used with the find method or aggregate method to return documents in which at least one element in the array matches the method’s specified search criteria.

For example, you can recast the previous statement to include the $elemMatch operator, as shown in the following example:

use sales;
db.customers.find(
  { "interests": { $elemMatch: {$eq: "Gaming"} } } );

The statement returns the same results as the preceding one, but with the added complexity that comes with the $elemMatch operator.

The search condition in this case is the embedded document {$eq: "Gaming"}, which means a document must contain the Gaming value in the interests array in order to be returned, just like the previous statement.

As the two examples demonstrate, you don’t need to use the $elemMatch operator when specifying a single search condition.

The operator is mentioned here only because you’ll see it used in the code generated by Visual Query Builder, even when only one search condition is specified.

How to build a MongoDB query based on multiple array string values

In the meantime, let’s consider how to build a query based on multiple array values, a process not quite as straightforward as a query based on a single value.

Suppose you want to return documents in which the interests array contains both the Gaming and Web Design values. In an attempt to build such a query, you might try the following statement:

use sales;
db.customers.find(
  { "interests": ["Gaming", "Web Design"] } );

Unfortunately, the query returns no documents from the customers collection because it’s looking for those that are an exact match.

In other words, the interests array must contain both the Gaming and Web Design values in the specified order and include no other array elements.

Returning all documents containing two array values

To return all documents that contain both values, you can instead use the $and operator to define two search conditions, one for Gaming and one for Web Design, as shown in the following example:

use sales;
db.customers.find(
  { 
    "$and": [
      { "interests": "Gaming" }, 
      { "interests": "Web Design" } ]
  } );

The statement returns only those documents in which the interests array contains both of the target elements.

It does not matter what order they appear or whether the array contains other elements, as long the document includes both Gaming and Web Design.

Returning documents that include either of two array values

If you want to return documents that include either value, you can instead use the $or operator:

use sales;
db.customers.find(
  { 
    "$or": [
      { "interests": "Gaming" }, 
      { "interests": "Web Design" } ]
  } );

Now only one of the two conditions needs to be true for a document to be returned, although a document can include both values.

Exercise 1: Using the mongo shell to query single and multiple values in a MongoDB array

In this exercise, you’ll use IntelliShell to create and run several queries that retrieve data from the customers collection. The queries will be based on values in the interests array.

The exercises in this section use the customers collection.

To build the queries in IntelliShell:

1. Launch Studio 3T and connect to your MongoDB database.

2. In the Connection Tree, right-click the database where you imported the customers collection and click Open IntelliShell. Studio 3T opens IntelliShell in its own tab in the main window.

3. On the IntelliShell toolbar, de-select the Retain previous result tabs button and Raw shell mode button if either are selected. This will make it easier to view the results returned by the mongo shell commands in this exercise.

De-select these two tabs in IntelliShell

4. At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { "interests": { $elemMatch: {$eq: "Database"} } }, 
  { "last" : 1.0, "interests": 1.0 } );

The statement uses the $elemMatch operator to match documents that contain the Database value in the interests array.

As long as any array element matches the operator’s search condition ({$eq: "Database"}), the document will be returned.

In other words, the results will include any document that has an array value that equals Database.

The statement also limits the results to the last and interests fields, which are specified as the second argument passed to the find method. The 1.0 value associated with each field indicates that the field should be included in the results, as opposed to being excluded.

5. On the IntelliShell toolbar, click the Execute entire script button (the round execute button with the single arrow).

Studio 3T runs the find statement and returns the results in the bottom window, displaying them in the Find tab.

6. On the Find tab, display the results in Table View, if it’s not already selected.

7. If the values in the interests array are not displayed, right-click one of the values in the interests column and then click Show Embedded Fields.

The query should have returned 22 documents. The following figure shows part of those results. Notice that the Database value can appear in any position within the array.

The query returns 22 documents containing the Database value

8. At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { interests: "Database" }, 
  { "last" : 1.0, "interests": 1.0 } );

The statement returns the same results as the previous find statement; however, you do not need to use the $elemMatch operator because you’re defining only one search condition.

9. On the IntelliShell toolbar, click the Execute entire script button. The statement should return the same 22 rows as the previous example.

10. At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { "interests.0": "Web Design" }, 
  { "last" : 1.0, "interests": 1.0 } );

The statement uses dot notation (interest.0) to specify the index position when verifying values in the interests array.

MongoDB indexes are zero-based, so interests.0 refers to the first position in the interests array. As a result, the statement will return only documents whose first value in that array is Web Design.

When using dot notation in this way, you must enclose the field and index position in quotation marks.

If you refer to the preceding example, you’ll see that the interests field name, when used alone, does not need to be enclosed in quotes, although quotation marks can be used.

11. On the IntelliShell toolbar, click the Execute entire script button. The query should return only three documents. For each document, the first value in the interests array should be Web Design.

12. At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { interests: { $size: 4 } }, 
  { "last" : 1.0, "interests": 1.0 } );

The statement uses the $size operator to specify that the interests array should include exactly four elements for the document to be returned.

13. On the IntelliShell toolbar, click the Execute entire script button. The query should return seven documents, each containing four values in the interests array.

14. At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { 
    "$and": [
      { "interests": { $elemMatch: { $eq: "Database" } } }, 
      { "interests": { $elemMatch: { $eq: "Web Design" } } } ]
  }, 
  { "last" : 1.0, "interests" : 1.0 } );

The statement uses the $and operator to define two search conditions that must both evaluate to true for a document to be returned.

Each search condition uses the $elemMatch operator to indicate that the interests array must contain the specified values (Database and Web Design, respectively).

15. On the IntelliShell toolbar, click the Execute entire script button. The statement returns only two documents. Each document includes the Database and Web Design values in the interests array, as shown in the following figure.

Using the $elemMatch operator to query a MongoDB array

16, At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { 
    "$and": [
      { "interests": "Database" }, 
      { "interests": "Web Design" } ]
  }, 
  { "last" : 1.0, "interests" : 1.0 } );

The statement works just like the previous one, except that it does not use the $elemMatch operator, helping to simplify the overall statement.

17. On the IntelliShell toolbar, click the Execute entire script button. You should receive the same results as the previous statement.

18. At the command prompt in the IntelliShell editor, replace the existing code with the following find statement:

db.customers.find(
  { "interests": { $all: ["Database", "Web Design"] } }, 
  { "last" : 1.0, "interests" : 1.0 } );

Rather than using the $and operator, the statement uses the $all operator, which provides a simple way to specify that returned documents should include certain values in the target array.

The values themselves are passed to the $all operator as an array, which in this case includes the Database and Web Design elements.

19. On the IntelliShell toolbar, click the Execute entire script button. You should receive the same results as the previous two statements.

20. Close the IntelliShell tab. If prompted to save your changes, click No.

Exercise 2: Using Visual Query Builder to query a single array value

In this exercise, you’ll use Visual Query Builder to build and run a query that retrieves documents from the customers collection. The query will be based on a single value (Database) in the interests array.

To build the query in Visual Query Builder: 

1. In the Studio 3T Connection Tree, expand the database where you imported the customers collection and, if necessary, expand the Collections node.

2. Double-click the customers collection node to open the customers collection tab.

3. On the customers collection tab, click the Visual Query Builder link to launch Visual Query Builder.

Open Visual Query Builder
4. In the Query section of the query form, select the Has array element(s) matching option from the drop-down list in the upper left corner.

Studio 3T adds the Array drop-down list to the top of the Query section, as shown in the following figure.

Choose Has array elements matching

5. From the Array drop-down list, select the interests field, which is configured with the Array data type.

6.  Click the Add button (white-on-green plus sign) in the box at the bottom of the Query section. The form will expand to include options for defining a search condition, as shown in the following figure.

Array Element will appear in the Query section

7. In the first drop-down list in the new search condition section, ensure that <Array Element> is selected, and in the second drop-down list (the one to the right of the first list), ensure that equals is selected. These options are typically selected by default, as indicated in the previous figure, but you should verify them anyway.

8. In the text box beneath the two drop-down lists, type Database.

9. In the main window on the customers collection tab, display the collection in Tree View and, if necessary, expand the first record.

10. Drag the last field to the box at the bottom of the Projection section of the query form.

11. Drag the interests field to the same box in the Projection section. Your query form should now look like the following figure.

The complete MongoDB query

12. On the query form, click the Run button in the upper right corner.

13. In the main window of the customers collection tab, display the collection in Table View.

14. If the values in the interests array are not displayed, right-click a value in the interests column and then click Show Embedded Fields. The query should have returned 22 documents, as shown in the following figure.

22 resulting documents from a query

15. Go to the Query Code tab and view the mongo shell code. It should look similar to the first find statement you created in the previous exercise, as shown in the following figure.

Choose mongo shell under the Query Code tab

16. Leave the customers collection tab and Visual Query Builder form in place for the next exercise.

Exercise 3: Using Visual Query Builder to query multiple array values

In this exercise, you’ll use Visual Query Builder to build and run a query that retrieves documents from the customers collection. The query will be based on two values (Database and Web Design) in the interests array.

To build the query in Visual Query Builder:

1. Return to the Visual Query Builder form on the customers collection tab. The query you built in the previous exercise should still populate the query form.

2. In the Query section, click the Delete button (trash can icon).

Click on the Delete icon

When you click the Delete button, Studio 3T removes the search condition, but leaves the Projection section untouched.

3. In the Query section, select the Match all of ($and) option from the drop-down list beneath the Array drop-down list. When you select this option, Studio 3T removes the Array drop-down list.

4. Click the Add AND/OR group link near the bottom right corner of the Query section.

Add AND/OR group

Studio 3T adds a subsection for defining the $and operator’s first search condition. The subsection has a light gray background, as shown in the following figure.

Added subsection in Visual Query Builder

5. In the new subsection, select Has array element(s) matching from the drop-down list. Studio 3T adds the Array drop-down list to the top of the subsection, similar to what you saw in the previous exercise.

6. From the newly added Array drop-down list, select the interests field.

7. Click the Add button (plus sign) in the box at the bottom of the subsection. The form will expand to include options for defining the search condition.

8. In the first drop-down list in the expanded area, ensure that <Array Element> is selected, and in the second drop-down list (to the right of the first one), ensure that equals is selected.

Make sure equals is chosen in the Visual Query Builder

9. In the text box beneath the two drop-down lists, type Database. The Query section should now look like the following figure.

Fill out the subsection in green

The box beneath the subsection you just configured is outlined in red to show where you define the $and operator’s second search condition.

10. In the box for the second search condition, click the Add AND/OR group link. (Be sure not to click the Add AND/OR group link directly above it.) This adds a second subsection, as shown in the following figure.

Add an AND/OR group in the correct subsection

11. In the new subsection, select Has array element(s) matching from the drop-down list.

12. From the Array drop-down list, select the interests field.

13. In the same subsection, click the Add button. The subsection will expand to include options for defining the second search condition.

14. In the first drop-down list in the expanded section, ensure that <Array Element> is selected, and in the second drop-down list, ensure that equals is selected.

15. In the text box beneath the two drop-down lists, type Web Design. The Query section should now look like the following figure.

Two array elements in the Visual Query Builder

16. Ensure that the Projection section still specifies that last and interests fields, just like you left it at the end of the previous exercise.

17. On the query form, click the Run button in the upper right corner.

18. In the main window of the customers collection tab, ensure that the Result tab is selected. The query should return only two rows, as shown in the following figure.

Using the $elemMatch operator to query a MongoDB array

19. Go to the Query Code tab and view the mongo shell code. It should look similar to the one of the find statements you created in the first exercise, as shown in the following figure.

Choose mongo shell language under Query Code

20. Close Studio 3T.

 

Updated on July 8, 2020

Was this article helpful?

Related Articles

Comments

  1. Hello,

    I have a problem, and I can’t find the query to retrieve a document, here is my structure :

    {
    “_id” : ObjectId(“5e42856dbb56000091007113”),
    “name” : “parent 1”,
    “children1” : [
    {
    “name” : “Child 1”,
    “_id” : ObjectId(“5e428574bb56000091007116”)
    “children2” : [
    {
    “name” : “child 2”,
    “_id” : ObjectId(“5e42882dbb5600009100712a”)
    ]
    }
    ]
    },
    {
    “nom” : “Child 1 bis”,
    “_id” : ObjectId(“5e42856dbb56000091007115”),
    “children2” : [
    {
    “name” : “child 2 bis”,
    “_id” : ObjectId(“5e4287e2bb56000091007124”)
    },
    {
    “name” : “child 2 ter”,
    “_id” : ObjectId(“5e4287f3bb56000091007127”),
    }
    ]
    }
    ]
    }

    I want to retrieve the children 2 ter and update it with the objectId, how am I suppose to do that ?

    I’m looking for an agregation function but no result for the moment.

    Please help 🙁

Leave a Comment