Comparison Clause (Reference)

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

A comparison clause is an object member whose field is a comparison operator. Example: "$gt" : 200. Table 5-1 describes the comparison operators. See Sample JSON Documents for the documents used in the examples in column Description.

Table 1 Filter Comparison Operators

Operator	Description
$exists	Tests whether the field exists. Matches document if either: The field exists and the operand represents true, meaning that it is any scalar value except false, null, or 0. The field does not exist and the operand represents false, meaning that it is false, null, or 0. Operand JSON scalar. Example {drinks : { "$exists" : true }} matches sample document 3. {drinks : { "$exists" : false }} matches sample documents 1 and 2.
$eq	Matches document only if field value equals operand value. Operand JSON scalar. Example {"name" : { "$eq" : "Jason" }} matches sample document 1.
$ne	Matches document only if field value does not equal operand value or there is no such field in the document. Operand JSON scalar. Example {"name" : { "$ne" : "Jason" }} matches sample documents 2 and 3.
$gt	Matches document only if field value is greater than operand value. Operand JSON number or string. Example {"age" : { "$gt" : 50 }} matches sample document 2.
$lt	Matches document only if field value is less than operand value. Operand JSON number or string. Example {"age" : { "$lt" : 50 }} matches sample document 1.
$gte	Matches document only if field value is greater than or equal to operand value. Operand JSON number or string. Example {"age" : { "$gte" : 45 }} matches sample documents 1, 2, and 3.
$lte	Matches document only if field value is less than or equal to operand value. Operand JSON number or string. Example {"age" : { "$lte" : 45 }} matches sample document 1.
$between	Matches document only if string or number field value is between the two operand array elements or equal to one of them. Operand JSON array of two scalar elements. The first must be the smaller of the two. (For string values, smaller means first, lexicographically.) At most one of the elements can be null, which means no limit. An error is raised if both are null or if there are not exactly two array elements. Example {"age" : { "$between" : [49, 70] }} matches sample documents 2 and 3. {"age" : { "$between" : [45, null] }} matches sample documents 1, 2, and 3. It is equivalent to {"age" : { "$gte" : 45 }}
$startsWith	Matches document only if field value starts with operand value. Operand JSON string. Example {"name" : {"$startsWith" : "J"}} matches sample document 1.
$hasSubstring or $instr	Matches document only if field value is a string with a substring equal to the operand. Operand Non-empty JSON string. Example {"street" : { "$hasSubstring" : "street" }} matches sample documents 1 and 2.
$regex	Matches document only if field value matches operand regular expression. Operand SQL regular expression, as a JSON string. See [Oracle Database SQL Language Reference](/pls/topic/lookup?ctx=en/database/oracle/simple-oracle-document-access/adsdi&id=SQLRF020). Example {"name" : { "$regex" : ".*son"}} matches sample document 1.
$like	Matches document only if field value matches operand pattern. Operand SQL LIKE condition pattern, as a JSON string. See [Oracle Database SQL Language Reference](/pls/topic/lookup?ctx=en/database/oracle/simple-oracle-document-access/adsdi&id=SQLRF-GUID-0779657B-06A8-441F-90C5-044B47862A0A). Example {"city" : { "$like" : "Mar_" }} matches sample documents 2 and 3.
$in	Matches document only if field exists and its value equals at least one value in the operand array. Operand Non-empty JSON array of scalars.[^1] Example {"address.zip" : { "$in" : [ 94088, 90001 ] }} matches sample documents 1 and 2.
$nin	Matches document only if one of these is true: Field exists, but its value is not equal to any value in the operand array. Field does not exist. Operand Non-empty JSON array of scalars.[^1] Example {"address.zip" : { "$nin" : [ 90001 ] }} matches sample documents 1 and 2.
$all	Matches document only if one of these is true: Field value is an array that contains all values in the operand array. Field value is a scalar value and the operand array contains a single matching value. Operand Non-empty JSON array of scalars.[^1] Example {"drinks" : { "$all" : ["soda", "tea"]}} matches sample document 2. {"drinks": { "$all" : ["tea"]}} matches sample documents 1 and 2.

Related Topics

• Overview of Filter Comparison Operators