Try Studio 3T for Free
  1. Knowledge Base
  2. MongoDB Tutorials
  3. How to Query and Filter Elements from MongoDB Arrays

How to Query and Filter Elements from MongoDB Arrays

The smallest unit of data in MongoDB is a field-value pair, like:

{
   "city" : "Berlin",
}

Field-value pairs are then stored in documents like the one below:

{ 
    "_id" : ObjectId("57d28452ed5d4d54e8686ff6"), 
    "title" : "Mr", 
    "first" : "Paul", 
    "last" : "Diaz", 
    "city" : "Berlin", 
    "country" : "Germany", 
}

MongoDB’s flexible document structure makes it possible to have a field with an array as a value.

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

It can be an array of string values, 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.

Beginner: Using Visual Query Builder to query array string values & embedded documents

The good news for beginners is, Visual Query Builder makes it possible to query arrays through a drag-and-drop query builder.

Get Visual Query Builder by downloading the latest Studio 3T version, available here. Not a beginner? Skip ahead to the advanced tutorial.

Two types of array fields

Let’s query a collection called expats.

To import the collection, copy all documents from the expats.json file and paste them to a new collection in Studio 3T. Alternatively, save the file and import it to your MongoDB database following these steps.

expats contains two types of array fields:

  • cities_visited whose value is an array of string values:
{
   cities_visited: [ “Tokyo”, “Sapporo”, “Kyoto”]
}
  • And previous_addresses whose value is an array of embedded documents:
 "previous_addresses" : [
        {
            "city" : "Berlin", 
            "country" : "Germany"
        }, 
        {
            "city" : "Austin", 
            "country" : "United States"
        }
    ]

Each embedded document contains two fields: city and country.

The expats collection contains ten documents (or ten expat profiles). We’d like to query the array fields cities_visited and previous_addresses to answer the questions:

  • Who has visited Berlin?
  • Who has lived in Spain?

Open the expats collection

Once you’ve downloaded and imported the expats collection, open it in Studio 3T by double-clicking on the collection in the Connection Tree.

This will open a collection tab. Table View should be chosen by default in the view dropdown menu.

expats collection in a collection tab

Query an array of string values

Who’s been to Berlin?

Let’s first query the cities_visited array field to find the element string value, “Berlin”.

1 – Open Visual Query Builder

Click on the Visual Query Builder button on the top-right corner of the collection tab.

Visual Query Button icon

This will open the query builder which contains three sections: Query, Sort, and Projection.

2 – Choose “Has array element(s) matching” in the Query Section

Choose Has array elements matching

3 – Choose the array field to query

The Array dropdown menu will appear. Here, choose cities_visited as the array to query.

Choose cities_visited as the array to query

4 – Choose <Array Element>

Double-click to expand the section that says “Drag field here or double-click”.

<Array Element> should be chosen by default. If not, choose it from the field dropdown menu.

Choose Array Element from the field dropdown menu

5 – Type the array value to look for

Type “Berlin” since we want the query to return a list of expats who have visited Berlin.

Type the array value to look for

Here we’re querying string values, but keep in mind that you can query other data types as easily by clicking and choosing from the data type dropdown menu:

Choose other data types by clicking on the dropdown

6 – Run the query

Click “Run” to execute the query.  Also, click on “Hide Query Builder”.

7 – Check results

Looks like Jonathan, Phyllis and Marilyn have visited the German capital.

Expats who have been to Berlin

We can confirm our results by “stepping into” the cities_visited column. Double-click on any value in the column.

The cities-visited column

This allows us to go a level deeper into the data so that we only see those values.

Step into cities_visited column

Query an array of embedded documents

Who’s lived in Spain?

Now that we have successfully queried an array of string values, let’s query an array of embedded documents to find the array element value, “Spain”, where the array embedded field is “country”.

Let’s start from scratch and open our expats collection in a new tab.

1 – Open Visual Query Builder

Visual Query Button icon

Click the button on the top-right corner.

2 – Choose Has array element(s) matching in the Query section

Choose Has array elements matching

3 – Choose the array field to query

Choose previous_addresses as the field to query in the Array dropdown menu.

