Writing Gadget Data Query Criteria

Key

A pair key can be:

• The name of a gadget data record property in the DB. In such a case:

  • The key is a search parameter against which gadget data records are filtered out.

  • The key-value pair is a complete, simple or complex search expression.

  {"customer_ref_identifier": "value"}

• A supported logical, comparison, or SQL operator. In such a case:

  • The key-value pair is either:

    • A join of multiple search expressions if the operator is AND or OR.

      {"$or": [ {"customer_ref_identifier": "102030"}, {"customer_ref_identifier": "102040"} ] }

    • A conditional clause of a complex search expression if the operator is not AND or OR.

      {"$eq": "value"}

Value

A pair value can be:

• An actual value as a string. In such a case:

  • The pair key is the name of a gadget data record property in the DB or an operator that is not AND or OR.

  • The pair value is a search operand against which the given gadget data record property is compared.

  {"customer_ref_identifier": "102030"}

• A nested key-value pair. In such a case:

  • The pair key is the name of a gadget data record property in the DB.

  • The pair value—the nested key-value pair—is a conditional clause that defines a condition with one or more operands against which the given gadget data record property is compared:

    • The key of the nested pair is an operator that is not AND or OR.

    • The value of the nested pair is an operand or an array of operands if the given operator supports multiple operands.

  {"customer_ref_identifier": {"$ne": "102030"} }

• The true Boolean value. In such a case:

  • The pair is nested in the value of another pair.

  • The key of the nesting pair is the name of a gadget data record property in the DB.

  • The pair is a conditional clause that defines a condition with one Boolean operand against which the given gadget data record property is compared.

  • The pair key is an SQL operator that accepts a Boolean operand: Is Null or Is Not Null.

  {"gadget_type": {"$isnull": true} }

• An array of strings. In such a case:

  • The pair is nested in the value of another pair.

  • The key of the nesting pair is the name of a gadget data record property in the DB.

  • The pair is a conditional clause that defines a condition with more than one operand against which the given gadget data record property is compared.

  • The pair key is an SQL operator that accepts multiple operands: In, Lowercase In, Not In, Lowercase Not In, or Between.

  {"user_identifier": {"$in": ["Jane Smith", "John Doe", "Johnnie Doe" ] } }

• An array of nested key-value pairs. In such a case:

  • The pair key is the AND or OR logical operator.

  • The pair value is multiple search expressions joined by the given logical operator.

  • The pair is a logical join expression.

  {"$or": [ {"customer_ref_identifier": "102030"}, {"customer_ref_identifier": "102040"} ] }

AND syntax

AND examples

Against the following search condition JSON object, the system will return the gadget data records whose customer_ref_identifier property has a value between "102030" and "204030" AND whose user_identifier property value is "John Doe".

AND discussion

The AND operator cannot be used as a key in a conditional clause. Its value is always an array of key-value pairs, not a string or conditional clause.

Multiple AND and OR operators can be combined by nesting.

For looking up values in a closed range, it is better to use the Between operator rather than joining the Greater Than and Less Than expressions with the AND operator.

OR syntax

OR examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value is "Jane Smith" or "John Doe".

OR discussion

The OR operator cannot be used as a key in a conditional clause. Its value is always an array of key-value pairs, not a string or conditional clause.

Multiple AND and OR operators can be combined by nesting.

When comparing one gadget data property with the Equal operator against multiple operands joined with the OR condition, it is better to use the In operator.

Equal syntax

You can specify the Equal operator implicitly or explicitly.

Equal examples

The following search condition JSON objects specify the same search criterion. Against each, the system will return the gadget data records whose user_identifier property value is "John Doe". As the Equal comparison is case-sensitive, the records that have the same value but with letters in a different case, like "john doe", will not be returned.

Equal discussion

The Equal operator is the opposite of Not Equal.

For case-insensitive comparisons, use the Lowercase Equal operator.

The Equal operator compares against one operand. If you need to compare one gadget data property for case-sensitive equality against multiple operands joined with the OR condition, use the In operator.

Lowercase Equal syntax

Where:

• "lowercase operand": a string whose all letters are in lower case.

Lowercase Equal examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value is "john doe" written with letters in any case, like "John Doe", "John doe", "JOHN DOE", and so on.

As the Lowercase Equal operator requires the operand to be in lower case, the system will not return any gadget data records against the following search condition JSON object, whose operand includes one or more letters in upper case, such as "John Doe".

Lowercase Equal discussion

