function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Janakiraman ChandravadhanJanakiraman Chandravadhan 

Big Object SOQL Limitation in Apex class

Due to the Data storage limits in our Salesforce Instance, we have created a big object with 3 Indexed fields. Now, when I use this Big object in an apex class to search for a particular value from the Big object records, I had to include all 3 indexed fields in the WHERE Clause due to Big Object SOQL Limitation. As per the existing Apex class logic, we should be able to search even with one parameter. Please help me to find a way to bypass Big object SOQL Limitation without changing the current Apex logic?

If I execute the below Apex class as is, I am getting an error: "Filters may not have any gaps within the composite key" in the Workbench Rest API explorer
 
@RestResource(urlMapping='/locationservice')
global class LocationService 
{
    @HttpGet
    global static void doGet() 
    {
        //variable to collect the params from apexrest url
        string Zcode = RestContext.request.params.get('Zipcode');
        string ste  = RestContext.request.params.get('State');
        string city = RestContext.request.params.get('City');
        string mlrange = RestContext.request.params.get('milerange');
        string lat;
        string lon;
{
if(ste!=null)
    {
list<GeoCoordinate__b> lstPostalCodeMDT = [select Latitude__c, Longitude__c from GeoCoordinate__b where State__c= :ste];//soql to query Geocoordinate Big Object
        if(!lstPostalCodeMDT.isempty())
        {
           lat = lstPostalCodeMDT.get(0).Latitude__c;
           lon = lstPostalCodeMDT.get(0).Longitude__c;
        }
    }
if(city!=null)
    {
list<GeoCoordinate__b> lstPostalCodeMDT = [select Latitude__c, Longitude__c from GeoCoordinate__b where City__c = :city];//soql to query Geocoordinate Big Object
        if(!lstPostalCodeMDT.isempty())
        {
           lat =  lstPostalCodeMDT.get(0).Latitude__c;
           lon = lstPostalCodeMDT.get(0).Longitude__c;
        }
    }
if(Zcode!=null)
    {
        list<GeoCoordinate__b> lstPostalCodeMDT = [select Latitude__c, Longitude__c from GeoCoordinate__b where Zip_code__c = :Zcode];//soql to query Geocoordinate Big Object
        if(!lstPostalCodeMDT.isempty())
        {
           lat = lstPostalCodeMDT.get(0).Latitude__c;
           lon = lstPostalCodeMDT.get(0).Longitude__c;
        }
    }
}
decimal lt = decimal.valueOf(lat);
decimal lg = decimal.valueOf(lon);
decimal mlran = decimal.valueOf(mlrange);

//SOQL to fetch the dealer data from Account object
list<account> locationList = [SELECT Id, Name, Phone, ShippingStreet, ShippingCity, ShippingState, ShippingCountry, ShippingLatitude, ShippingLongitude, ShippingPostalCode, Website, distance(ShippingAddress, geolocation (:lt, :lg), 'mi') distance FROM Account WHERE distance(ShippingAddress, geolocation (:lt, :lg),'mi') < :mlran ORDER BY distance(ShippingAddress, geolocation (:lt, :lg), 'mi') LIMIT 20];

//pass the results in JSON format     
String locationJSON = JSON.serialize(locationList);
RestContext.response.addHeader('Content-Type', 'application/json');
RestContext.response.responseBody = Blob.valueOf(locationJSON);    
    }
}

 
Alain CabonAlain Cabon
Hi,

"include all 3 indexed fields in the WHERE Clause due to Big Object SOQL Limitation." ... if you want to use the last index field indeed (that's right and you need therefore the first and second index field, strong constraint).

The only workaround is the ASync SOQL which needs a new custom object (working dataset) to store the result of the request on the big object.

 
Janakiraman ChandravadhanJanakiraman Chandravadhan
@Alain Cabob, Thank you for the response. Was wondering if there could be a different way which I am missing. But this helps.