Logical Combining Clause (Reference)

The script content on this page is for navigation purposes only and does not alter the content in any way.

A logical combining clause combines the effects of multiple non-empty filter conditions.

A logical combining clause is a logical combining operator — $and, $or, or $nor — followed by a non-empty array of one or more non-empty filter conditions.1

This logical combining clause uses operator $or. It is satisfied if either of its conditions is true (or if both are true). That is, it is satisfied if the document contains a field name whose value is "Joe", or if it contains a field salary whose value is 10000.

"$or" : [ {"name" : "Joe"}, {"salary" : 10000} ]

The following logical combining clause uses operator $and. Its array operand has two filter conditions as its members. The second of these is a condition with a logical combining clause that uses operator $or. This logical combining clause is satisfied if both of its conditions are true. That is, it is satisfied if the document contains a field age whose value is at least 60, and either it contains a field name whose value is "Jason" or it contains a field drinks whose value is "tea".

"$and" : [ {"age" : {"$gte" : 60}},
           {"$or" : [{"name" : "Jason"}, {"drinks" : "tea"}]} ]

Omitting $and

Sometimes you can omit the use of $and.

A filter condition is true if and only if all of its clauses are true. And a field-condition clause can contain multiple condition clauses, all of which must be true for the field-condition clause as whole to be true. In each of these, logical conjunction (AND) is implied. Because of this you can often omit the use of $and, for brevity.

This is illustrated by Example 5-1 and Example 5-2, which are equivalent in their effect. Operator $and is explicit in Example 5-1 and implicit (omitted) in Example 5-2.

The filter specifies objects for which the name starts with “Fred” and the salary is greater than 10,000 and less than or equal to 20,000 and either address.city is “Bedrock” or address.zip is 12345 and married is true.

A rule of thumb for $and omission is this: If you omit $and, make sure that no field or operator in the resulting filter appears multiple times at the same level in the same object.

This rule precludes using a filter such as this, where field salary appears twice at the same level in the same object:

{ "salary" : { "$gt" : 10000 },
  "age"    : { "$gt" : 40 },
  "salary" : { "$lt" : 20000 } }

And it precludes using a filter such as this, where the same condition operator, $regex, is applied more than once to field name in the same condition clause:

{ "name" : { "$regex" : "son", "$regex" : "Jas" } }

The behavior here is not that the field condition is true if and only if both of the $regex criteria are true. To be sure to get that effect, you would use a QBE such as this one:

{ $and : [ { "name" : { "$regex" : "son" }, { "name" : { "$regex" : "Jas" } ] }

If you do not follow the rule of thumb for $and omission then only one of the conflicting condition clauses that use the same field or operator is evaluated; the others are ignored, and no error is raised. For the salary example, only one of the salary field-condition clauses is evaluated; for the name example, only one of the $regex condition clauses is evaluated. Which one of the set of multiple condition clauses gets evaluated is undefined.

Example 5-1 Filter Specification with Explicit $and Operator

{ "$and" : [ { "name"    : { "$startsWith" : "Fred" } },
             { "salary"  : { "$gt"  : 10000, "$lte" : 20000 } },
             { "$or"     : [ { "address.city"    : "Bedrock" },
                             { "address.zip" : 12345 } ] },
             { "married" : true } ] }

Example 5-2 Filter Specification with Implicit $and Operator

{ "name"    : { "$startsWith" : "Fred" },
  "salary"  : { "$gt"  : 10000, "$lte" : 20000 },
  "$or"     : [ { "address.city"    : "Bedrock" },
                { "address.zip" : 12345 } ],
  "married" : true }

  1. A syntax error is raised if the array does not contain at least one element. ↩