Multiple filter in the statement

multiple
filter
index
secondary

#1

Trying multiple filter in the statement but it doesnt work. throwing exception com.aerospike.client.AerospikeException: Error Code 4: Parameter error

Here is the code Statement statement = new Statement(); statement.setNamespace(namespace); statement.setSetName(“set1”); statement.setBinNames(“bin1”,“bin2”); statement.setFilters(Filter.equal(“type”, “gal”),Filter.equal(“refid”, "1)); RecordSet rs = asi.client.query(null, statement);


#2

Re: “Parameter error” is returned here, most likely because the “namespace” used is not configured in the aerospike.conf file.

Re: multiple filter. The recipe to doing predicate on multiple columns can be found:

http://www.aerospike.com/community/labs/query_multiple_filters.html


#3

It works If I try with single filter value. Statement statement = new Statement(); statement.setNamespace(namespace); statement.setSetName(“set1”); statement.setBinNames(“bin1”,“bin2”); statement.setFilters(Filter.equal(“type”, “gal”)); RecordSet rs = asi.client.query(null, statement);

Getting exception for multiple filter. Cant we use multiple filter like this statement.setFilters(Filter.equal(“type”, “gal”),Filter.equal(“refid”, "1));


#4

Currently Aerospike does not support multiple predicates directly with select statements. The multiple predicate filtering can be done using the UDF stream functionality, stated in example: http://www.aerospike.com/community/labs/query_multiple_filters.html2

Thanks.


#5

Does anyone know when multiple filters will be implemented? Thanks.

Regards, Joe


#6

We currently do not have concrete time-frame for delivering this. But implementing this natively is certainly on the roadmap.

Just wondering if the current streamUDF alternative can suffice for now? We’ve mostly found the additional bin filters are on an already smaller subset of data after first bin is indexed. Additional use cases always help us out in determining product priority. Feel free to also reach out privately

Thanks.


#7

The streadUDF solution worked for our case.

For our project we don’t have a need for multiple filter statement so no urgency from our side. However, I can imagine requirements where they need to build filters at run time.

Regards,


#8

Thanks for the additional info.


#10

Sorry for reopening the issue again but I would like to know if there is a more concrete time-frame for delivering this feature now.

I would really really benefit from this, let me explain my use case.

I’d like to do a String equals filter for a certain field and then do a range filter by Timestamp, so I can get every record related to that field. Doing a range filter from my records and then filtering them by name using a UDF is too expensive as there would be too many records to filter, and if I filter first by name then its really cumbersome to sort the records by Timestamp inside a UDF.


#11

It is possible to structure the secondary index such that string equality is the first filter, and the second filter (currently can only be in UDF) will be on the timestamp. It is not required that the first filter be a range filter.

Hope this addresses your issue.


#12

I understand your answer, and I acknowledge it is possible to do so. Maybe I haven’t explained myself well.

As far as I know Secondary Index are made using B-trees, as seen in https://www.aerospike.com/docs/architecture/secondary-index.html . Therefore it is very quick to access them.

I use your approach,I would be filtering the first set of records using the Secondary Index in the query, but the remaining records will be filtered one by one by inside the UDF. However, you have to go through the whole set.

If you are able to use two Secondary Indexes in your filters you wouldn’t have to go through the first subset fully. Am I correct?

Also, I think it is very confusing to provide a method in Java allowing multiple filters to be passed when only one of them works. I started developing a solution using multiple filters only to find out in forums that they don’t work yet. If the method only accepted one filter and then later a new method for multiple filters is added, it would not be so confusing.

Thank you for your reply.

EDIT: In my previous comment I was also asking if you know about the time-frame for this function to be implemented. Is there any approximate date available for this?


#13

If you are able to use two Secondary Indexes in your filters you wouldn’t have to go through the first subset fully. Am I correct?

There are different strategies of supporting multiple filters, each with different cost and complexity. Doing a first level filtering using secondary Index to quickly filter out the majority data is simple and easy to understand, so this is where we started with.

Right now, we do not have a time-frame yet for the next steps.

Also, I think it is very confusing to provide a method in Java allowing multiple filters to be passed when only one of them works. I started developing a solution using multiple filters only to find out in forums that they don’t work yet. If the method only accepted one filter and then later a new method for multiple filters is added, it would not be so confusing.

Fully agree. Will update Java Client documentation to make this more clear. Thanks!


#14

Thank you for your clarification and for appreciating my feedback :smile: