Secondary index query

For the complete documentation index see: llms.txt

All documentation pages available in markdown.

The following example demonstrates using a secondary index to find a document record. It does the following:

Create a document.
Add the document to an Aerospike set.
Create a secondary index.
Query the document using the secondary index.

The example demonstrates two ways of setting up a secondary index. The first approach stores the index target in a dedicated bin at write time. The second approach indexes values inside the document bin with a MAPVALUES index.

Each example creates an index, runs one query, and drops the index again. For managing indexes outside a self-contained example, such as bounding a long index build with a timeout policy or making creation safe to rerun, see Index build timeouts and reruns.

Setup

Import the necessary helpers, create a client connection, and create a key.

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

AerospikeClient client = new("localhost", 3000);

string ns = "test";
string setName = "table1";

Create a JSON document

Prepare the JSON document to be sent to Aerospike.

List<Dictionary<string, object>> transactions = new()
{
    new() { ["txn_id"] = "1111", ["name"] = "Davis", ["item_id"] = "A1234", ["count"] = 1 },
    new() { ["txn_id"] = "2222", ["name"] = "Johnson", ["item_id"] = "B2345", ["count"] = 2 },
    new() { ["txn_id"] = "3333", ["name"] = "Johnson", ["item_id"] = "C3456", ["count"] = 2 },
    new() { ["txn_id"] = "4444", ["name"] = "Lee", ["item_id"] = "D4567", ["count"] = 3 }
};

Write

Write the document to Aerospike.

WritePolicy writePolicy = new()
{
    sendKey = true
};

foreach (Dictionary<string, object> transaction in transactions)
{
    Key key = new(ns, setName, transaction["txn_id"].ToString());

    Bin nameBin = new("name", transaction["name"]);
    Bin transactionBin = new("transaction", transaction);

    try
    {
        client.Put(writePolicy, key, nameBin, transactionBin);

        Record record = client.Get(null, key);
        Console.WriteLine("Create succeeded\nKey: {0}\nRecord: {1}",
            key.userKey, record);
    }
    catch (AerospikeException e)
    {
        Console.WriteLine("Create failed for key {0}\nError: {1}", key.userKey, e.Message);
        throw;
    }
}

Create a secondary index on a dedicated bin

Create a secondary index on the dedicated name bin and execute a query. This approach stores the index target in its own bin during record insertion.

try
{
    IndexTask task = client.CreateIndex(null, ns, setName, "test_name_idx", "name", IndexType.STRING);
    task.Wait();
    Console.WriteLine("Successfully created index test_name_idx on the 'name' bin");
}
catch (AerospikeException e)
{
    Console.WriteLine("Create index failed\nError: {0}", e.Message);
}

Statement stmt = new();
stmt.SetNamespace(ns);
stmt.SetSetName(setName);
stmt.SetBinNames("name", "transaction");
stmt.SetFilter(Filter.Equal("name", "Johnson"));

RecordSet rs = client.Query(null, stmt);

try
{
    while (rs.Next())
    {
        Console.WriteLine("Key: {0} | Record: {1}", rs.Key.userKey, rs.Record);
    }
}
finally
{
    rs.Close();
    client.DropIndex(null, ns, setName, "test_name_idx").Wait();
}

Create a MAPVALUES index on the document bin

Create a secondary index on map values in the transaction bin and execute a query. This approach indexes nested values inside the document bin directly.

Secondary indexes are typed. A STRING MAPVALUES index covers only the string values in each map, so the integer count values in these documents are not indexed and cannot be queried through it. Indexing those requires a separate NUMERIC index on the same bin.

try
{
    IndexTask task = client.CreateIndex(
        null, ns, setName, "test_transaction_idx", "transaction",
        IndexType.STRING, IndexCollectionType.MAPVALUES);
    task.Wait();
    Console.WriteLine("Successfully created index test_transaction_idx on the 'transaction' bin");
}
catch (AerospikeException e)
{
    Console.WriteLine("Create index failed\nError: {0}", e.Message);
}

Statement stmt = new();
stmt.SetNamespace(ns);
stmt.SetSetName(setName);
stmt.SetBinNames("name", "transaction");
stmt.SetFilter(Filter.Contains("transaction", IndexCollectionType.MAPVALUES, "Johnson"));

RecordSet rs = client.Query(null, stmt);

try
{
    while (rs.Next())
    {
        Console.WriteLine("Key: {0} | Record: {1}", rs.Key.userKey, rs.Record);
    }
}
finally
{
    rs.Close();
    client.DropIndex(null, ns, setName, "test_transaction_idx").Wait();
    client.Close();
}

Code block — dedicated bin index

Expand this section for a single code block to run the dedicated bin index example.

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

AerospikeClient client = new("localhost", 3000);

