Running Async SOQL Queries

Formulating Your Async SOQL Query

To use Async SOQL effectively, it’s helpful to understand its key component and other related concepts. Each query is formulated in the POST request as a JSON-encoded list of three or four key-value pairs.

Request body for POST

Name	Type	Description	Required or Optional	Available Version
`query`	String	Specifies the parameters for the SOQL query you want to execute. The `FROM` object must be a big object.	Required	35.0
`operation`	String	Specify whether the query is an insert or upsert. If the record doesn’t exist, an upsert behaves like an insert. Upsert is not supported for big objects Note	Optional	39.0
`targetObject`	String	A standard object, custom object, external object, or big object into which to insert the results of the query.	Required	35.0
`targetFieldMap`	Map<String, String>	Defines how to map the fields in the query result to the fields in the target object. When defining the `targetFieldMap` parameter, make sure that the field type mappings are consistent. If the source and target fields don’t match, these considerations apply. Any source field can be mapped onto a target text field. If the source and target fields are both numerical, the target field must have the same or greater number of decimal places than the source field. If not, the request fails. This behavior is to ensure that no data is lost in the conversion. If a field in the query result is mapped more than once, even if mapped to different fields in the target object, only the last mapping is used. Note	Required	35.0
`targetValueMap`	Map<String, String>	Defines how to map static strings to fields in the target object. Any field or alias can be used as the `TargetValueMap` value in the SELECT clause of a query. You can map the special value, $JOB_ID, to a field in the target object. The target field must be a lookup to the Background Operation standard object. In this case, the ID of the Background Operation object representing the Async SOQL query is inserted. If the target field is a text field, it must be at least 15–18 characters long. You can also include any field or alias in the SELECT clause of the `TargetValueMap`. They can be combined together to concatenate a value to be used.	Optional	37.0
`targetExternalIdField`	String	The ID of the target sObject. Required for upsert operations.	Optional	39.0

This simple Async SOQL example queries SourceObject__b, a source big object, and directs the result to TargetObject__c, a custom object. You can easily map the fields in the source object to the fields of the target object in which you want to write the results.

Example URI
Example POST request body

The response of an Async SOQL query includes the elements of the initial POST request.

Response body for POST

Property Name	Type	Description	Filter Group and Version	Available Version
`jobId`	String	The ID of the Async SOQL query. This ID corresponds to an entry in the Background Operation standard object. It matches the ID that is used in the `targetValueMap` when $JOB_ID is used. To get the status of an async query job, use this ID in an Async Query, Status request (`/async-queries/jobId`).	Big, 35.0	35.0
`message`	String	A text message that provides information regarding the query, such as an error message if the query failed.	Big, 37.0	37.0
`operation`	String	Specify whether the query is an insert or upsert. If the record doesn’t exist, an upsert behaves like an insert. Upsert is not supported for big objects Note	Big, 39.0	.39.0
`query`	String	Specifies the parameters for the SOQL query you want to execute. The `FROM` object must be a big object.	Big, 35.0	35.0
`status`	String	Status of an async query job. `Canceled`—The job was canceled before it could be run. `Complete`—The job was successfully completed. `Failed`—The job failed after the system submitted it or because the request exceeded the Async SOQL limits. The message field provides details on the reason for failure. `Running`—The job is running successfully, and the org hasn’t exceeded any limits. `Scheduled`—The new job has been created and scheduled, but is not yet running. `New`—The job has been created but is not yet scheduled.	Big, 35.0	35.0
`targetExternalIdField`	String	The ID of the target sObject. Required for upsert operations.	Big, 39.0	39.0
`targetFieldMap`	Map<String, String>	Defines how to map the fields in the query result to the fields in the target object. When defining the `targetFieldMap` parameter, make sure that the field type mappings are consistent. If the source and target fields don’t match, these considerations apply. Any source field can be mapped onto a target text field. If the source and target fields are both numerical, the target field must have the same or greater number of decimal places than the source field. If not, the request fails. This behavior is to ensure that no data is lost in the conversion. If a field in the query result is mapped more than once, even if mapped to different fields in the target object, only the last mapping is used. Note	Big, 35.0	35.0
`targetValueMap`	Map<String, String>	Defines how to map static strings to fields in the target object. Any field or alias can be used as the `TargetValueMap` value in the SELECT clause of a query. You can map the special value, $JOB_ID, to a field in the target object. The target field must be a lookup to the Background Operation standard object. In this case, the ID of the Background Operation object representing the Async SOQL query is inserted. If the target field is a text field, it must be at least 15–18 characters long. You can also include any field or alias in the SELECT clause of the `TargetValueMap`. They can be combined together to concatenate a value to be used.	Big, 37.0	37.0
`targetObject`	String	A standard object, custom object, external object, or big object into which to insert the results of the query.	Big, 35.0	35.0

Example POST response body

Tracking the Status of Your Query

To track the status of a query, specify its jobID with an HTTP GET request.

You must have explicit read access to the BackgroundOperation standard object to track the status of an Async SOQL query. See Object Permissions.

Important

The response is similar to the initial POST response but with updated status and message fields to reflect the status.

Example GET response body

You can get status information for all queries with the following HTTP GET request.

Example GET response body

Canceling a Query

You can cancel a query using an HTTP DELETE request by specifying its jobId.

Canceling a query that has already completed has no effect.

Note

Handling Errors in Async SOQL Queries

Two different types of errors can occur during the execution of an Async SOQL query.

An error in the query execution
One or more errors writing the results into the target object

Problems in executing the job cause some errors. For example, an invalid query was submitted, one of the Async SOQL limits was exceeded, or the query caused a problem with the underlying infrastructure. For these errors, the response body includes a status of Failed. The message parameter provides more information on the cause of the failure.

Other times, the query executes successfully but encounters an error while attempting to write the results to the target object. Because of the volume of data involved, capturing every error is inefficient. Instead, subsets of the errors generated are captured and made available. Those errors are captured in the BackgroundOperationResult object and retained for seven days. You can query this object with the Async SOQL query jobID to filter the errors for the specific Async SOQL query. Async SOQL job info is retained for a year.

Big Objects Implementation Guide

Formulating Your Async SOQL Query

Tracking the Status of Your Query

Canceling a Query

Handling Errors in Async SOQL Queries

See Also