Export ZOQL
Zuora Export ZOQL (Zuora Object Query Language) is the query language you use to create data source exports through the Zuora API. Export ZOQL is more powerful than ZOQL because Export ZOQL queries can select data from related business objects in a single operation.
You can perform Export ZOQL queries in the following ways:
-
Use the AQuA API (recommended)
You can use this method to perform multiple queries or continuously query data.
-
Use the "CRUD: Create Export" API operation
You can use this method to create an Export object corresponding to a single query. See the Zuora API Reference for more information.
-
Create an Export object through the SOAP API (deprecated)
Overview of Queries
When you write an Export ZOQL query, you must specify the data source to select data from. Each data source provides all your business objects of a particular type. Most data sources also provide related business objects. This saves you the effort of constructing nested queries, and results in better performance.
For example, the Invoice Item data source provides every Invoice Item object in your Zuora tenant, along with related Account, Contact, Invoice, Product, Rate Plan, and Subscription objects. See Data Sources and Objects for a listing of the objects provided by each data source.
Names in Queries
You must use API names of data sources, objects, fields, and field values in Export ZOQL queries. API names do not necessarily match the corresponding names displayed in the Zuora user interface. For example, you must use "InvoiceItem" instead of "Invoice Item" or "Rechnungsposten" (German):
select BillToContact.FirstName from InvoiceItem
In the above example:
InvoiceItemis the API name of the Invoice Item data source.BillToContactis the API name of a Contact object that is related to an Invoice Item object.FirstNameis the API name of the First Name field in the Contact object.
InvoiceItem is also called the "base" object of the Invoice Item data source. BillToContact is called a "joined" object.
To determine API names:
-
For data sources and objects, refer to the listing in the Data Sources and Objects section, below.
-
For fields and field values, use the Describe object API operation.
Changes to the "Describe object" API operation were introduced in version 85 of the Zuora WSDL. See Changes to the Describe API for more information.
Prior to version 79 of the Zuora WSDL, some fields in data sources had different values from the corresponding fields in SOAP API objects. See Field Values in Export ZOQL Data Sources for more information.
Limitations
Characters per Query
A maximum of 20,000 characters is allowed in an Export ZOQL query.
File Size
The maximum export file size is 2047MB. If you have large data requests that go over this limit, you will get the following 403 HTTP response code from Zuora:
<security:max-object-size>2047MB</security:max-object-size>
Submit a request at Zuora Global Support if you require additional assistance.
We can work with you to determine if large file optimization is an option for you.
Data Sources and Objects
The following table lists the objects that are provided by each data source. Objects marked with * depend on Zuora features. See the "Availability of Data Source Objects" section for more information.
| Data Source | Objects |
|---|---|
|
Base object: Account Joined objects:
|
|
|
Base object: AccountingCode Joined objects:
|
|
|
Part of the Invoice Settlement feature. |
Base object: ApplicationGroup Joined objects:
|
| AR Transaction |
Base object: AR Transaction Joined objects:
|
|
Base object: BillingRun Note: The Joined objects:
|
|
| Billing Transaction |
Base object: Billing Transaction Joined objects:
|
| Booking Transaction |
Base object: Booking Transaction Joined objects:
|
|
Base object: Contact Joined objects:
|
|
|
Part of the Credit Balance feature. Deprecated if the Invoice Settlement feature is enabled. |
Base object: CreditBalanceAdjustment Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditMemo Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditMemoApplication Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditMemoApplicationItem Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditMemoItem Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditMemoPart Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditMemoPartItem Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: CreditTaxationItem Joined objects:
|
|
Part of the Custom Foreign Currency Exchange Rates feature. |
Base object: FXCustomRate Joined objects:
|
|
Part of the Prepaid with Drawdown feature. |
Base object: DailyConsumptionSummary Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: DebitMemo Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: DebitMemoItem Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: DebitTaxationItem Joined objects:
|
|
Limited Availability feature. |
Base object: DiscountAppliedMetrics Joined objects:
|
|
Base object: PaymentGatewayReconciliationEventLog Joined objects:
|
|
|
Base object: PaymentReconciliationLog Joined objects:
|
|
|
Part of the Unbilled Usage feature. |
Base object: GuidedUsage Joined objects:
|
|
Base object: Invoice Joined objects:
|
|
|
Deprecated if the Invoice Settlement feature is enabled. |
Base object: InvoiceAdjustment Joined objects:
|
|
Base object: InvoiceItem Joined objects:
|
|
|
Deprecated if the Invoice Settlement feature is enabled. |
Base object: InvoiceItemAdjustment Joined objects:
|
|
Deprecated if the Invoice Settlement feature is enabled. |
Base object: InvoicePayment Joined objects:
|
|
Requires Zuora Finance. |
Base object: JournalEntryItem Joined objects:
|
| Notification History - Email |
Base object: EmailHistory Joined objects:
|
| Notification History - Callout |
Base object: CalloutHistory Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: Order Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderAction Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderContact Joined objects: None |
|
Part of the Order Metrics feature. |
Base object: OrderElp Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderItem Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderLineItem Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderMrr Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderQuantity Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderTcb Joined objects:
|
|
Part of the Order Metrics feature. |
Base object: OrderTcv Joined objects:
|
|
Base object: Payment Joined objects:
|
|
|
Part of the Invoice Settlement feature. |
Base object: PaymentApplication Joined objects:
|
|
Part of the Invoice Item Settlement feature. |
Base object: PaymentApplicationItem Joined objects:
|
|
Base object: PaymentMethod Joined objects:
If you want to query data by joining Payment Method and Stored Credential Profile, use StoredCredentialProfile as the base object to achieve better query performance. |
|
|
Base object: PaymentMethodTransactionLog Joined objects:
|
|
|
Base object: UpdaterDetail Joined objects:
|
|
|
Part of the Invoice Settlement feature. |
Base object: PaymentPart Joined objects:
|
|
Part of the Invoice Item Settlement feature. |
Base object: PaymentPartItem Joined objects:
|
|
Base object: PaymentRun Joined objects:
|
|
| Payment Schedule |
Base object: PaymentSchedule Joined objects:
|
| Payment Schedule Item |
Base object: PaymentScheduleItem Joined objects:
|
| Payment Schedule Item Payment |
Base object: PaymentScheduleItemPart Joined objects:
|
|
Base object: PaymentTransactionLog Joined objects:
|
|
|
Part of the Prepaid with Drawdown feature. |
Base object: Fund Joined objects:
|
|
Prepaid Validity Period Summary Part of the Prepaid with Drawdown feature. |
Base object: PrepaidValidityPeriodSummary |
|
Base object: ProcessedUsage Joined objects:
|
|
|
Base object: Product Joined objects:
|
|
|
Base object: ProductRatePlanCharge Joined objects:
|
|
|
Base object: ProductRatePlanChargeTier Joined objects:
|
|
|
Base object: RatePlan Joined objects:
|
|
|
Base object: RatePlanCharge Joined objects:
|
|
|
Base object: RatePlanChargeTier Joined objects:
|
|
|
Part of the Unbilled Usage feature. |
Base object: RatingResult Joined objects:
|
|
Base object: Refund Joined objects:
|
|
|
Part of the Invoice Settlement feature. |
Base object: RefundApplication Joined objects:
|
|
Part of the Invoice Item Settlement feature. |
Base object: RefundApplicationItem Joined objects:
|
|
Deprecated if the Invoice Settlement feature is enabled. |
Base object: RefundInvoicePayment Joined objects:
|
|
Part of the Invoice Settlement feature. |
Base object: RefundPart Joined objects:
|
|
Part of the Invoice Item Settlement feature. |
Base object: RefundPartItem Joined objects:
|
| Revenue Recognition Events Transaction |
Base object: Revenue Recognition Events Transaction Joined objects: Entity |
|
Base object: RefundTransactionLog Joined objects:
|
|
|
Requires the Zuora Finance - Revenue feature. |
Base object: RevenueChargeSummaryItem Joined objects:
|
|
Requires the Zuora Finance - Revenue feature. |
Base object: RevenueEventItem Joined objects:
|
|
Revenue Event Item Credit Memo Item Requires the Zuora Finance - Revenue feature. Zuora strongly recommends that you only use this data source if the Invoice Settlement feature is enabled. |
Base object: RevenueEventItemCreditMemoItem Joined objects:
|
|
Revenue Event Item Debit Memo Item Requires the Zuora Finance - Revenue feature. Zuora strongly recommends that you only use this data source if the Invoice Settlement feature is enabled. |
Base object: RevenueEventItemDebitMemoItem Joined objects:
|
|
Revenue Event Item Invoice Item Requires the Zuora Finance - Revenue feature. |
Base object: RevenueEventItemInvoiceItem Joined objects:
|
|
Revenue Event Item Invoice Item Adjustment Requires the Zuora Finance - Revenue feature. |
Base object: RevenueEventItemInvoiceItemAdjustment Joined objects:
|
|
Requires the Zuora Finance - Revenue feature. |
Base object: RevenueScheduleItem Joined objects:
|
|
Revenue Schedule Item Credit Memo Item Requires the Zuora Finance - Revenue feature. Zuora strongly recommends that you only use this data source if the Invoice Settlement feature is enabled. |
Base object: RevenueScheduleItemCreditMemoItem Joined objects:
|
|
Revenue Schedule Item Debit Memo Item Requires the Zuora Finance - Revenue feature. Zuora strongly recommends that you only use this data source if the Invoice Settlement feature is enabled. |
Base object: RevenueScheduleItemDebitMemoItem Joined objects:
|
|
Revenue Schedule Item Invoice Item Requires the Zuora Finance - Revenue feature. |
Base object: RevenueScheduleItemInvoiceItem Joined objects:
|
|
Revenue Schedule Item Invoice Item Adjustment Requires the Zuora Finance - Revenue feature. |
Base object: RevenueScheduleItemInvoiceItemAdjustment Joined objects:
|
| Stored Credential Profile |
Base object: StoredCredentialProfile Joined object: PaymentMethod If you want to query data by joining Payment Method and Stored Credential Profile, use StoredCredentialProfile as the base object to achieve better query performance. |
|
Base object: Subscription Joined objects:
|
|
|
Base object: TaxationItem Joined objects:
|
|
|
Base object: Usage Joined objects:
|
Availability of Data Source Objects
Depending on the features that are enabled in your Zuora tenant, some joined objects may not be available or may not contain any data. The following joined objects depend on Zuora features:
- AccountingPeriod - Requires Zuora Finance
- BillToContactSnapshot - Requires the Preserve snapshot of bill-to and sold-to contacts when billing documents are posted? billing rule to be enabled
- Entity - Part of the Multi-entity feature
- JournalEntry - Requires Zuora Finance
- JournalRun - Requires Zuora Finance
- ParentAccount - Part of the Customer Hierarchy feature
- SoldToContactSnapshot - Requires the Preserve snapshot of bill-to and sold-to contacts when billing documents are posted? billing rule to be enabled
- TaxableItemSnapshot - Part of the Taxable Item Snapshot feature
Additionally, any existing Subscribe and Amend customers who are on Orders (not on Orders Harmonization) will not have access to the objects related to Amendments.
Additional Data Source Fields
- If you have defined custom fields for an object, your custom fields are available in every data source that provides the object. To specify a custom field in an Export ZOQL query, use the API name that you defined for the custom field.
- If you have enabled foreign currency conversion in Zuora, you can include currency conversion data in data source exports. Currency conversion data is available through additional fields in some objects. See Foreign Currency Conversion for Data Source Exports for more information.