Secondary index queries

Note

Prior to Server 6.0.0, secondary index (SI) queries were called queries. See the Queries feature guide for more information.

Jump to the Code block for a combined complete example.

Basic SI queries can employ the following index filters:

• Equality comparison against string or numeric indexes.
• Range comparison against numeric indexes. Range result sets are inclusive of lower and upper limits.
• Point-In-Region or Region-Contain-Point comparisons against geo indexes.

You can use record filtering with expressions with secondary index queries.

Policies

See Basic Queries for query policy information.

Setup

The following examples will use the setup and record structure below to illustrate secondary index queries in an Aerospike database.

using Aerospike.Client;
using System;
using System.Collections;

// Define host configuration
Host config = new Host("127.0.0.1", 3000);
// Establishes a connection to the server
AerospikeClient client = new AerospikeClient(null, config);

The record structure:

Occurred: Integer
Reported: Integer
Posted: Integer
Report: Map
{
    shape: List,
    summary: String,
    city: String,
    state: String,
    duration: String
}
Location: GeoJSON

Create an index

You should plan and schedule index creation/removal on production systems as described in Secondary Index Capacity Planning. An index consumes RAM for every index entry. Background index creation/removal can take a substantial amount of resources. It is important to carefully plan and schedule index creation/removal on production systems.

The following command uses the Aerospike Admin tool (asadm) to create an integer index on the sandbox namespace, ufodata set, and occurred bin. This is the recommended way to create a secondary index.

asadm -e 'enable; manage sindex create numeric occurred_idx ns sandbox set ufodata bin occurred'

The Aerospike Client API can be used to create a secondary index as well. The following example creates the same index as the example above.

// Create index task
IndexTask task = client.CreateIndex(null, //policy
    "sandbox", // namespace
    "ufodata", // set name
    "occurred_idx", // index name
    "occurred", // bin name
    IndexType.NUMERIC //index type
);

// Wait for the task to complete
task.Wait();

In this example, the IndexType is NUMERIC. Aerospike supports index types NUMERIC,STRING, GEO2DSPHERE, and BLOB as of server 7.0.

Index build timeouts and reruns

Index creation is asynchronous. CreateIndex() returns as soon as the server accepts the request, and IndexTask.Wait() polls until the build finishes. The policy passed to CreateIndex() supplies the socket timeout used for the create command, for each status poll, and as the overall deadline for Wait(). Passing null, as in the example above, falls back to the client’s default write policy, whose 30-second socket timeout is fine for a small set but often too short for a large one.

Two outcomes deserve their own handling:

• Wait() timed out.

  This means the client stopped polling, not that the build failed. The server keeps building the index, so reporting it as “create index failed” is misleading, and retrying the create will only produce the error below.

• The index already exists.

  Rerunning a create script throws AerospikeException with ResultCode.INDEX_ALREADY_EXISTS (server code 200). Catching it is what makes the script idempotent, and it also covers another client creating the same index concurrently.

Policy indexPolicy = new()
{
  // Bounds the create command, each status poll, and the Wait() deadline.
  socketTimeout = 60000
};

try
{
  IndexTask task = client.CreateIndex(
    indexPolicy,
    ns,
    SetName,
    IndexName,
    BinName,
    IndexType.NUMERIC);

  task.Wait();
  Console.WriteLine($"Created index {IndexName}");
}
catch (AerospikeException.Timeout ae)
{
  // Wait() stopped polling. The server keeps building the index.
  Console.Error.WriteLine($"Still waiting on index {IndexName}: {ae.Message}");
  throw;
}
catch (AerospikeException ae) when (ae.Result == ResultCode.INDEX_ALREADY_EXISTS)
{
  Console.WriteLine($"Index {IndexName} already exists");
}

Remove an index

The following command uses the Aerospike Admin (asadm) to remove the index created above.

asadm -e 'enable; manage sindex delete occurred_idx ns sandbox set ufodata'

The Aerospike Client API can be used to remove a secondary index as well. The following example removes the same index as the example above.

// Create index task
IndexTask task = client.DropIndex(null, //policy
    "sandbox", // namespace
    "ufodata", // set name
    "occurred_idx", // index name
);

// Wait for the task to complete
task.Wait();

Dropping an index that is not there throws AerospikeException with ResultCode.INDEX_NOTFOUND, so teardown code benefits from the same treatment as creation.

try
{
  client.DropIndex(indexPolicy, ns, SetName, IndexName).Wait();
  Console.WriteLine($"Dropped index {IndexName}");
}
catch (AerospikeException ae) when (ae.Result == ResultCode.INDEX_NOTFOUND)
{
  Console.WriteLine($"Index {IndexName} does not exist");
}

Query an index

The following example queries the sandbox namespace and ufodata set name, with an inclusive range filter on the occurred bin, returning records with a bin value between 20210101 and 20211231.

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.SetNamespace("sandbox");
stmt.SetSetName("ufodata");

// Create index filter
stmt.SetFilter(Filter.Range("occurred", 20210101, 20211231));

// Execute the query
RecordSet recordSet = client.Query(null, stmt);

