Big Object Query Examples

Understand some of the common big object querying use cases.

Customer 360 Degree and Filtering

In this use case, administrators load various customer engagement data from external sources into Salesforce big objects and then process the data to enrich customer profiles in Salesforce. The goal is to store customer transactions and interactions—such as point-of-sale data, orders, and line items—in big objects, and then process and correlate that data with your core CRM data. Anchoring customer transactions and interactions with core CRM data provides a richer 360-degree view that translates to an enhanced customer experience.

Batch Apex is the best choice for automated processing on a big object or ApiEvent, ReportEvent, or ListViewEvent. This example shows how to add processing that references correlated data.

Run a batch Apex query on a big object and correlate the Contact information associated with that big object.

1public class QueryBigObjectAndContact implements Database.Batchable<sObject> {
2    private String key;
3	public QueryBigObjectAndContact(String keyParam) {
4        key = keyParam
5    }
6    
7    public Iterable<SObject> start(Database.BatchableContext BC) {
8		return [SELECT Big_Object_Field__c, Account__c FROM Big_Object__b WHERE Big_Object_Primary_Key > key LIMIT 50000]
9    }
10
11    public void execute(Database.BatchableContext bc, List<Big_Object__b> bos){
12        // process the batch of big objects and associate them to Accounts
13        Map<Id, Big_Object__b> accountIdToBigObjectMap = new Map<Id, Big_Object__b>();
14        for (Big_Object__b bigObject : bos) {
15            accountIdToBigObjectMap.put(bigObject.Account__c, bigObject);
16            key = bigObject.Big_Object_Primariy_Key__c
17        }
18        Map<Id, Account> accountMap = new Map<Id, Account>(
19            [SELECT Id, Name, ... FROM Account WHERE Id IN :accountIdToBigObjectMap.keySet()]
20        );
21        for (Id accountId : accountMap.keySet()) {
22            Big_Object__b bigObject = accountIdToBigObjectMap.get(accountId);
23            Account account = accountMap.get(accountId);
24            // perform any actions that integrate the big object and Account
25        }
26    }
27    public void finish(Database.BatchableContext bc){
28        // You can daisy chain additional calls using the primary key of the big object to get around the 50k governor limit
29        QueryBigObjectAndContact nextBatch = new QueryBigObjectAndContact(key);
30        Database.executeBatch(nextBatch);
31    }
32}

Field Audit Trail

This example shows how to query FieldHistoryArchive and analyze a large number of results in a CSV format.

Example URI

1/services/data/vXX.X/jobs/query

Example Post Request

1{
2    "operation": "query",
3    "query": "SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValueFROM FieldHistoryArchive WHERE FieldHistoryType = ‘Account’ AND CreatedDate > LAST_MONTH"
4}

Use the Get Results for a Query Job resource.

Example CURL Request

1curl --include --request GET \
2--header "Authorization: Bearer token" \
3--header "Accept: text/csv" \
4https://instance.salesforce.com/services/data/vXX.X/jobs/query/750R0000000zxr8IAA/results ?maxRecords=50000

This request results in a CSV file that can be examined for auditing purposes.

Real-Time Event Monitoring

With Real-Time Event Monitoring you can track who is accessing confidential and sensitive data in your Salesforce org. You can view information about individual events or track trends in events to swiftly identify unusual behavior and safeguard your company’s data. These features are useful for compliance with regulatory and audit requirements.

With Real-Time Events, you can monitor data accessed through API calls, report executions, and list views. The corresponding event objects are called ApiEvent, ReportEvent, and ListViewEvent. Querying these events covers many common scenarios because more than 50% of SOQL queries occur using the SOAP, REST, or Bulk APIs. Key information about each query—such as the username, user ID, rows processed, queried entities, and source IP address—is stored in the event objects. You can then run SOQL queries on the event objects to discover user activity details.

For more information, see Real-Time Event Monitoring.

This example shows how to query and analyze an event big object using a field’s contents.

1public class EventMatchesObject implements Database.Batchable<sObject> {
2    private String lastEventDate;
3
4    public EventMatchesObject(String lastEventDateParam) {
5        lastEventDate = lastEventDateParam;
6    }
7
8    public Iterable<SObject> start(Database.BatchableContext bc) {
9        return [SELECT EventDate, EventIdentifier, QueriedEntities, SourceIp, Username, UserAgent FROM ApiEvent WHERE EventDate > lastEventDate LIMIT 50000]
10    }
11
12    public void execute(Database.BatchableContext bc, List<ApiEvent> events){
13        // Process this list of entities if a certain attribute matches
14        for (ApiEvent event: events) {
15            String objectString = 'Patent__c';
16            String eventIdentifier = event.EventIdentifier;
17            if (eventIdentifier.contains(objectString) {
18                // Perform actions on the event that contains 'Patent__c'
19            }
20            lastEventDate = format(event.EventDate);
21        }
22    }
23
24    public void finish(Database.BatchableContext bc){         
25        // You can daisy chain additional calls using EventDate or other filter fields to get around the 50k governor limit
26        EventMatchesObject nextBatch = new EventMatchesObject(lastEventDate);
27        Database.executeBatch(nextBatch);
28    }
29}

Aggregate Queries

This example shows an alternative for aggregate queries similar to the COUNT() method.

1public class CountBigObjects implements Database.Batchable<sObject> {
2    private Integer recordsCounted;
3    private String key;
4
5	public CountBigObjects(Integer recordsCountedParam, String keyParam) {
6        recordsCounted = recordsCountedParam
7        key = keyParam
8    } 
9
10    public Iterable<SObject> start(Database.BatchableContext bc) {
11        return [SELECT Custom_Field__c FROM Big_Object__b LIMIT 25000]
12    }
13
14    public void execute(Database.BatchableContext bc, List<Big_Object__b> bos){
15        // process the batch of big objects and associate them to Accounts
16        Map<Id, Big_Object__b> accountIdToBigObjectMap = new Map<Id, Big_Object__b>();
17        for (Big_Object__b bigObject : bos) {
18            accountIdToBigObjectMap.put(bigObject.Account__c, bigObject);
19        }
20        Map<Id, Account> accountMap = new Map<Id, Account>(
21            [SELECT Id, Name, ... FROM Account WHERE Id IN :accountIdToBigObjectMap.keySet()]
22        );
23        for (Id accountId : accountMap.keySet()) {
24            Big_Object__b bigObject = accountIdToBigObjectMap.get(accountId);
25            Account account = accountMap.get(accountId);
26            // perform any actions that integrate the big object and Account
27        }
28    }
29
30    public void finish(Database.BatchableContext bc) {         
31        // You can daisy chain additional calls using the primary key of the big object to get around the 50k governor limit
32        CountBigObjects nextBatch = new CountBigObjects(recordsCounted, key);
33        Database.executeBatch(nextBatch);
34    }
35}

Big Objects Implementation Guide

Big Object Query Examples

Customer 360 Degree and Filtering

Field Audit Trail

Real-Time Event Monitoring

Aggregate Queries