Query on Big Objects in Salesforce

Query on Big Objects

Big objects can be queried by using SOQL or Async SOQL and because it is designed to handle a large amount of data that can be kept within a big object. We can use Async SOQL queries that run by using Rest API. Async SOQL can do queries on complex queries and then the custom Object stores the result, So it seems like only one Async SOQL can run at any given time or it run in a concurrent way.

Note: In Asynchronous SOQL queries, there is one limitation and that is at one time, you can do only one concurrent query.

Async SOQL and SOQL having so many of the same functionality. So you will decide according to the requirement when there is a use of Async SOQL queries and standard SOQL queries.

Standard SOQL:

If you want to changes in returned results instantly in a block of apex code, you can use the SOQL query when you want to query on a small amount of data.

Async SOQL:

It is Async SOQL so it’s working on Async operation you can query on millions of records.

We can’t perform the aggregate function on queries because there is issued against indexed fields because indexed fields must be in order.

Query on Big Objects using SOQL

The query on Standard and custom objects is different than the query on Big objects. When you are using SOQL queries you need to query in all fields starting from the first field and do not skip any field at the end in query because if you will skip any field it will return wrong data.

For example: If you are query on three fields, you can query on all three fields.

Ex:

SELECT LastName__c, FirstName__c, Phone__c FROM Phone_book__b WHERE LastName__c=’001R01110302D3′ AND FirstName__c=’PC’ AND Phone__c =’243566′

Note: Remember do not gap in the query because then it doesn’t work.

Query on Big Objects using Async SOQL

Using Async SOQL, after the query on custom big object you can direct the results directly into our target object. We extract the first field and second field data from a specific third field in our custom big object to our target object, then further we can use this results at anywhere for reports and for any analysis tool.

Running process of Async SOQL Queries

The query use in Async SOQL must be run in JSON-formate in the POST request because it runs in key-value pairs.

POST Request Body

{
    "query": "SELECT fieldA__c,
    fieldB__c FROM SourceObject__c",
    "operation": "insert",
    "targetObject": "TargetObject__c",
    "targetFieldMap": {
        "fieldA__c":"fieldATarget__c",
        "fieldB__c ":"fieldBTarget__c"
    },
    "targetValueMap": {
        "$JOB_ID":"BackgroundOperationLookup__c",
        "Copy fields from source to target":"BackgroundOperationDescription__c"
    }
}

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

POST Response Body

The response body contains the query’s jobId, the status of your query, and any relevant messages.

{
    "jobId": "08PD000000003kiT",
    "message": "", "query": "SELECT fieldA__c,
    fieldB__cFROM SourceObject__c",
    "status": "New",
    "targetObject": "TargetObject__c",
    "targetFieldMap": {
        "fieldA__c":"fieldATarget__c",
        "fieldB__c":"fieldBTarget__c"
    },
    "targetValueMap": {
        "$JOB_ID":"BackgroundOperationLookup__c",
        "Copy fields from source to target":"BackgroundOperationDescription__c"
    }
}

Tracking the Status of Your Query

You need to specify the jobID with the HTTP GET request so that if you want to track the status of a query and its results.

https://yourInstance.salesforce.com/services/data/v38.0/async-queries/

The result of response returned after the query is similar to the initial POST response with the updated status.

Hope this will help you!

Thanks.