The Lowercase Equal operator is equivalent to LOWER(property) = 'lowercase operand' in SQL.

It is the opposite of Lowercase Not Equal.

For case-sensitive comparisons, use the Equal operator.

The Lowercase Equal operator compares against one operand. If you need to compare one gadget data property for case-insensitive equality against multiple operands joined with the OR condition, use the Lowercase In operator.

Not Equal syntax

Not Equal examples

Against the following search condition JSON object, the system will not return the gadget data records whose user_identifier property value is "John Doe". As the not equal comparison is case-sensitive, the records that have the same value but with letters in a different case, like "john doe", will be returned.

Not Equal discussion

The Not Equal operator is the opposite of Equal.

For case-insensitive comparisons, use the Lowercase Not Equal operator.

The Not Equal operator compares against one operand. If you need to compare one gadget data property for case-sensitive inequality against multiple operands joined with the OR condition, use the Not In operator.

Lowercase Not Equal syntax

Where:

• "lowercase operand": a string whose all letters are in lower case.

Lowercase Not Equal examples

Against the following search condition JSON object, the system will not return the gadget data records whose user_identifier property value is "john doe" with letters in any case, like "John Doe", "John doe", "JOHN DOE", and so on.

As the Lowercase Not Equal operator requires the operand to be in lower case, the system will return the gadget data records with the values described in the previous example against the following search condition JSON object because its operand includes one or more letters in upper case, such as "John Doe".

Lowercase Not Equal discussion

The Lowercase Not Equal operator is equivalent to LOWER(property) != 'lowercase operand' in SQL.

It is the opposite of Lowercase Equal.

For case-sensitive comparisons, use the Not Equal operator.

The Lowercase Not Equal operator compares against one operand. If you need to compare one gadget data property for case-insensitive inequality against multiple operands joined with the OR condition, use the Lowercase Not In operator.

Greater Than syntax

Greater Than examples

Against the following search condition JSON object, the system will return the gadget data records whose customer_ref_identifier property value is greater than "102030".

Greater Than discussion

The Greater Than operator can compare textual data. The comparison is case-sensitive. Numbers in textual fields, like key, value, customer_ref_identifier, user_identifier, etc., are compared as strings, not as numbers. For details, see Comparing strings.

For looking up values in a closed range, it is better to use the Between operator rather than joining the Greater Than and Less Than expressions with the AND operator.

Less Than syntax

Less Than examples

Against the following search condition JSON object, the system will return the gadget data records whose customer_ref_identifier property value is less than "204030".

Less Than discussion

The Less Than operator can compare textual data. The comparison is case-sensitive. Numbers in textual fields, like key, value, customer_ref_identifier, user_identifier, etc., are compared as strings, not as numbers. For details, see Comparing strings.

For looking up values in a closed range, it is better to use the Between operator rather than joining the Greater Than and Less Than expressions with the AND operator.

Greater Than Or Equal syntax

Greater than or equal examples

Against the following search condition JSON object, the system will return the gadget data records whose customer_ref_identifier property value is greater than or equal to "102030".

Greater Than Or Equal discussion

The Greater Than Or Equal operator can compare textual data. The comparison is case-sensitive. Numbers in textual fields, like key, value, customer_ref_identifier, user_identifier, etc., are compared as strings, not as numbers. For details, see Comparing strings.

For looking up values in a closed range, it is better to use the Between operator rather than joining the Greater Than Or Equal and Less Than Or Equal expressions with the AND operator.

Less Than Or Equal syntax

Less Than Or Equal examples

Against the following search condition JSON object, the system will return the gadget data records whose customer_ref_identifier property value is less than or equal to "204030".

Less Than Or Equal discussion

The Less Than Or Equal operator can compare textual data. The comparison is case-sensitive. Numbers in textual fields, like key, value, customer_ref_identifier, user_identifier, etc., are compared as strings, not as numbers. For details, see Comparing strings.

For looking up values in a closed range, it is better to use the Between operator rather than joining the Greater Than Or Equal and Less Than Or Equal expressions with the AND operator.

Starts With syntax

Starts With examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value begins with "John", such as "John Doe", "Johnnie Doe", "John Smith", and so on. As the Starts With comparison is case-sensitive, the records that have the same value but with letters in a different case, like "john doe", "johNnie Doe", "JOHN Smith", will not be returned.

Starts With discussion

The Starts With operator is equivalent to LIKE 'operand%' in SQL.

