Table of contents
Overview
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 a few differences. The biggest difference is that with Exports, you query a data source, not a Z-Commerce API object.
Data Sources
A data source is a Zuora object prejoined with additional, related objects. Every data source object is a Z-Commerce API object. For example, the Account datasource not only has all of the fields associated with 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. This is useful for reporting, business intelligence, and other applications where related information across various objects can be combined. For example, the Subscription data source allows you to query for the related account, bill to contact, sold to contact, and default payment method fields.
The objects are already linked within the data source. Because of this, you do not need to join these objects (such as WHERE Subscription.AccountId=Account.Id).
Data Sources and Objects
The following table lists the data sources and their prejoined objects. See Data Sources for detailed information.
| 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 | (No additional objects) |
| 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 |
The following data sources are in controlled release. Contact Zuora Global Support to gain access to these data sources.
Viewing Data Source Fields
To view all fields for any data source, you can use the Zuora REST API URL:
https://www.zuora.com/apps/api/describe/datasource
Where datasource is the name of the data source (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 data source:
https://www.zuora.com/apps/api/describe/RatePlan
While any base object in the Z-Commerce API is also a data source, not every data source offers additional pre-joined objects. For example, the PaymentMethod data source does not have any additional objects joined to it. Because of this, the PaymentMethod data source is equivalent to the PaymentMethod Z-Object.
Related
- Select Statement
- This topic provides usage information about the Export ZOQL select statement.
- Aggregate Functions
- As of release 34.0, Export ZOQL supports aggregate functions.
- Export ZOQL Clauses
- Export ZOQL supports the order by, group by, and having clauses.
- Filter Statements
- Export ZOQL supports multiple filter statements that you can use when building queries.
- Dates and Datetimes
- Zuora stores all dates as timestamps. Often, most reports are meant for the last week, or the last day. Because aligning the timestamps correctly can be difficult (for example, setting the start time to be 00:00:00-08:00 and the ending time to be 23:59:59-08:00), Export ZOQL allows you to query for simplified date ranges.
Comments