Secondary index query

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.

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\nError: {0}", e.Message);
    }
}

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.

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\nError: {0}", e.Message);
    }
}

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\nError: {0}", e.Message);
    }
}

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();
}