For case-insensitive comparisons, use the Lowercase Starts With operator.

Lowercase Starts With syntax

Lowercase Starts With examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value begins with "john" written with letters in any case, such as "John Doe", "john Doe", "Johnnie Doe", "johNnie Doe", "John Smith", "JOHN Smith", and so on.

The system will return the same results as for the previous example if the operand has one or more letters in upper case, like "John".

Lowercase Starts With discussion

The Lowercase Starts With operator is equivalent to LIKE|ILIKE 'operand%' in SQL, where ILIKE is an insensitive LIKE in PostgreSQL and some other databases.

For case-sensitive comparisons, use the Starts With operator.

Ends With syntax

Ends With examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value ends with "oe", such as "John Doe", "Johnnie oe", "Jane Shmoe", and so on. As the Ends With comparison is case-sensitive, the records that have the same value but with letters in a different case, like "John DOE", "Johnnie Oe", "Jane ShmoE", will not be returned.

Ends With discussion

The Ends With operator is equivalent to LIKE '%operand' in SQL.

For case-insensitive comparisons, use the Lowercase Ends With operator.

Lowercase Ends With syntax

Lowercase Ends With examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value ends with "oe" written with letters in any case, such as "John Doe", "John DOE", "Johnnie oe", "Johnnie Oe", "Jane Shmoe", "Jane ShmoE", and so on.

The system will return the same results as for the previous example if the operand has one or more letters in upper case, like "OE".

Lowercase Ends With discussion

The Lowercase Ends With operator is equivalent to LIKE|ILIKE '%operand' in SQL, where ILIKE is an insensitive LIKE in PostgreSQL and some other databases.

For case-sensitive comparisons, use the Ends With operator.

Contains syntax

Contains examples

Against the following search condition JSON object, the system will return the gadget data records whose value property value contains "AB" at any position, such as "AB2030", "10AB30", "1020AB", and so on. As the Contains comparison is case-sensitive, the records that have the same value but with letters in a different case, like "aB2030", "10ab30", "1020Ab", and so on, will not be returned.

Contains discussion

The Contains operator is equivalent to LIKE '%operand%' in SQL.

It is the opposite of Excludes.

For case-insensitive comparisons, use the Lowercase Contains operator.

Lowercase Contains syntax

Lowercase Contains examples

Against the following search condition JSON object, the system will return the gadget data records whose value property value contains "ab" that is located at any position and written with letters in any case, such as "AB2030", "aB2030", "10AB30", "10ab30", "1020AB", "1020Ab", and so on.

The system will return the same results as for the previous example if the operand has one or more letters in upper case, like "AB".

Lowercase Contains discussion

The Lowercase Contains operator is equivalent to LIKE|ILIKE '%operand%' in SQL, where ILIKE is an insensitive LIKE in PostgreSQL and some other databases.

It is the opposite of Lowercase Excludes.

For case-sensitive comparisons, use the Contains operator.

Excludes syntax

Excludes examples

Against the following search condition JSON object, the system will not return the gadget data records whose value property value contains "AB" at any position, such as "AB2030", "10AB30", "1020AB", and so on. As the Excludes comparison is case-sensitive, the records that have the same value but with letters in a different case, like "aB2030", "10ab30", "1020Ab", and so on, will be returned.

Excludes discussion

The Excludes operator is equivalent to NOT LIKE '%operand%' in SQL.

It is the opposite of Contains.

For case-insensitive comparisons, use the Lowercase Excludes operator.

Lowercase Excludes syntax

Lowercase Excludes examples

Against the following search condition JSON object, the system will not return the gadget data records whose value property value contains "ab" that is located at any position and written with letters in any case, such as "AB2030", "aB2030", "10AB30", "10ab30", "1020AB", "1020Ab", and so on.

The system will output the same results as for the previous example if the operand has one or more letters in upper case, like "AB".

Lowercase Excludes discussion

The Lowercase Excludes operator is equivalent to NOT LIKE|ILIKE '%operand%' in SQL, where ILIKE is an insensitive LIKE in PostgreSQL and some other databases.

It is the opposite of Lowercase Contains.

For case-sensitive comparisons, use the Excludes operator.

In syntax

In examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value is "Jane Smith", "John Doe", or "Johnnie Doe". As the In comparison is case-sensitive, the records that have the same value but with letters in a different case, like "Jane smith", "john doe", "Johnnie DOE", will not be returned.

