Hi @p3y8eYWuij,
This is a great question and you've hit on quite a complex topic. Unfortunately, the documentation in this area is a bit fragmented and it's not so easy to piece together what's going on.
We're currently preparing a turorial that provides an overview of data queries/exports in Zuora. Since the tutorial addresses both of your questions, I've included a preliminary version below. I hope you'll find it helpful! If you have any follow up questions or want to discuss anything in more detail, please don't hesitate to reply.
~ ~ ~
Zuora supports two different data query languages, with different use cases:
Query objects of a particular type - If you want to retrieve fields of a single type of object, you can run a ZOQL query. For example:
select InvoiceDate, AccountId from Invoice
This query selects InvoiceDate and AccountId for each Invoice. See below for an example of how to run the query via the REST API.
Export data from a data source - If you want to retrieve fields of multiple types of object with a single query, you must run an Export ZOQL query. Export ZOQL queries are very similar to ZOQL queries, but Export ZOQL queries select data from data sources instead of individual objects.
Each data source represents a “base” type of object that is joined to related types of object. When you write an Export ZOQL query, you can select joined fields without needing to specify which types of object are joined. For example:
select InvoiceDate, Account.Id, Account.Name from Invoice
This query selects InvoiceDate for each Invoice, along with Id and Name for the Account that owns each Invoice. The query selects fields from the Invoice data source. See below for an example of how to run the query via the REST API.
When Zuora constructs the Invoice data source, Zuora joins Accounts to Invoices according to the value of AccountId for each Invoice. That is, for each Invoice, Zuora joins the Account that satisfies Account.Id = AccountId . Instead of exposing both AccountId and Account.Id in the data source, Zuora only exposes Account.Id in the data source.
See Describe object in the API Reference for how to determine which fields are exposed in data sources.
Example: Run a ZOQL Query
You can use Query to run a ZOQL query.
Send the following request:
POST /v1/action/query
{
"queryString": "select InvoiceDate, AccountId from Invoice"
}
Response from Zuora:
{
"records": [
{
"AccountId": "2c92c0f948f36bd90148f803e924768a",
"InvoiceDate": "2018-08-13",
"Id": "2c92c0855388c50301539b680c950e3a"
},
{
"AccountId": "2c92c0f948f8997b0148f8c600275a58",
"InvoiceDate": "2018-02-10",
"Id": "2c92c0855388c50301539b680d910e6e"
},
...
]
}
Notice that Zuora returns Id for each Invoice even though the query did not select Id .
Example: Run an Export ZOQL Query
You can use the AQuA API to run an Export ZOQL query:
Send the following request:
POST /v1/batch-query/
{
"format": "CSV",
"queries": [
{
"type": "zoqlexport",
"query": "select InvoiceDate, Account.Id, Account.Name from Invoice"
}
]
}
Response from Zuora:
{
"batches": [
{
"status": "pending",
"query": "select InvoiceDate, Account.Id, Account.Name from Invoice",
...
}
],
"status": "submitted",
"id": "2c92c0f865419a280165563d462d5da8",
"format": "CSV",
...
}
This response indicates that Zuora has started running the query and that the internal ID of the job is 2c92c0f865419a280165563d462d5da8 .
To retrieve the query results, first send the following request:
GET /v1/batch-query/jobs/2c92c0f865419a280165563d462d5da8
Response from Zuora:
{
"batches": [
{
"status": "completed",
"query": "select InvoiceDate, Account.Id, Account.Name from Invoice",
"recordCount": 148,
"fileId": "2c92c08565243f640165563d46970df3",
...
}
],
"status": "completed",
"id": "2c92c0f865419a280165563d462d5da8",
"format": "CSV",
...
}
The value of fileId is the internal ID of the file that contains the query results.
To download the query results, send the following Get files request:
GET /v1/files/2c92c08565243f640165563d46970df3
Response from Zuora:
Invoice: Invoice Date,Account: ID,Account: Name
2018-08-01,2c92c0f846f580ab014719bedfea14b9,ABC Company
2018-07-01,2c92c0f946f58862014715624e782bfc,Cloud Services
2018-05-20,2c92c0f8474c4d4c014756f6e5913796,North Finance
...
The AQuA API supports multiple simultaneous queries and is primarily intended to be used for data synchronization. The legacy operation CRUD: Create Export provides a slightly simpler way to run an Export ZOQL query, but this operation does not support the latest objects and fields.
... View more
