OJAI Query Conditions Using elementAnd
The elementAnd
operator allows you to specify multiple conditions on
the same array element using a container field path. This is in contrast to the
and
operator where conditions can refer to any array element. You can use
elementAnd
with both nested documents and scalar values. You can also use it
in combination with other operators, including between
, and
,
and or
.
Using elementAnd
with Nested Documents
Assume that you have the following set of documents that reflect student scores on
courses. Each document has an array of grades
. Grades
is a
nested document that reflects how the students scored on each course they took.
{"_id":"001","grades":[{"course":"math","score":15.5},{"score":12,"course":"history"},{"course":"english","score":8}]} {"_id":"002","grades":[{"course":"math","score":4},{"course":"history","score":12,"cmts":"..."},{"course":"english","score":18}]} {"_id":"003","grades":[{"course":"math","score":11},{"course":"history","score":15},{"course":"english","score":12},{"course":"sports","score":4}]} {"_id":"004","grades":[{"course":"math","score":15.5},{"course":"history","score":12,"details":{"info":"..."}}]} {"_id":"005","grades":[{"course":"math","score":15.5},{"course":"history","score":10},{"course":"physics","score":11}]}
elementAnd
condition:{ "$elementAnd":{ "grades[]":[ {"$eq":{"course":"history"}}, {"$eq":{"score":12}} ] } }
The condition matches the following documents, with the matching conditions highlighted in bold:
{"_id":"001","grades":[{"course":"math","score":15.5},{"course":"history","score":12},{"course":"english","score":8}]} {"_id":"002","grades":[{"course":"math","score":4},{"course":"history","score":12,"cmts":"..."},{"course":"english","score":18}]} {"_id":"004","grades":[{"course":"math","score":15.5},{"course":"history","score":12,"details":{"info":"..."}}]}
The example illustrates the following behavior:
- The positions of the subfields in the nested document are not significant.
- In document 002, there are other subfields in the nested document that do not match the specified conditions.
In contrast, the following example expresses a different condition, using
and
instead of elementAnd
:
{ "$and":[ {"$eq":{"grades[].course":"history"}}, {"$eq":{"grades[].score":12}} ] }
This condition returns documents corresponding to students who have taken history and scored 12 on any course. The following are the matching documents, with the matching conditions highlighted in bold:
{"_id":"001","grades":[{"course":"math","score":15.5},{"course":"history","score":12},{"course":"english","score":8}]} {"_id":"002","grades":[{"course":"math","score":4},{"course":"history","score":12,"cmts":"..."},{"course":"english","score":18}]} {"_id":"003","grades":[{"course":"math","score":11},{"course":"history","score":15},{"course":"english","score":12},{"course":"sports","score":4}]} {"_id":"004","grades":[{"course":"math","score":15.5},{"course":"history","score":12,"details":{"info":"..."}}]}
The example illustrates the following behavior:
- Besides returning the same documents as the previous
elementAnd
example, this condition also returns document 003. - Document 003 matches because that student took history and scored 12 on english, rather than history.
- Document 005 does not match because although the student took history, the student did not score 12 on any courses.
Using elementAnd
with Scalar Values
If you apply elementAnd
to a container of scalar values, you use the
$
symbol to denote an unspecified container element.
Suppose you have the following documents:
{"_id" : "001", "name" : "a", "values" : [1, 2, 3, 6, 15] } {"_id" : "002", "name" : "b", "values" : [3, 6, 9, 10, 15] } {"_id" : "003", "name" : "c", "values" : [14] } {"_id" : "004", "name" : "c", "values" : 11 }
To find all documents where values[]
contains a number between 7 and 11
(inclusive), you can use the following condition:
{ "$elementAnd":{ "values[]":[ {"$ge":{"$":7}}, {"$le":{"$":11}} ] } }
The condition returns the following documents, with the matching numbers highlighted in bold:
{"_id":"002","name":"b","values":[3,6,9,10,15]} {"_id":"004","name":"c","values":11}
The example illustrates the following behavior:
- In document 002, multiple elements in the array match the condition.
- In document 004,
values
is a scalar value.
Suppose you apply the following condition that uses and
instead of
elementAnd
:
{ "$and":[ {"$ge":{"values[]":7}}, {"$le":{"values[]":11}} ] }
All documents except 003 match this and
condition because in the matching
documents, values[]
contains some number greater than or equal to 7
and some number less than or equal to 11. The difference is that the same number does
not need to match both conditions, which is the case for document 001.
Using between
with
elementAnd
You cannot use a container field path in a between
condition. To use the
between
operator to match against an arbitrary array element, you must
include the between
condition in an elementAnd
condition.
The following table shows the proper way to specify a between
condition
that is equivalent to the elementAnd
example from the previous section:
Correct Condition | Incorrect Condition |
---|---|
|
|
This example uses between
to match against an arbitrary scalar array
element. You can also use between
to match against a subfield in a nested
document, in which the nested document is an arbitrary array element.
For example, using the sample documents shown earlier, the following table shows the
correct way to apply the between
operator on the subfield
score
in the nested documents that are elements in the
grades
array:
Correct Condition | Incorrect Condition |
---|---|
|
|
The condition returns the following documents, with the matching conditions highlighted in bold:
{"_id":"001","grades":[{"course":"math","score":15.5},{"course":"history","score":12},{"course":"english","score":8}]} {"_id":"002","grades":[{"course":"math","score":4},{"cmts":"...","course":"history","score":12},{"course":"english","score":18}]} {"_id":"004","grades":[{"course":"math","score":15.5},{"course":"history","details":{"info":"..."},"score":12}]} {"_id":"005","grades":[{"course":"math","score":15.5},{"course":"history","score":10},{"course":"physics","score":11}]}
Using Other Operators in elementAnd
Conditions
You can also use operators like or
in elementAnd
's query
condition list.
For example, the following condition finds all students who scored 12 in either history or english:
{
"$elementAnd":{
"grades[]":[
{"$or":[
{"$eq":{"course":"history"}},
{"$eq":{"course":"english"}}
]},
{"$eq":{"score":12}}
]
}
}
The condition returns the following documents, with the matching conditions highlighted in bold:
{"_id":"001","grades":[{"course":"math","score":15.5},{"course":"history","score":12},{"course":"english","score":8}]} {"_id":"002","grades":[{"course":"math","score":4},{"cmts":"...","course":"history","score":12},{"course":"english","score":18}]} {"_id":"003","grades":[{"course":"math","score":11},{"course":"history","score":15},{"course":"english","score":12},{"course":"sports","score":4}]} {"_id":"004","grades":[{"course":"math","score":15.5},{"course":"history","score":12,"details":{"info":"..."}}]}
Combining elementAnd
with Other Operators
You can combine elementAnd
with other operators like
and
.
For example, using the sample documents shown earlier, suppose you want to find all students who scored 12 in history as well scored 15.5 in math. The following condition expresses this criteria:
{ "$and":[ { "$elementAnd":{ "grades[]":[ {"$eq":{"course":"history"}}, {"$eq":{"score":12}} ] } }, { "$elementAnd":{ "grades[]":[ {"$eq":{"course":"math"}}, {"$eq":{"score":15.5}} ] } } ] }
The condition returns the following documents, with the matching conditions highlighted in bold:
{"_id":"001","grades":[{"course":"math","score":15.5},{"course":"history","score":12},{"course":"english","score":8}]} {"_id":"004","grades":[{"course":"math","score":15.5},{"course":"history","score":12,"details":{"info":"..."}}]}