As the In comparison requires a full match, the system will not return the gadget data records as specified in the previous example against the following search condition JSON object because its "Smith" and "Doe" operands match the discussed values partially.

In discussion

The In operator is a shorthand for comparing one gadget data property with the Equal operator against multiple operands joined with the OR condition. Thus, it can be used as a key only in a conditional clause. And, its value is always an array of strings, not a string, key-value pair, or conditional clause.

It looks for an exact full match with an operand from the list. If the value of a gadget data property includes, at any position, a pattern that matches the operand, the operator will output false for that value because the match is partial.

The In operator is equivalent to IN ('operand 1', 'operand 2', ..., 'operand N') in SQL.

It is the opposite of Not In.

For case-insensitive comparisons, use the Lowercase In operator.

Lowercase In syntax

Where:

• "lowercase operand N": a string whose all letters are in lower case.

Lowercase In examples

Against the following search condition JSON object, the system will return the gadget data records whose user_identifier property value is "jane smith", "john doe", or "johnnie doe" written with letters in any case, like "Jane Smith", "Jane smith", "John Doe", "JOHN DOE", "Johnnie Doe", "Johnnie DOE", and so on.

As the Lowercase In comparison requires a full match, the system will not return the gadget data records as specified in the previous example against the following search condition JSON object because its "smith" and "doe" operands match the discussed values partially.

As the Lowercase In operator requires the operands to be in lower case, the system will not return any gadget data records against the following search condition JSON object, whose operands include one or more letters in upper case, such as "Jane smith", "JOHN DOE", and "Johnnie Doe".

Lowercase In discussion

The Lowercase In operator is a shorthand for comparing one gadget data property with the Lowercase Equal operator against multiple operands joined with the OR condition. Thus, it can be used as a key only in a conditional clause. And, its value is always an array of strings, not a string, key-value pair, or conditional clause.

It looks for an exact full match with an operand from the list. If the value of a gadget data property includes, at any position, a pattern that matches the operand, the operator will output false for that value because the match is partial.

The Lowercase In operator is equivalent to LOWER(property) IN ('operand 1', 'operand 2', ..., 'operand N') in SQL.

It is the opposite of Lowercase Not In.

For case-sensitive comparisons, use the In operator.

Not In syntax

Not In examples

Against the following search condition JSON object, the system will not return the gadget data records whose user_identifier property value is "Jane Smith", "John Doe", or "Johnnie Doe". As the Not In comparison is case-sensitive, the records that have the same value but with letters in a different case, like "Jane smith", "john doe", "Johnnie DOE", will be returned.

As the Not In comparison requires a full match, the system will return the gadget data records as specified in the previous example against the following search condition JSON object because its "Smith" and "Doe" operands match the discussed values partially.

Not In discussion

The Not In operator is a shorthand for comparing one gadget data property with the Not Equal operator against multiple operands joined with the OR condition. Thus, it can be used as a key only in a conditional clause. And, its value is always an array of strings, not a string, key-value pair, or conditional clause.

It looks for an exact full match with an operand from the list. If the value of a gadget data property includes, at any position, a pattern that matches the operand, the operator will output false for that value because the match is partial.

The Not In operator is equivalent to NOT IN ('operand 1', 'operand 2', ..., 'operand N') in SQL.

It is the opposite of In.

For case-insensitive comparisons, use the Lowercase Not In operator.

Lowercase Not In syntax

Where:

• "lowercase operand N": a string whose all letters are in lower case.

Lowercase Not In examples

Against the following search condition JSON object, the system will not return the gadget data records whose user_identifier property value is "jane smith", "john doe", or "johnnie doe" written with letters in any case, like "Jane Smith", "Jane smith", "John Doe", "JOHN DOE", "Johnnie Doe", "Johnnie DOE", and so on.

As the Lowercase Not In comparison requires a full match, the system will return the gadget data records as specified in the previous example against the following search condition JSON object because its "smith" and "doe" operands match the discussed values partially.

As the Lowercase Not In operator requires the operands to be in lower case, the system will return the gadget data records as specified in the first example against the following search condition JSON object, whose operands includes one or more letters in upper case, such as "Jane smith", "JOHN DOE", and "Johnnie Doe".

Lowercase Not In discussion

The Lowercase Not In operator is a shorthand for comparing one gadget data property with the Lowercase Not Equal operator against multiple operands joined with the OR condition. Thus, it can be used as a key only in a conditional clause. And, its value is always an array of strings, not a string, key-value pair, or conditional clause.

