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 articledescription– Article descriptionpurchase– 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.
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
}
}
]
}
]);
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
In the screenshot above, we’ve simply added the fields in 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 states:
The
$elemMatchoperator 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.
In the first stage using the $match operator, paste the bolded part of the query. Make sure you remove any existing placeholder brackets or you might get an error.
Run the pipeline. The result should look like the screenshot below, in JSON View.
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, run the full pipeline. You should see only one document.
$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
$elemMatchoperator 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.