Choose previous_addresses

4 – Click on the “Drag field here or double-click” section

5 – Type the name of the array embedded field to query

The field dropdown will choose <Array Element> by default. Let’s override it by typing the name of the embedded field we want to query, “country”.

Type the embedded field name to query

6 – Type the array value to search for

Looking for the array value, "Spain"

Here, let’s type “Spain”.

7 – Execute the query

Click on the “Run” button.

8 – Check results

The query returns two expats, Paul and Craig, who seem to have lived in Spain. But where exactly?

Right-click on any cell in the Results tab and choose Show All Embedded Fields.

Show embedded fieldsThis will show all embedded fields next to their parent fields. An embedded field is always written in dot notation (e.g. previous_addresses.0.city).

Show embedded fields for spot-checking

Now we can easily see that the two have indeed lived in Spain – in Barcelona and Madrid, respectively.


Learn more about what Visual Query Builder can do, as well as other handy tasks you can complete in Studio 3T even when you’re just getting started with MongoDB.

Advanced: Using $elemMatch alongside find(), $match, and the $project and $filter functions

Often, we need to check documents that contain specific information inside arrays and to project only some of the data stored inside them.

We need to be careful with how we build the queries when dealing with arrays. Oftentimes, we make the mistake in the query and fetch documents we are not looking for.

This is the case when we have two or more conditions and, as a result, we get documents in which one element of the array matches one condition and another document only matches the other condition.

Of course, what we want is that one element of the array matches both conditions at the same time.

It is very common to project all the fields in the documents, but in some cases, our purpose is not only to select those documents that match our conditions but, beyond that, we want to project only those fields in the arrays that we need (or a particular user is allowed to check).

Purpose

In this advanced tutorial, we are going to learn how to select only those documents in our dataset in which inside an array there are fields that satisfy our requirements. Also, we are going to learn how to project only the array’s fields we need.

To select or filter the documents that we need, we are going to use the $elemMatch operator alongside find().

Also, we can use $elemMatch inside the $match stage of the aggregation framework.

However, we will use the $filter function when comes the time to project those fields that we need.

A quick reminder: this operator runs in conjunction with the $project stage of the aggregation framework. Thus, if we need to select documents according to the values of the fields in arrays and to project only some array fields, and all of this in the same query, we need to use the aggregation framework.

Our MongoDB dataset

In this post, the dataset we are going to work with consists only in a collection of items called ‘articles’ and two documents – enough for our didactic purposes. As is already habitual in my previous posts, the database we are going to use is called ‘3tdb’.

The info we have in each document is:

  • Code of the article – ‘article.code’
  • Article description – ‘description’
  • Array with information regarding the purchases made to our providers – ‘purchase’
  • Array with information regarding the stock per country and warehouse – ‘stock’

This is the query to execute in our ‘3tdb’ database in order to store these two documents in our ‘articles’ collection:

$ mongo
use 3tdb
db.articles.insert([
{
	"_id" : 1,
	"description" : "DESCRIPTION ARTICLE AB",
	"article_code" : "AB",
	"purchase" : [
		{
			"company" : 1,
			"cost" : NumberDecimal("80.010000")
		},
		{
			"company" : 2,
			"cost" : NumberDecimal("85.820000")
		},
		{
			"company" : 3,
			"cost" : NumberDecimal("79.910000")
		}
	],
	"stock" : [
	    {
	        "country" : "01",
	        "warehouse" : {
	            "code" : "02",
	            "units" : 10
	        }
	    },
	    {
	        "country" : "02",
	        "warehouse" : {
	            "code" : "02",
	            "units" : 8
	        }
	    }
	]
},
{
	"_id" : 2,
	"description" : "DESCRIPTION ARTICLE AC",
	"article_code" : "AC",
	"purchase" : [
		{
			"company" : 1,
			"cost" : NumberDecimal("90.010000")
		},
		{
			"company" : 2,
			"cost" : NumberDecimal("95.820000")
		},
		{
			"company" : 3,
			"cost" : NumberDecimal("89.910000")
		}
	],
	"stock" : [
	    {
	        "country" : "01",
	        "warehouse" : {
	            "code" : "01",
	            "units" : 20
	        }
	    },
	    {
	        "country" : "02",
	        "warehouse" : {
	            "code" : "02",
	            "units" : 28
	        }
	    }
	]
}
]);