string ns = "test";
string setName = "table1";

List<Dictionary<string, object>> transactions = new()
{
    new() { ["txn_id"] = "1111", ["name"] = "Davis", ["item_id"] = "A1234", ["count"] = 1 },
    new() { ["txn_id"] = "2222", ["name"] = "Johnson", ["item_id"] = "B2345", ["count"] = 2 },
    new() { ["txn_id"] = "3333", ["name"] = "Johnson", ["item_id"] = "C3456", ["count"] = 2 },
    new() { ["txn_id"] = "4444", ["name"] = "Lee", ["item_id"] = "D4567", ["count"] = 3 }
};

WritePolicy writePolicy = new()
{
    sendKey = true
};

foreach (Dictionary<string, object> transaction in transactions)
{
    Key key = new(ns, setName, transaction["txn_id"].ToString());
    Bin nameBin = new("name", transaction["name"]);
    Bin transactionBin = new("transaction", transaction);

    try
    {
        client.Put(writePolicy, key, nameBin, transactionBin);
        Record record = client.Get(null, key);
        Console.WriteLine("Create succeeded\nKey: {0}\nRecord: {1}", key.userKey, record);
    }
    catch (AerospikeException e)
    {
        Console.WriteLine("Create failed for key {0}\nError: {1}", key.userKey, e.Message);
        throw;
    }
}

try
{
    IndexTask task = client.CreateIndex(null, ns, setName, "test_name_idx", "name", IndexType.STRING);
    task.Wait();
    Console.WriteLine("Successfully created index test_name_idx on the 'name' bin");
}
catch (AerospikeException e)
{
    Console.WriteLine("Create index failed\nError: {0}", e.Message);
}

Statement stmt = new();
stmt.SetNamespace(ns);
stmt.SetSetName(setName);
stmt.SetBinNames("name", "transaction");
stmt.SetFilter(Filter.Equal("name", "Johnson"));

RecordSet rs = client.Query(null, stmt);

try
{
    while (rs.Next())
    {
        Console.WriteLine("Key: {0} | Record: {1}", rs.Key.userKey, rs.Record);
    }
}
finally
{
    rs.Close();
    client.DropIndex(null, ns, setName, "test_name_idx").Wait();
    client.Close();
}

Code block — MAPVALUES index on document bin

Expand this section for a single code block to run the MAPVALUES index example.

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

AerospikeClient client = new("localhost", 3000);

string ns = "test";
string setName = "table1";

List<Dictionary<string, object>> transactions = new()
{
    new() { ["txn_id"] = "1111", ["name"] = "Davis", ["item_id"] = "A1234", ["count"] = 1 },
    new() { ["txn_id"] = "2222", ["name"] = "Johnson", ["item_id"] = "B2345", ["count"] = 2 },
    new() { ["txn_id"] = "3333", ["name"] = "Johnson", ["item_id"] = "C3456", ["count"] = 2 },
    new() { ["txn_id"] = "4444", ["name"] = "Lee", ["item_id"] = "D4567", ["count"] = 3 }
};

WritePolicy writePolicy = new()
{
    sendKey = true
};

foreach (Dictionary<string, object> transaction in transactions)
{
    Key key = new(ns, setName, transaction["txn_id"].ToString());
    Bin nameBin = new("name", transaction["name"]);
    Bin transactionBin = new("transaction", transaction);

    try
    {
        client.Put(writePolicy, key, nameBin, transactionBin);
        Record record = client.Get(null, key);
        Console.WriteLine("Create succeeded\nKey: {0}\nRecord: {1}", key.userKey, record);
    }
    catch (AerospikeException e)
    {
        Console.WriteLine("Create failed for key {0}\nError: {1}", key.userKey, e.Message);
        throw;
    }
}

try
{
    IndexTask task = client.CreateIndex(
        null, ns, setName, "test_transaction_idx", "transaction",
        IndexType.STRING, IndexCollectionType.MAPVALUES);
    task.Wait();
    Console.WriteLine("Successfully created index test_transaction_idx on the 'transaction' bin");
}
catch (AerospikeException e)
{
    Console.WriteLine("Create index failed\nError: {0}", e.Message);
}

Statement stmt = new();
stmt.SetNamespace(ns);
stmt.SetSetName(setName);
stmt.SetBinNames("name", "transaction");
stmt.SetFilter(Filter.Contains("transaction", IndexCollectionType.MAPVALUES, "Johnson"));

RecordSet rs = client.Query(null, stmt);

try
{
    while (rs.Next())
    {
        Console.WriteLine("Key: {0} | Record: {1}", rs.Key.userKey, rs.Record);
    }
}
finally
{
    rs.Close();
    client.DropIndex(null, ns, setName, "test_transaction_idx").Wait();
    client.Close();
}