It looks for an exact full match with an operand from the list. If the value of a gadget data property includes, at any position, a pattern that matches the operand, the operator will output false for that value because the match is partial.

The Lowercase In operator is equivalent to LOWER(property) NOT IN ('operand 1', 'operand 2', ..., 'operand N') in SQL.

It is the opposite of Lowercase In.

For case-sensitive comparisons, use the Not In operator.

Between syntax

Where:

• "operand 1" is less than "operand 2"

Between examples

Against the following search condition JSON object, the system will return the gadget data records whose customer_ref_identifier property has a value between "102030" and "204030" operands inclusive, i.e., greater than or equal to "102030" and less than or equal to "204030".

As the second operand must be greater than the first one, the system will not return any gadget data records against the following search condition JSON object, which is the first example with the swapped operands.

As the Between operator requires two values, the following example with an extra third operand will raise an error when saving the gadgets data query on the Edit Gadgets Data Query page. The same error occurs when trying to save the Between operator with one operand.

Between discussion

The Between operator is a shorthand for comparing one gadget data property against a range of values defined by the lowest and highest bounds using either of the following conditions:

• Greater than operand 1 and less than operand 2.

• Greater than or equal to operand 1 and less than or equal to operand 2.

Thus, the Between operator can be used as a key only in a conditional clause. And, its value is always an array of two strings, not a string, key-value pair, or conditional clause. The system will raise an error when saving the gadget data query with the number of operands in the array that is not two.

The Between operator can compare textual data. The comparison is case-sensitive. Numbers in textual fields, like key, value, customer_ref_identifier, user_identifier, etc., are compared as strings, not as numbers. For details, see Comparing strings.

The Between operator is equivalent to BETWEEN 'operand 1' AND 'operand 2' in SQL.

Is Null syntax

Is Null examples

Against the following search condition JSON object, the system will return the gadget data records whose gadget_type property has no value set.

Is Null discussion

In DBs and code, NULL is not a value. It is a flag indicating that a given property is null—completely empty—has no value set at all.

The Is Null operator verifies whether gadget data record properties have no values set by testing them against being NULL. Thus, it can be used as a key only in a conditional clause. And, its value is always true, not a string, key-value pair, or conditional clause.

An empty string that has no characters is "", not NULL, because it is a valid value. Some textual properties of login and data capture gadgets, like user_identifier, key, and value, are automatically set to an empty string when document users have not populated them with data. Such properties should be verified against "", with the Equal or Not Equal operator, not against NULL with the Is Null or Is Not Null operator.

The Is Null operator is equivalent to property IS NULL in SQL.

As the NULL operator does not accept a value in SQL, true is a dummy operand to make Is Null expressions comply with the key-value pair notation. An Is Null expression will return the same results against any operand, such as false, "", "abc", etc., because the DB ignores a value given for the NULL SQL operator. Specifying true makes it clear for understanding that the DB will return the records for which the Is Null expression is true. Specifying other operands will not change the output but will introduce a misunderstanding in reading the Is Null expression.

The Is Null operator is the opposite of Is Not Null.

Is Not Null syntax

Is Not Null examples

Against the following search condition JSON object, the system will return the gadget data records whose gadget_type property has a value set.

Is Not Null discussion

In DBs and code, NULL is not a value. It is a flag indicating that a given property is null—completely empty—has no value set at all.

The Is Not Null operator verifies whether gadget data record properties have values set by testing them against not being NULL. Thus, it can be used as a key only in a conditional clause. And, its value is always true, not a string, key-value pair, or conditional clause.

An empty string that has no characters is "", not NULL, because it is a valid value. Some textual properties of login and data capture gadgets, like user_identifier, key, and value, are automatically set to an empty string when document users have not populated them with data. Such properties should be verified against "", with the Equal or Not Equal operator, not against NULL with the Is Not Null or Is Null operator.

The Is Not Null operator is equivalent to property IS NOT NULL in SQL.

As the NULL operator does not accept a value in SQL, true is a dummy operand to make Is Not Null expressions comply with the key-value pair notation. An Is Not Null expression will return the same results against any operand, such as false, "", "abc", etc., because the DB ignores a value given for the NULL SQL operator. Specifying true makes it clear for understanding that the DB will return the records for which the Is Not Null expression is true. Specifying other operands will not change the output but will introduce a misunderstanding in reading the Is Not Null expression.

The Is Not Null operator is the opposite of Is Null.