Result:

BulkWriteResult({
	"writeErrors" : [ ],
	"writeConcernErrors" : [ ],
	"nInserted" : 2,
	"nUpserted" : 0,
	"nMatched" : 0,
	"nModified" : 0,
	"nRemoved" : 0,
	"upserted" : [ ]
})

Our first try

Suppose we want to get those items with stock in the warehouse ‘02’ of the country ‘01’. If we do not have the necessary experience, our first try would be something like this:

db.articles.find({
    "stock.country" : "01",
    "stock.warehouse.code" : "02"
}).pretty();

Result:

{
	"_id" : 1,
	"description" : "DESCRIPTION ARTICLE AB",
	"article_code" : "AB",
	"purchase" : [
		{
			"company" : 1,
			"cost" : NumberDecimal("80.010000")
		},
		{
			"company" : 2,
			"cost" : NumberDecimal("85.820000")
		},
		{
			"company" : 3,
			"cost" : NumberDecimal("79.910000")
		}
	],
	"stock" : [
		{
			"country" : "01",
			"warehouse" : {
				"code" : "02",
				"units" : 10
			}
		},
		{
			"country" : "02",
			"warehouse" : {
				"code" : "02",
				"units" : 8
			}
		}
	]
}
{
	"_id" : 2,
	"description" : "DESCRIPTION ARTICLE AC",
	"article_code" : "AC",
	"purchase" : [
		{
			"company" : 1,
			"cost" : NumberDecimal("90.010000")
		},
		{
			"company" : 2,
			"cost" : NumberDecimal("95.820000")
		},
		{
			"company" : 3,
			"cost" : NumberDecimal("89.910000")
		}
	],
	"stock" : [
		{
			"country" : "01",
			"warehouse" : {
				"code" : "01",
				"units" : 20
			}
		},
		{
			"country" : "02",
			"warehouse" : {
				"code" : "02",
				"units" : 28
			}
		}
	]
}

It is very easy to build this query using the Visual Query Builder of the MongoDB GUI, Studio 3T .

Related reading: Visual Query Builder, Studio 3T’s drag-and-drop MongoDB query builder

Studio 3T's Visual Query Builder, a MongoDB drag-and-drop query builder

The query has returned both documents.

If we look carefully at the document where _id field equals 1, our requirements are fulfilled in one of the subdocuments of the array, while in the document which _id field equals 2, only one condition is matched in each of the subdocuments of the array.

Our purpose is to obtain only those documents in which all of our conditions are fulfilled, at the same time, in the subdocument, or subdocuments, of the array. So, our query must not return the document which _id field equals 2.

In order to build this query in a proper way, we must use the $elemMatch operator.

How to use $elemMatch

The official MongoDB documentation for version 4.0 states:

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

Syntax

This is the syntax of the $elemMatch operator:

<fieldName> : { $elemMatch : { <condition1>, <condition2>, … } }

How to use $elemMatch with find()

We can use the $elemMatch in the query part of the find() function, and/or in the projection part of it.

Let’s see right now how to use it in the query part with the goal to resolve our problem.

db.articles.find(
  { stock : { $elemMatch : { country : "01", "warehouse.code" : "02" } } }
).pretty();

Result:

{
	"_id" : 1,
	"description" : "DESCRIPTION ARTICLE AB",
	"article_code" : "AB",
	"purchase" : [
		{
			"company" : 1,
			"cost" : NumberDecimal("80.010000")
		},
		{
			"company" : 2,
			"cost" : NumberDecimal("85.820000")
		},
		{
			"company" : 3,
			"cost" : NumberDecimal("79.910000")
		}
	],
	"stock" : [
		{
			"country" : "01",
			"warehouse" : {
				"code" : "02",
				"units" : 10
			}
		},
		{
			"country" : "02",
			"warehouse" : {
				"code" : "02",
				"units" : 8
			}
		}
	]
}

