Studio 3T Knowledge Base

How to Find & Filter MongoDB Array Elements Using $elemMatch

Posted on: 16/10/2020 (last updated: 06/09/2021) by Juan Roy Couto

We need to be careful with how we build the queries when dealing with MongoDB 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.

In this tutorial, we are going to learn how to select only those documents in our dataset that contain fields within arrays that satisfy our requirements, and project only the array’s fields we need.

The MongoDB client Studio 3T provides multiple ways to query MongoDB arrays.

This tutorial covers how to filter elements using the $elemMatch operator alongside find(), the MongoDB aggregation stages $match and $project, and IntelliShell, Studio 3T’s built-in mongo shell.

If you would prefer a no-code, visual approach, here’s a tutorial on how to query string values and array elements using a drag-and-drop query builder.

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:

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

We’ll be using IntelliShell, the built-in mongo shell in Studio 3T, throughout the tutorial.  Let’s use it to add our two new documents.

1. Create a 3tdb database.

2. Add the new articles collection.

3. Open IntelliShell, done quickest with the keyboard shortcut Ctrl + L (⌘+ L).

Get IntelliShell in the latest Studio 3T version.

4. Execute this query in our 3tdb database in order to store these two documents in our articles collection:

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
	        }
	    }
	]
}
]);

5. In the Result tab, choose JSON View. The result should be:

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

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

Our first try

Now that we’ve inserted the two documents, 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();

Which gives us this result in JSON View:

{
	"_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
			}
		}
	]
}

Side note: It is very easy to build such find() queries using the Visual Query Builder in 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

In the screenshot above, we’ve simply dragged the fields into the query builder, defined the values, and run the query.

The query returned the same two documents, pasted again in whole below.

{
	"_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
			}
		}
	]
}

If we look carefully at the document where _id field equals 1 (bolded above) our requirements are fulfilled in one of the subdocuments of the array.

In the document where _id field equals 2 (also bolded above), 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.

That means this query should 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 go back to IntelliShell and run this query:

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

This should give you the 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 right document (where _id field equals 1).

Next, we will learn how to filter out of the array only those elements that meet our criteria using the aggregation stage, $project.

To make writing the aggregation query easier, we will use Studio 3T’s Aggregation Editor.

Let’s get started by taking our original query and copying only the bolded part:

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

Open Aggregation Editor in Studio 3T, done quickest with the keyboard shortcut F4.

Add a first stage using the $match operator and paste the bolded part of the query. Make sure you remove any existing placeholder brackets or you might get an error.

Execute the pipeline. The result should look like the screenshot below, in JSON View.

Studio 3T's Aggregation Editor simplifies building aggregation queries

Related reading: Aggregation Editor, Studio 3T’s stage-by-stage MongoDB aggregation pipeline builder.

How to use $filter and $project

Filtering elements from arrays

On several occasions, we do not want to project (or display) 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. We can do this using the $project aggregation stage.

To achieve our goal – to filter the document to show only the fields we need – we must use the $filter operator 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

Right now this is how our query looks like in full. Let’s copy, paste, and run the query in IntelliShell.

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();

Then, 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!

Now back to the Aggregation Editor.

Let’s add a second stage using the $project operator and paste the query below. Make sure you remove any existing placeholder brackets or you might get an error.

{ "article_code" : 1, 
  "description" : 1, 
  "stock" : { 
      "$filter" : { 
         "input" : "$stock", 
         "as" : "stock", 
         "cond" : { "$and" : [
              { "$eq" : [ "$stock.country", "01" ] }, 
              { "$eq" : [ "$stock.warehouse.code", "02" ] }
           ] 
         } 
      } 
   } 
}

Next, execute the full pipeline. You should see only one document.

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 articles collection.

Go back to IntelliShell and run the query below to insert our third document.

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
			}
		}
	]
});

Next, let’s run this 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.

Hands-on exercises

Find more exercises to practice your array-filtering skills in our MongoDB course, MongoDB 201: Querying MongoDB Data. Dive straight in, no registration needed.

How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

Juan Roy Couto

Juan reinvented himself to become one of the current MongoDB Masters (https://www.mongodb.com/community/masters). He has got both MongoDB certifications, DBA and DEV. Currently, he works as a MongoDB DBA. Before that, he worked for 20 years as a developer for various financial companies. He likes to collaborate with the Madrid's MUG and also talking to tech communities. You can read him at twitter.com/juanroycouto.