The Zuora Object Query Language (ZOQL) is a simple SQL-like query language used to construct query calls in the SOAP API.
Basic knowledge and experience with SQL is required to work with ZOQL.
The basic query syntax is:
select field_names from object where filter_statements
Use lower case for all keywords.
The following rules apply to the ZOQL statements:
ZOQL for SOAP queries do not support parentheses to indicate order of operation to retrieve data with multiple combined conditions in query() calls. The and operator takes priority in the order of operation over the or operator. Users must take this into account when creating
No Complex Queries
Zuora does not support complex queries and joining for query().
No Aggregate Functions
Nested aggregate functions are not supported for query().
No Wild Card Support
You cannot use the asterisk wild card (*) for field names with a query() call. You must explicitly specify a field name.
No Order By Support
ZOQL does not support sorting results in ascending or descending order.
A maximum of 200 conditions are allowed in a
WHERE clause of a query() call. For example, you can have up to 200 distinct AND or OR clauses.
The query() call allows the following number of records to be returned:
Use the queryMore() call to page through additional results.
In general, queries are not case sensitive. However, explicit string values are case-sensitive, such as pick-list values. For example
Batch1 for Batch field.
The field_names placeholder represents a list of one or more fields that exist in the object. You cannot use single or double quotation marks on the field names. Use a comma to separate one value from the next.
select AccountId, FirstName, LastName from contact where State = 'California'
The object placeholder represents an object from the Zuora API. Each query can name only one object.
The filter_statements placeholder represents comparisons being made on different types of data in order to find items matching specified criteria. A filter statement takes the following form:
field_name operator value
For example, in the following query AutoPay is a filter statement:
select AccountNumber from account where AutoPay !=true
You can also use custom fields in the filter statement. Make sure that the custom field you specified in the filter statement is included in the WSDL file. For example:
select AccountNumber from account where Subsidiary__c='US'
Before R190, Zuora ignored time zone offset components in dateTime values for queries. From R190 and later, if you use a time zone offset component in your query, it will be applied when making a query.
In WSDL 68 and earlier, there are 56 date fields in the Zuora SOAP API that Zuora treats as dateTime fields. From WSDL 69, Zuora treats these fields as date fields. These fields are no longer compatible with dateTime values. The data type of the filter value must match the field type in the filter statement.
See Date Field Changes in the SOAP API for more information.
Zuora treats date fields as dateTime fields. You can filter date or dateTime fields with date or dateTime values. For example:
select AutoRenew from subscription where ContractEffectiveDate = '2015-02-28T23:54:01-08:00'
DateTimes with a time zone offset that is not Pacific Time (GMT-7/-8) are converted to Pacific Time (GMT-7/-8). This could shift the date of items returned from a query of date fields. For example, the following query will return subscriptions with a contract effective date of
2014-11-30 even though the query is for
select AutoRenew from subscription where ContractEffectiveDate = '2014-12-01T01:30:00+00:00'
The reason for this date shift is that Zuora converts the dateTime value
2014-11-30T05:30-08:00 before the query is completed. The date component of the converted value is the value used to complete the query.
Date fields are strictly treated as date fields and dateTime fields are strictly treated as dateTime fields. For example:
select AutoRenew from subscription where ContractEffectiveDate = '2015-02-28'
Incorrect formatting will return an