As we expected, the query has returned the same document.

To make writing the aggregation query easier, you can use Studio 3T’s Aggregation Editor:

Studio 3T's Aggregation Editor simplifies building aggregation queries

How to use $filter

Filtering elements from arrays

We should query only the documents we need.

However, on several occasions, we do not want to project all the fields in the document, but only those that match our requirements.

For this, we need to filter out of the array only those elements that meet our criteria. Obviously, we must do this in the $project stage.

To achieve our goal (see: not to project elements that do not meet our requirements), we must use the $filter operator (new in 3.2 version) alongside the aggregation framework.

Let’s continue with the same example. We maintain the $match stage to get only the document we need and then we add the $project stage.

Inside this stage, we project all the fields we need and, through the $filter operator, we tell the database which fields inside the array we want to project too.

The $filter operator has three variables:

  • input: the array we are going to work with
  • as: reference name
  • cond: our requirements

So, right now this is how our query looks like:

db.articles.aggregate([
{
   "$match" : {
       "stock" : {
          "$elemMatch" : {
             "$and" : [
                { "country" : "01" },
                { "warehouse.code" : "02" }
             ]
          }
       },
   }
},
{
   "$project" : {
       "article_code" : 1, "description" : 1,
       "stock" : {
          "$filter" : {
             "input" : "$stock",
             "as" : "stock",
             "cond" : {
                "$and" : [
                   { "$eq" : [ "$$stock.country", "01" ] },
                   { "$eq" : [ "$$stock.warehouse.code", "02" ] }
                ]
             }
          }
       }
   }
}
]).pretty();

Let’s check whether the output responds to what we are looking for:

{
	"_id" : 1,
	"description" : "DESCRIPTION ARTICLE AB",
	"article_code" : "AB",
	"stock" : [
		{
			"country" : "01",
			"warehouse" : {
				"code" : "02",
				"units" : 10
			}
		}
	]
}

Ok, we got it!

In the next picture, we can see how the query looks like in Studio 3T:

The same MongoDB aggregation query in Studio 3T

$elemMatch in the find projection part

Maybe you’re wondering: why don’t we use the $elemMatch operator in the projection part of the find(), instead of using the $filter operator in the aggregation framework?

Let’s refer to the official documentation:

The $elemMatch operator projects the first matching element from an array based on a condition.

OK. In order to better understand what this means, we are going to insert a new document in our ‘3tdb.articles’ collection.

db.articles.insert(
{
	"_id" : 3,
	"description" : "DESCRIPTION ARTICLE AD",
	"article_code" : "AD",
	"purchase" : [
		{
			"company" : 1,
			"cost" : NumberDecimal("70.010000")
		},
		{
			"company" : 2,
			"cost" : NumberDecimal("75.820000")
		},
		{
			"company" : 3,
			"cost" : NumberDecimal("69.910000")
		}
	],
	"stock" : [
		{
			"country" : "01",
			"warehouse" : {
				"code" : "01",
				"units" : 5
			}
		},
		{
			"country" : "01",
			"warehouse" : {
				"code" : "02",
				"units" : 6
			}
		}
	]
});

And now, we are going to run our query using the $elemMatch operator in the projection part of the find() function.

db.articles.find(
  { _id : 3 },
  { stock : { $elemMatch : { country : "01" } } }
).pretty();

This is the document the query returns:

{
	"_id" : 3,
	"stock" : [
		{
			"country" : "01",
			"warehouse" : {
				"code" : "01",
				"units" : 5
			}
		}
	]
}

Thus, this is not what we are looking for and this is the reason we do not use it.

Conclusion

We’ve learned how to resolve one of the most common problems that commonly plagues those new to MongoDB, which is how to query an array when more than one condition must be fulfilled in the same element. We now also know how to use the $elemMatch operator.

Once we achieved our first goal, the second was to extract from the array only the elements we needed. Now we know and are able to use the $filter operator alongside the aggregation framework.

Finally, we’ve learned about the use of the $match operator in the project part of the find function.


Here are other tutorials on MongoDB arrays you might find helpful:

Updated on February 19, 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