// Get the results
try
{
    while(recordSet.Next())
    {
        Key key = recordSet.Key;
        Record record = recordSet.Record;
        // Do something
        Console.WriteLine("Key: {0} | Record: {1}\\n", key.userKey, record.ToString().Split("bins:")[1]);
    }
}
finally
{
    recordSet.Close();
}

// Close the connection to the server
client.Close();

Query an index with a Filter Expression

The following example will use the geo string defined in the expandable section below.

View the language specific data creation

// Create geo region
string region = "{ " +
"   \\"type\\": \\"Polygon\\", " +
"   \\"coordinates\\": [ " +
"     [ " +
"           [-109.061279296875, 36.97622678464096], " +
"           [-102.01904296874999, 36.97622678464096], " +
"           [-102.01904296874999, 41.0130657870063], " +
"           [-109.061279296875, 41.0130657870063], " +
"           [-109.061279296875, 36.97622678464096] " +
"       ] " +
"   ] " +
"}";

This example queries the same namespace and set name, while using the same index filter as the example above, but adds a Filter Expression to the query policy to only return records with a location bin value within the geo region specified above.

// Create new query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.Build(
    Exp.GeoCompare(Exp.GeoBin("location"), Exp.Geo(region))
);

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.SetNamespace("sandbox");
stmt.SetSetName("ufodata");

// Create index filter
stmt.SetFilter(Filter.Range("occurred", 20210101, 20211231));

// Execute the query
RecordSet recordSet = client.Query(queryPolicy, stmt);

// Get the results
try
{
    while(recordSet.Next())
    {
        Key key = recordSet.Key;
        Record record = recordSet.Record;
        // Do something
        Console.WriteLine("Key: {0} | Record: {1}\\n", key.userKey, record.ToString().Split("bins:")[1]);
    }
}
finally
{
    recordSet.Close();
}

// Close the connection to the server
client.Close();

Query an index then create a batch operation on the returned keys

Currently, Transaction and CDT operations are not available for basic queries. The following example shows how a basic query and a batch operation can be combined to use transaction operations on the results.

This example queries the same namespace and set name, while using the same index filter, geo region, and Filter Expression as above, but only returns record metadata, which is then used to create a batch operation to return only the city and state from the report map.

// Create new query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.Build(
    // region defined in expandable section above
    Exp.GeoCompare(Exp.GeoBin("location"), Exp.Geo(region))
);
// do not include record bins
queryPolicy.includeBinData = false;

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.SetNamespace("sandbox");
stmt.SetSetName("ufodata");

// Create index filter
stmt.SetFilter(Filter.Range("occurred", 20210101, 20211231));

// Create keys list
List<Key> keysList = new List<Key>();

// Execute the query
RecordSet recordSet = client.Query(queryPolicy, stmt);

// Get the results
try
{
    while(recordSet.Next())
    {
        keysList.Add(recordSet.Key);
    }
}
finally
{
    recordSet.Close();
}

// Convert list to array
Key[] keys = keysList.ToArray();

// Create map key list
string[] mapKeys = {"city", "state"};

// Get 'city' and 'state' from report map for each record
BatchResults batchResult = client.Operate(null, null, keys,
    MapOperation.GetByKeyList("report", mapKeys, MapReturnType.VALUE)
);

// Access the records
foreach (BatchRecord batchRecord in batchResult.records)
{
    Key key = batchRecord.key;
    Record record = batchRecord.record;
    if(record != null)
    {
        // Do something
        Console.WriteLine("Key: {0} | Record: {1}\\n", key.userKey, record.ToString().Split("bins:")[1]);
    }
}

// Close the connection to the server
client.Close();

Pagination

See Pagination for more information.

Code block

Expand this section for a single code block to execute a basic SI query

using Aerospike.Client;
using System;
using System.Collections;

// Define host configuration
Host config = new Host("127.0.0.1", 3000);
// Establishes a connection to the server
AerospikeClient client = new AerospikeClient(null, config);

string region = "{ " +
"   \\"type\\": \\"Polygon\\", " +
"   \\"coordinates\\": [ " +
"     [ " +
"           [-109.061279296875, 36.97622678464096], " +
"           [-102.01904296874999, 36.97622678464096], " +
"           [-102.01904296874999, 41.0130657870063], " +
"           [-109.061279296875, 41.0130657870063], " +
"           [-109.061279296875, 36.97622678464096] " +
"       ] " +
"   ] " +
"}";

QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.Build(
    Exp.GeoCompare(Exp.GeoBin("location"), Exp.Geo(region))
);

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.SetNamespace("sandbox");
stmt.SetSetName("ufodata");

// Create index filter
stmt.SetFilter(Filter.Range("occurred", 20210101, 20211231));

// Execute the query
RecordSet recordSet = client.Query(queryPolicy, stmt);

// Get the results
try
{
    while(recordSet.Next())
    {
        Key key = recordSet.Key;
        Record record = recordSet.Record;
        // Do something
        Console.WriteLine("Key: {0} | Record: {1}\\n", key.userKey, record.ToString().Split("bins:")[1]);
    }
}
finally
{
    recordSet.Close();
}

// Close the connection to the server
client.Close();