Visualforce action method to search Big Object with LIKE

Question

I built a visualforce page on Big object to show Account name which we are querying. As the big object is not supporting Like' in SOQL command, was unable to get the desired result and getting the following error 'Big object unsupported Operation', any idea how can we overcome this?.

public class ctrlAccountSearch
{

    public string strAccSearchString {get; set;}
    public List<Email_Caseb__b> lstAccount {get; set;}
    public boolean isResultDisplay {get; set;}

    //  constructor
    public ctrlAccountSearch(){
        lstAccount = new List<Email_Caseb__b>();
        isResultDisplay = false;
    }

    //  Search Function
    public void SearchAccountRec(){
        isResultDisplay = true;
        string strLikeString = '%'+strAccSearchString+'%';
        string strSOQL = 'select id, name__c,Account__c from Email_Caseb__b where name__c Like: strLikeString';
        lstAccount = database.query(strSOQL);
    }
}

Answer 1

By looking up the documentation : like operator doesnt seem to be possible with Big Objects SOQL.

Build an index query starting from the first field defined in the index, without gaps between the first and last field in the query. You can use =, <, >, <=, or >=, or IN on the last field in your query. Any prior fields in your query can only use the = operator. The !=, LIKE, NOT IN, EXCLUDES, and INCLUDES operators are not valid in any query.

The options that i can think of are:

If i have the same account created in account object, i would run the like query there get the account id or exact name and then use a in clause to get data from big objects.

Another option is to get the data using other filter conditions if possible like created date and then filter by name in UI(javascript) but that would be highly inefficient.

Answer 2

The LIKE operator is supported in Async SOQL with BigObjects, unlike SOQL. However, it's unlikely you'll want to use Async SOQL in a Visualforce page due to the higher engineering overhead and the fact that it is an asynchronous search.

This is a situation that looks like a requirements mis-match to me. If you need that level of fuzzy searching of a BigObject in a user-facing, real-time context, you might need to reevaluate using BigObjects. Alternately, if you must use BigObjects, you may need to build a more structured search solution using the SOQL options that are available.