Multiple rows delete: how to write a delete query



Hi all,

I am new in aerospike . I have create a namespace say “Company” with in that i have set “Products”

and bins are (data,k, product_id, product_type)

I want to write the delete query

a) delete all product of particular type. b) delete all product of particular id

How should i write these query .

i am using php client . thanks,


You would use a record UDF, written in Lua, to inspect whether the criteria are met for deletion, and if they are you’d call the aerospike.remove() function.

Currently you have to use scanApply() to attach the record UDF to a scan of the set Company.Products. Be aware that we are working on secondary index record UDFs, meaning there will soon be a queryApply() that will let you run a record UDF against the records matched by a secondary index query. This should fit your use case better, as the query itself can be targeted at an index over product type or ID, rather than scanning all the records in the set.


Do you have an example for this topic ?


I answered a similar question on stackoverflow.


Thank you for your answer. After query, I have a RecordSet about 2M records, now I want to remove all these records . Can you tell me how I can do this with best performance ?

This is my code to do this but it’s very slow (200s to remove 2M record).

    AerospikeClient mAsClient = new AerospikeClient(Const.AEROSPIKE_HOST, Const.AEROSPIKE_PORT);
    mAsClient.register(null, "udf/aggregate.lua", "aggregate.lua", Language.LUA);
    Statement stmt1 = new Statement();

    RecordSet resultSet = mAsClient.query(null, stmt1);
    while ( {
        String id = resultSet.getRecord().getString("id");
        Statement stmt = new Statement();
        stmt.setFilters(Filter.equal("id", id));
        ExecuteTask task = mAsClient.execute(null, stmt, "aggregate", "deleteRecord");
        System.out.println("Status = " + task.queryStatus());

and my lua :

function deleteRecord(r)


As I pointed out, you do not want to get a RecordSet, you want to apply a record UDF to each that matches your criteria. The UDF has a single line removing the record it’s being applied to.

Here’s a similar example of modifying the TTL of all records that match a specific criteria. You would change the Lua code from trying to modify the TTL and update the record to an aerospike:remove(rec) call.


Thank you @zbotzer , I will try it.


I tried your example in my issue and it’s working perfect. Thank you so much .


Hello zbotzer, I removed records from aerospike by update TTL = 1 to record. But the records still on Ram and after few seconds( ~ 10s ) , the records have gone from Ram. I want after update TTL then record has gone immediate so how I can do that ?


If you set the TTL to 1s, it will expire one second after being written. The record cannot be accessed by a get once it is expired, but it still needs the nsup thread to clean it up from the primary index. It’ll consume the 64B of DRAM in the primary index until it’s removed by the namespace supervisor.

Instead of changing the TTL to 1 second in order to remove a record, your code should call aerospike:remove(rec). See the aerospike module of the Lua UDF API. That will immediately remove the record’s metadata from the primary index.

What you were doing before (rather than updating the TTL) should work.


Thanks rbotzer , After changed aerospike:update() to aerospike:remove() , it’s working perfect as I want .

Thanks so much.


hi ronen, is the queryapply on secondary index already in the release?


Long time ago

Check out the latest PHP client, and generate the PHPDoc for yourself.


Hi, don’t know if it is the case to open a new thread?

I need to know the number of record deleted (removed) from the Set. I’m using this registered UDF:

function delete(r)
    aerospike: remove(r)

In C# I call it using:

public void DeleteItems(IEnumerable<string> itemIds)
    foreach (var itemId in itemIds)
        var statement = new Statement
            Namespace = configuration.Namespace,
            SetName = configuration.ItemsSetName,
            Filter = Filter.Equal("Id", itemId)

        Client.Execute(null, statement, removeRecordFileName, removeRecordFunctionName);

My initial idea was to count the record before and after but seems like a SQL “select count(*)” is not so simple to do in Aerospike.

Any suggestion?


You can just do a statement with includebindata=false and then invoke the execution.

var statement = new Statement
        Namespace = configuration.Namespace,
        SetName = configuration.ItemsSetName,
        Filter = Filter.Equal("Id", itemId)
    QueryPolicy qp = new QueryPolicy();
    rs=Client.query(qp, statement,);

or something similar.