Filtering

You can search the collection of resources by using the q query parameter to specify search criteria. If you use the q parameter when retrieving a list of resources using the GET method, the response will only include the objects matching the conditions specified in the request.

The query expression can include a single clause or combine several clauses.

• Each clause includes a field name and a comparison operator — See Queryable Fields and Comparison Operators.

• Clauses may include one or more values — See Values in Query Clauses.

• To combine clause, you can use logical operators and parenthesis ( ) — See Logical Operators and Precedence.

Important:

The query expression must be URL-encoded to be included as part of the URL query string. The request will fail otherwise. The examples in this guide do not show URL-encoded query expressions for ease of reading. For example, the query expression q=created BETWEEN [‘2020-01-01’,’2020-06-30’] AND currency IS ‘EUR’ should be encoded to q=created%20BETWEEN%20[%272020-01-01%27,%272020-06-30%27]%20AND%20currency%20IS%20%27EUR%27.

Please review the current limitations for this feature — See Limitations.

Queryable Fields

The field descriptions in the Generated API Documentation JSON or the REST API Endpoint Reference indicate whether you can use the field in your query clauses. Look for the [Query allowed] mention in the schema object field descriptions.

Comparison Operators

The comparison operators you can use depend on the field data type and format. The following table lists the comparison operators available for each data type and format.

There are different classes of operators:

• Unary operators operate on only one operand — the field name. A unary operator is typically used in this format: fieldName OPERATOR

• Binary operators operate on two operands — the field name and a value. A binary operator is typically used in this format: fieldName OPERATOR value

• Ternary operators operate on three operands — the field name and two values. A ternary operator is typically used in this format: fieldName OPERATOR [value1,value2]

• N-ary operators operate on N operands — the field name and one or more values. A ternary operator is typically used in this format: fieldName OPERATOR [value1,value2,value3,...]

Data Type (Format)	Query Operators	Class	Examples
[All data types and formats]	EMPTY, EMPTY_NOT	Unary	description EMPTY
Boolean	IS, IS_NOT	Binary	isActive IS true
integer($int64) number($double) number($float)	EQUAL, EQUAL_NOT, GREATER, GREATER_NOT, GREATER_OR_EQUAL, GREATER_OR_EQUAL_NOT, LESS, LESS_NOT, LESS_OR_EQUAL, LESS_OR_EQUAL_NOT,	Binary	userId EQUAL 237 total LESS_OR_EQUAL 148.5
integer($int64) number($double) number($float)	BETWEEN, BETWEEN_NOT, WITHIN, WITHIN_NOT	Ternary	total BETWEEN [25,148.5]
integer($int64) number($double) number($float)	ANY_OF, ANY_OF_NOT	N-ary	userId ANY_OF [217, 237, 638,755,829]
string	CONTAIN, CONTAIN_NOT, IS, IS_NOT, START_WITH, START_WITH_NOT, END_WITH, END_WITH_NOT	Binary	description CONTAIN “meal”
string($date) string($date-time)	AFTER, AFTER_NOT, BEFORE, BEFORE_NOT, ON, ON_NOT, ON_OR_AFTER, ON_OR_AFTER_NOT, ON_OR_BEFORE, ON_OR_BEFORE_NOT	Binary	accountingDate ON_OR_BEFORE “2020–09–30”
string($date) string($date-time)	BETWEEN, BETWEEN_NOT,	Ternary	created BETWEEN [“2020–01–01”,“2020–06–30”]

Values in Query Clauses

Query clauses may include one or more values. Multiple values are The following table lists the accepted value formats for each data type and format.

Data Type (Format)	Value Format	Examples
Boolean	lowercase, Titlecase, or numeric Boolean values, with or without single or double quotes	true, False, ‘true’, “False”, 1, ‘0’, “1”
integer($int64) number($double) number($float)	numeric values, with or without single or double quotes	126.32, ‘126.32’, “126.32”
string	string values, with single or double quotes	'USD', “CAD”
string($date) string($date-time)	string values using the date format YYYY-MM-DD, with single or double quotes Note: The date-time format is not supported for values in query expressions. Use the date format YYYY-MM-DD in query clauses for the read-only fields created, updated, and exported, even though values are returned as string($date-time).	'2020–09–19', “2020–09–19”

Logical Operators and Precedence

You can combine clauses in your query expression using the logical operators AND and OR. The operator AND takes precedence over the operator OR. You can use parenthesis () to mark precedence in your query expression.

Consider the example isAdjusting IS false AND (currency IS 'CAD' OR userId EQUAL 237) AND accountingDate EMPTY — the request returns the objects meeting all the following criteria:

• isAdjusting IS false — The expense report is not an adjusting expense report.

• currency IS 'CAD' OR userId EQUAL 237 — Either the expense report currency is Canadian Dollars or the internal ID for the Employee is 237.

• accountingDate EMPTY — The accounting date is not set.

Limitations

The following limitations currently apply to the filtering feature:

• The query expression passed using the q parameter in query string can be up to around 5500 characters long. Longer query strings return an invalid request error.

• OpenAir custom field types other than Check box, Numeric, and Date are treated as string. You can only use the query operators available for the relevant data type and format string data types when querying other custom field types. See also Custom Fields.

• OpenAir custom field types Multiple selection and Pick list may not be handled correctly with some string query operators. Use the operators CONTAIN and CONTAIN_NOT with these custom field types. See also Custom Fields.