Fetch all keys based on some pattern

Feature Discussion Query & Indexing
,
1

i have thousands of records on namepace:set.

keys are of pattern: lastname/firstname/state/zip example: brown/chris/fl/32514

i want to get all keys based on the pattern “*/chris/*/32514” and perform delete operation on the resulting keys. i can do a scanAll but that is very expensive operation. i have seen many posts but couldn’t find a solution. please suggest on how can i achieve this.

2

Hey @unverifieduser, welcome to the forum!

I’m assuming you’re talking about the records key, but you could follow the same setup for a map key as well, with a little extra setup.

To achieve this you would need to do a full scan, or secondary index query, but you could utilize a filter expression, RegexCompare to be exact, to compare the key with the pattern. One caveat to this, the records would need to have been written with the sendKey write policy set to true.

Here is a code sample, in Go, that walks through writing a record and then using a query with an expression filter to return based on a pattern to match the key. You could change things up to utilize a background query for the deletion.

You can drop this code directly into the sandbox on our Developer Hub as well. Just select Go as the language and hit Connect, the paste and run this on the blank code tab. Hope that helps!

// Import Aerospike client library
import (
    "log"
    as "github.com/aerospike/aerospike-client-go/v6" 
)

func writeData() {
    client, errClient := as.NewClient("127.0.0.1", 3000)
    if errClient != nil {
        log.Printf("Error: %s", errClient)
    }
    
    // Close the connection to the server
    defer client.Close()
    
    // Initialize a write policy
    policy := as.NewWritePolicy(0,0)
    policy.SendKey = true

    // Create a key in namespace "sandbox" and set "ufodata"
    key, errKey := as.NewKey("sandbox", "ufodata", "myKeyString")
    if errKey != nil {
        log.Printf("Error: %s", errKey)
        return
    }

    // Create the bins as Bin("binName", value)
    myBin := as.NewBin("myBin", "we did it!")

    // Write the record to Aerospike
    errWrite := client.PutBins(policy, key, myBin)
    if errWrite != nil {
        log.Printf("Error: %s", errWrite)
        return
    }
    
    log.Println("One record added")
}

func getData(){
    
    client, errClient := as.NewClient("127.0.0.1", 3000)
    if errClient != nil {
        log.Printf("Error: %s", errClient)
        return
    }
    
    // Close the connection to the server
    defer client.Close()
    
    // Create query policy
    queryPolicy := as.NewQueryPolicy()
    queryPolicy.FilterExpression = as.ExpRegexCompare(
        ".*Str.*",
        as.ExpRegexFlagICASE,
        as.ExpKey(as.ExpTypeSTRING))

    // Create statement
    stmt := as.NewStatement("sandbox", "ufodata")

    // Execute the query
    recordSet, err := client.Query(queryPolicy, stmt)
    if err != nil {
        log.Printf("Error: %s", err)
        return
    }

    // Get the results
    for records := range recordSet.Results() {
        if records != nil { 
            // Do something
            log.Printf("Record: %v \n", records.Record.Bins)
        }
    }

    recordSet.Close()
}

func main() {
    writeData()
    getData()
}
3

thanks for the solution but, i was looking mostly to get only the keys and not records. is that possible?

if getting only keys is not possible, can i

  1. create & use secondary index for filtering without any performance issues? my app does a lot of saving .
  2. store my key in a seperate bin and do a delete operation with matching keys?
4

You could update the query policy with:

queryPolicy.IncludeBinData = false

to the example above to only return metadata.

The only way to create a secondary index on the key would be to store it in a bin within the record. You could also explore creating a set index, if you are storing your data in a set, to possibly make querying the set more efficient.

If you are only looking to delete records with a key that matches a defined pattern, then as I said in my other response, you could use the query and filter expression as above, but execute as a background query that deletes the matching records. This returns only a task to track the query status, and would run asynchronously on the server. You can see code samples for background queries on the Developer Hub.