Export ZOQL
From API Documentation
Zuora Export ZOQL (Zuora Object Query Language) is the query language used to create Exports with the Z-Commerce API. Zuora Export ZOQL is similar to Z-Commerce API ZOQL, with only a few slight differences. The biggest difference is that with Exports, you query a datasource, not a Z-Commerce API object.
Similarities Between Export ZOQL and ZOQL
The query syntax for Exports is almost identical to regular ZOQL. For example:
select field_names from datasource
and
select field_names from datasource where filter_statements
A datasource is a Zuora object prejoined with additional, related objects, and every Z-Commerce API object is also a datasource. For example, the Account datasource not only has all of the fields associated to an Account, but also has additional objects connected to it, such as the Bill-To contact, Sold-To contact and default payment method. This allows you to query for these related fields that exist in other objects in a single query.
- Note: There is no need to join these objects (such as
WHERE Subscription.AccountId=Account.Id), as the objects are already linked within the datasource.
Key Differences Between Export ZOQL and ZOQL
Datasource vs. Object
Every API object within the Z-Commerce API is also a datasource. The Subscription datasource has access to all the standard Subscriptions, as well as the additional objects that are joined to it. This is exceptionally useful for reporting, business intelligence, and other applications where related information across various objects can be combined. For example, the Subscription datasource allows you to query for the related Account, Bill-to contact, Sold-to contact, and Default Payment Method fields.
The table outlines the key datasources and their prejoined objects.
| Key Datasources | Prejoined Objects |
|---|---|
| Account | DefaultPaymentMethod, BillToContact, SoldToContact |
| Billing Run | (No additional objects) |
| Contact | (No additional objects) |
| Invoice | Account, BillToContact, DefaultPaymentMethod, SoldToContact |
| InvoiceItem | Account, BillToContact, DefaultPaymentMethod, Invoice, Product, ProductRatePlan, ProductRatePlanCharge, RatePlan, RatePlanCharge, Subscription, SoldToContact |
| InvoicePayment | Account, BillToContact, Invoice, Payment, PaymentMethod, SoldToContact |
| Payment | Account, BillToContact, SoldToContact, PaymentMethod |
| PaymentMethod | (No additional objects) |
| PaymentTransactionLog | Account, BillToContact, Payment, PaymentMethod, SoldToContact |
| Product | CreatedById, CreatedDate, Description, EffectiveEndDate, EffectiveStartDate, Id, Name, SKU, UpdatedById, Updated Date |
| ProductRatePlanCharge | Product, ProductRatePlan |
| RatePlan | Account, DefaultPaymentMethod, BillToContact, Product, ProductRatePlan, SoldToContact, Subscription |
| RatePlanCharge | Account, DefaultPaymentMethod, BillToContact, Product, ProductRatePlan, ProductRatePlanCharge, SoldToContact, Subscription |
| Refund | Account, BillToContact, SoldToContact |
| RefundInvoicePayment | Account, BillToContact, Invoice, InvoicePayment, Payment, PaymentMethod, Refund, SoldToContact |
| RefundTransactionLog | Account, BillToContact, Refund, SoldToContact |
| Subscription | Account, BillToContact, DefaultPaymentMethod, SoldToContact |
| TaxationItem | Account, Amendment, BillToContact, Invoice, InvoiceItem, Product, ProductRatePlanCharge, RatePlan, RatePlanCharge, SoldToContact, Subscription |
| Usage | Account, Amendment, BillToContact, Product, ProductRatePlanCharge, RatePlan, RatePlanCharge, SoldToContact, Subscription |
To view all fields for any datasource, you can use the Zuora REST API URL:
https://www.zuora.com/apps/api/describe/datasource
Where datasource is the name of the datasource (such as Account, RatePlanCharge, or InvoiceItem). You will need to specify your Zuora credentials using Basic Authentication or by setting a Cookie with a valid Zuora Session Id (See Creating an Export for additional details).
For example, the following URL will return an XML description of the datasource:
https://www.zuora.com/apps/api/describe/RatePlan
While any base object in the Z-Commerce API is also a datasource, not every datasource offers additional pre-joined objects. For example, the PaymentMethod datasource does not have any additional objects joined to it, and thus the PaymentMethod datasource is essentially equivalent to the PaymentMethod Z-Object.
Query Examples
To query for all Accounts that have active subscriptions that pay with a Visa credit card, issue the following query:
SELECT Subscription.Name, Account.Name FROM Subscription WHERE Subscription.Status='Active' AND DefaultPaymentMethod.CreditCardType='Visa'
You need not use the datasource name as a qualifier for its fields. Thus, an identical query to the one above would be:
SELECT Name, Account.Name FROM Subscription WHERE Status='Active' AND DefaultPaymentMethod.CreditCardType='Visa'
Asterisk Wild Card Support
If you want to get all the fields for a given object, you can use an asterisk in place of the field names:
SELECT * FROM Account
This would return all the fields on the Account datasource, not just the Account object. Thus, the above query would return all the fields with the DefaultPaymentMethod, BillToContact, and the SoldToContact.
Simplified Dates
Since Zuora stores all dates as timestamps, working with dates can be problematic. Often, most reports are meant for the last week, or last day. However, getting the timestamps aligned properly (for example, setting the start time to be 00:00:00-08:00 and the ending time to be 23:59:59-08:00), can be difficult. Because of this, Zuora allows you to query for simplified date ranges.
DAYS AGO - Select the last n days, setting the start and time to encompass the entire days
SELECT AccountNumber, Balance, BillToContact.Firstname, BillToContact.Lastname, BillToContact.Homephone from Account WHERE Balance > 0 and BillToContact.Updateddate >= 'today - 30 days'
NO TIME STAMP - Select data for a given day, without specifying a timestamp
SELECT * FROM Invoice where CreatedDate = '2010-02-01'
SELECT * FROM Invoice WHERE CreatedDate > '2010-02-01'
OFFSETS - Simply specify a time period with an offset
SELECT Invoice.InvoiceNumber, Account.Name FROM Invoice WHERE createddate < 'today - 30 days' #Query for the last 30 days of data
SELECT * FROM invoice WHERE duedate >= 'today' AND duedate < 'today + 1 week' #Query for invoices whose due dates are due within the coming week.
NOW
SELECT * FROM invoice WHERE createddate = 'now - 1 day'
The now keyword specifies the current timestamp. This query will return all invoices equal to the current timestamp minus 24 hours. For example, if the current time is 6 PM on January 15, now - 1 day will return all invoices generated at 6 PM on January 14.
To show all invoices generated in the last 24 hours, use >='now - 1 day'. For example:
SELECT * FROM invoice WHERE createddate >= 'now - 1 day'
TODAY
SELECT * FROM invoice WHERE createddate = 'today - 1 day'
The today keyword specifies the current day. This query will return all invoices from the previous calendar day, regardless of the time stamp (in other words, all invoices from 00:00 to 23:59).
Wildcard Support For Joined Objects
If you want to get all the fields for an associated object, you can do that using asterisk wild card:
SELECT ccountnumber, Balance, billtocontact.* FROM Account
You can optionally specify the primary object name before its fields. So, this query:
SELECT Accountnumber, Balance, BillToContact.Firstname, BillToContact.Lastname, BillToContact.Homephone FROM Account
is equivalent to:
SELECT Account.accountnumber, Account.balance, Billtocontact.firstname, Billtocontact.lastname, Billtocontact.homephone FROM Account
Reserved and Escaped Characters
The only character that needs to be escaped is the single quote ('). To escape it, put another single quote in front (''), just as in SQL.
SELECT AccountNumber FROM account WHERE Name = 'San Francisco''s Finest'
Filter Statements
| Data Type | Supported Operators | Example | Notes |
|---|---|---|---|
| boolean | =, != | select AccountNumber from account where AutoPay != true
| Use 'true' or 'false'. Make sure to include quotation marks around the value. (We also accept 't'/'f' and 'yes'/'no') |
| dateTime | =, !=, >=, <= | Datetime with default (Pacific) timezone:
Date with default (Pacific) timezone:
Datetime with Eastern Standard Time:
Date with GMT:
| Use the following format for dateTime values (ISO 8601):
If a date or time is a single-digit number, supply a leading zero (0) as shown in the example. Several fields are optional, including the seconds (ss) and milliseconds (SSS). As you can see, none of the examples includes milliseconds. The timezone (Z) is also optional. If you leave it out, then Pacific Time will be used (GMT-8 standard; GMT-7 during daylight savings). To specify GMT, use the letter 'Z'. To specify any other timezone, use an offset from GMT in the form:
If you only want to specify a date, you can leave out the time entirely:
Timezones are still treated the same way. If you want something other than Pacific Time, you should specify it. The last example shows this. |
| number | =, !=, >=, <= | select AccountNumber from account where BillCycleDay >= 15
| Quotes are optional around this value. |
| string | =, !=, >=, <= | select AccountNumber from account where Status = 'Draft'
| Strings are enclosed in single quotes ('like this'). |
| string | like | select AccountNumber from account where Name like 'Z%'
| The string matches the specified query. This can be used to find strings using one of two wildcard characters: the percent character,%, which selects everything, or the underscore character, _, which selects a single character. You must enclose the value in single quotes (').
For example, the following query will return account numbers for all accounts where the value in the Name field starts with the letter Z followed by any characters. |
| string | and | select AccountNumber from account where AutoPay = true and Status='Active'
| This is a logical operator that requires both specified items to be true. For example, in the following statement, IDs will only be returned for accounts where AutoPay is set to true and Status is Active. |
| string | or | select AccountNumber from account where Status='Draft' or Status='Active'
| This is a logical operator that requires either one or the other of a specified item to be true. (Both can also be true.) For example, in the following statement, IDs will be returned for all accounts whose status is either Draft or Active (or both). |
| string | not | select * from Account where name = 'Eve' or name like 'Adam%' or not createdDate = '2010-01-01'
| This is a logical operator that requires the specified item to be false. For example, the following statement returns information from all accounts with the name "Eve," a variation that includes "Adam," and that were not created on January 1, 2010:
Note: Support for NOT was added in version 30 of the Z-Commerce API. |
Using Parentheses With OR and NOT
You can use parentheses to nest OR and NOT conditions. For example:
select id from Account where (name like 'Adam %' and (status = 'Active' or status = 'Canceled'))
Note: Support for parentheses was added in version 30 of the Z-Commerce API.
Order By
You can use an order by clause to sort results in ascending (ASC) or or descending (DESC) in the order by clause.
By default, the ZOQL sorts results in ascending order if you do not specify ASC or DESC in the order by clause.
For example, the following two statements will return results in ascending order:
select * from Account order by ID" = "select * from Account order by ID asc
select * from Account order by ID" = "select * from Account order by ID
Aggregate Functions
As of release 34.0, Export ZOQL supports aggregate functions.
Supported Functions
Export ZOQL supports the following aggregate functions:
- avg(): Returns the average value
- count(): Returns the number of rows
- max(): Returns the largest value
- min(): Returns the smallest value
- sum(): Returns the sum
Limitations
The aggregate functions have the same limitations as standard SQL aggregate functions, including the following:
- The sum() and avg() functions support only numeric values.
- You can specify a maximum of five fields when using group by.
- The alias function is available only for use with the aggregate functions. You cannot replace an aggregate function with its alias in having or orderby clauses.
- You cannot nest aggregate functions.
- You cannot use an index as a parameter for the count() command. For example, you cannot use the command
function count(1) or count(2).
