Skip to content
Visit booth 3171 at Google Cloud Next to see how to unlock real-time decisions at scaleMore info

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 method extracts the secondary index target during insertion of the document, then uses it for the query. The second method uses the document bin directly for the query.

Setup

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

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import com.aerospike.client.AerospikeClient;
import com.aerospike.client.AerospikeException;
import com.aerospike.client.Bin;
import com.aerospike.client.Key;
import com.aerospike.client.Record;
import com.aerospike.client.Value;
import com.aerospike.client.cdt.CTX;
import com.aerospike.client.cdt.MapReturnType;
import com.aerospike.client.exp.Exp;
import com.aerospike.client.exp.MapExp;
import com.aerospike.client.policy.QueryPolicy;
import com.aerospike.client.policy.RecordExistsAction;
import com.aerospike.client.policy.WritePolicy;
import com.aerospike.client.query.Filter;
import com.aerospike.client.query.IndexType;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.Statement;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;


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


// Aerospike namespace, set, and key_id to be used for the Aerospike key
String namespace = "sandbox";
String set = "ufo";
String secondaryIndex = "city_index";
String sightingsBinName = "sightings";
String cityBinName = "city";

Create a JSON document

Prepare the JSON document to be sent to Aerospike.

// Example JSON string
String sightings = "[{\"sighting\":{\"occurred\":20200912,\"reported\":20200916,\"posted\":20201105,\"report\":{\"city\":\"Kirkland\",\"duration\":\"~30 minutes\",\"shape\":[\"circle\"],\"state\":\"WA\",\"summary\":\"4 rotating orange lights in the Kingsgate area above the Safeway. Around 9pm the power went out in the Kingsgate area.  Four lights were spotted rotating above the local Safeway and surrounding streets.  They were rotating fast but staying relatively in the same spots.  Also described as orange lights. About thirty minutes later they disappeared.  The second they disappeared the power was restored.  Later a station of police from Woodinville and Kirkland came to guard the street where it happened.  They wouldn't let anyone go past the street, putting out search lights and flare signals so people couldn't drive past Safeway.  The police also would not let people walk past to go home.\"},\"location\":\"\\\"{\\\"type\\\":\\\"Point\\\",\\\"coordinates\\\":[-122.1966441,47.69328259]}\\\"\"}},\n" +
                "{\"sighting\":{\"occurred\":20200322,\"reported\":20200322,\"posted\":20200515,\"report\":{\"city\":\"Pismo Beach\",\"duration\":\"5 minutes\",\"shape\":[\"light\"],\"state\":\"CA\",\"summary\":\"About 20 solid, bright lights moving at the same altitude, heading and speed.  Spaced perfectly apart flying over the ocean headed south.\"},\"location\":\"\\\"{\\\"type\\\":\\\"Point\\\",\\\"coordinates\\\":[-120.6595,35.1546]}\\\"\"}},\n" +
                "{\"sighting\":{\"occurred\":20200530,\"reported\":20200531,\"posted\":20200625,\"report\":{\"city\":\"New York Staten Island\",\"duration\":\"2 minutes\",\"shape\":[\"disk\"],\"state\":\"NY\",\"summary\":\"Round shaped object observed over Staten Island NYC, while sitting in my back yard. My daughter also observed this object . Bright White shaped object moving fast from East to West . Observed over Graniteville, Staten Island towards the Elizabeth NJ area and appears to be fast. We then lost view of it due to the clouds.\"}}}]";


// Convert string to Java Map
List<Map<String, Object>> sightingMap = new Gson().fromJson(sightings, new TypeToken<ArrayList<HashMap<String, Object>>>(){}.getType());

Write

Write the document to Aerospike.

// Create write policy
WritePolicy writePolicy = new WritePolicy();
writePolicy.recordExistsAction = RecordExistsAction.UPDATE;


for (int i = 0; i < sightingMap.size(); i++) {
    // Define Aerospike key
    Key key = new Key(namespace, set, 5000 + i);


    // Extracting value from record for secondary index column
    Map<String, Object> entry =  sightingMap.get(i);
    Map<String, Map<String, Object>> value = (Map<String, Map<String, Object>>) entry.get("sighting");


    // Set bins for "sightings" and "city"
    Bin sightingsBin = new Bin(sightingsBinName, Value.get(entry));
    Bin cityBin = new Bin(cityBinName, Value.get( value.get("report").get("city")));


    // Writing record to Aerospike
    try {
        client.put(writePolicy, key, cityBin, sightingsBin);
    } // Write failed
    catch (AerospikeException ae) {
        System.out.println("Read failed\\nError: " + ae.getMessage());
    }
}

Create a secondary index - method 1

Create a secondary index and execute a query. This indexing method extracts the secondary index target during insertion of the document, then uses it for the query.

// Creating secondary index
try {
    client.createIndex(writePolicy, namespace, set, secondaryIndex, cityBinName, IndexType.STRING);
} // Create index failed
catch (AerospikeException ae) {
    System.out.println("Create index failed\\nError: " + ae.getMessage());
}


// Fetching records from Aerospike using seconday index "city" as filter


// Creating query statement
// The statement is the equivalent of SQL query `select city, sighting from sandbox.ufo where city = "Kirkland"`;
Statement statement = new Statement();
statement.setNamespace(namespace);
statement.setSetName(set);
statement.setBinNames(cityBinName, sightingsBinName);
statement.setFilter(Filter.equal("city", "Kirkland"));


// Submitting the query
RecordSet recordSet =  client.query(new QueryPolicy(), statement);


// Iterating over read records
while (recordSet.next()) {
    Record record = recordSet.getRecord();
    System.out.printf("Record read using seconday index `%s` with key: %s and value: %s\n", secondaryIndex,
            record.getValue("city"), new Gson().toJson(record.getValue(sightingsBinName)));
}
// Cleanup
client.dropIndex(writePolicy, namespace, set, secondaryIndex);
client.close();

Create a secondary index - method 2

Create a secondary index and execute a query. This indexing method uses the document bin directly for the query.

// Creating secondary index
try {
    client.createIndex(writePolicy, namespace, set, secondaryIndex, sightingsBinName, IndexType.STRING);
} // Create index failed
catch (AerospikeException ae) {
    System.out.println("Create index failed\\nError: " + ae.getMessage());
}
// Fetching records from Aerospike using seconday index "city" as filter
// Create filter expression
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp =  Exp.build(
    Exp.eq(
        MapExp.getByKey(MapReturnType.VALUE, Exp.Type.STRING, Exp.val("city"), Exp.mapBin(sightingsBinName), CTX.mapKey(Value.get("sighting")), CTX.mapKey(Value.get("report"))),
        Exp.val("Kirkland")
    )
);


// Creating query statement
Statement statement = new Statement();
statement.setNamespace(namespace);
statement.setSetName(set);


// Submitting the query
RecordSet recordSet =  client.query(queryPolicy, statement);


// Iterating over read records
try {
    while (recordSet.next()) {
        Record record = recordSet.getRecord();
        System.out.printf("Record read using seconday index `%s` with key: %s and value: %s\n", secondaryIndex,
                record.getValue("city"), new Gson().toJson(record.getValue(sightingsBinName)));
    }
} catch(AerospikeException ae) {
    System.out.println("Failed to fetch records \\nError: " + ae.getMessage());
}
finally {
    // Cleanup
    client.dropIndex(writePolicy, namespace, set, secondaryIndex);
    client.close();
}

Code block - method 1

Expand this section for a single code block to run the example with indexing method 1.
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import com.aerospike.client.AerospikeClient;
import com.aerospike.client.AerospikeException;
import com.aerospike.client.Bin;
import com.aerospike.client.Key;
import com.aerospike.client.Record;
import com.aerospike.client.Value;
import com.aerospike.client.cdt.CTX;
import com.aerospike.client.cdt.MapReturnType;
import com.aerospike.client.exp.Exp;
import com.aerospike.client.exp.MapExp;
import com.aerospike.client.policy.QueryPolicy;
import com.aerospike.client.policy.RecordExistsAction;
import com.aerospike.client.policy.WritePolicy;
import com.aerospike.client.query.Filter;
import com.aerospike.client.query.IndexType;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.Statement;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;


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


// Aerospike namespace, set, and key_id to be used for the Aerospike key
String namespace = "sandbox";
String set = "ufo";
String secondaryIndex = "city_index";
String sightingsBinName = "sightings";
String cityBinName = "city";


// Example JSON string
String sightings = "[{\"sighting\":{\"occurred\":20200912,\"reported\":20200916,\"posted\":20201105,\"report\":{\"city\":\"Kirkland\",\"duration\":\"~30 minutes\",\"shape\":[\"circle\"],\"state\":\"WA\",\"summary\":\"4 rotating orange lights in the Kingsgate area above the Safeway. Around 9pm the power went out in the Kingsgate area.  Four lights were spotted rotating above the local Safeway and surrounding streets.  They were rotating fast but staying relatively in the same spots.  Also described as orange lights. About thirty minutes later they disappeared.  The second they disappeared the power was restored.  Later a station of police from Woodinville and Kirkland came to guard the street where it happened.  They wouldn't let anyone go past the street, putting out search lights and flare signals so people couldn't drive past Safeway.  The police also would not let people walk past to go home.\"},\"location\":\"\\\"{\\\"type\\\":\\\"Point\\\",\\\"coordinates\\\":[-122.1966441,47.69328259]}\\\"\"}},\n" +
                "{\"sighting\":{\"occurred\":20200322,\"reported\":20200322,\"posted\":20200515,\"report\":{\"city\":\"Pismo Beach\",\"duration\":\"5 minutes\",\"shape\":[\"light\"],\"state\":\"CA\",\"summary\":\"About 20 solid, bright lights moving at the same altitude, heading and speed.  Spaced perfectly apart flying over the ocean headed south.\"},\"location\":\"\\\"{\\\"type\\\":\\\"Point\\\",\\\"coordinates\\\":[-120.6595,35.1546]}\\\"\"}},\n" +
                "{\"sighting\":{\"occurred\":20200530,\"reported\":20200531,\"posted\":20200625,\"report\":{\"city\":\"New York Staten Island\",\"duration\":\"2 minutes\",\"shape\":[\"disk\"],\"state\":\"NY\",\"summary\":\"Round shaped object observed over Staten Island NYC, while sitting in my back yard. My daughter also observed this object . Bright White shaped object moving fast from East to West . Observed over Graniteville, Staten Island towards the Elizabeth NJ area and appears to be fast. We then lost view of it due to the clouds.\"}}}]";


// Convert string to Java Map
List<Map<String, Object>> sightingMap = new Gson().fromJson(sightings, new TypeToken<ArrayList<HashMap<String, Object>>>(){}.getType());


// Create write policy
WritePolicy writePolicy = new WritePolicy();
writePolicy.recordExistsAction = RecordExistsAction.UPDATE;


for (int i = 0; i < sightingMap.size(); i++) {
    // Define Aerospike key
    Key key = new Key(namespace, set, 5000 + i);


    // Extracting value from record for secondary index column
    Map<String, Object> entry =  sightingMap.get(i);
    Map<String, Map<String, Object>> value = (Map<String, Map<String, Object>>) entry.get("sighting");


    // Set bins for "sightings" and "city"
    Bin sightingsBin = new Bin(sightingsBinName, Value.get(entry));
    Bin cityBin = new Bin(cityBinName, Value.get( value.get("report").get("city")));


    // Writing record to Aerospike
    try {
        client.put(writePolicy, key, cityBin, sightingsBin);
    } // Write failed
    catch (AerospikeException ae) {
        System.out.println("Read failed\\nError: " + ae.getMessage());
    }
}


// Creating secondary index
try {
    client.createIndex(writePolicy, namespace, set, secondaryIndex, cityBinName, IndexType.STRING);
} // Create index failed
catch (AerospikeException ae) {
    System.out.println("Create index failed\\nError: " + ae.getMessage());
}


// Fetching records from Aerospike using seconday index "city" as filter


// Creating query statement
// The statement is the equivalent of SQL query `select city, sighting from sandbox.ufo where city = "Kirkland"`;
Statement statement = new Statement();
statement.setNamespace(namespace);
statement.setSetName(set);
statement.setBinNames(cityBinName, sightingsBinName);
statement.setFilter(Filter.equal("city", "Kirkland"));


// Submitting the query
RecordSet recordSet =  client.query(new QueryPolicy(), statement);


// Iterating over read records
while (recordSet.next()) {
    Record record = recordSet.getRecord();
    System.out.printf("Record read using seconday index `%s` with key: %s and value: %s\n", secondaryIndex,
            record.getValue("city"), new Gson().toJson(record.getValue(sightingsBinName)));
}
// Cleanup
client.dropIndex(writePolicy, namespace, set, secondaryIndex);
client.close();

Code block - method 2

Expand this section for a single code block to run the example with indexing method 2.
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import com.aerospike.client.AerospikeClient;
import com.aerospike.client.AerospikeException;
import com.aerospike.client.Bin;
import com.aerospike.client.Key;
import com.aerospike.client.Record;
import com.aerospike.client.Value;
import com.aerospike.client.cdt.CTX;
import com.aerospike.client.cdt.MapReturnType;
import com.aerospike.client.exp.Exp;
import com.aerospike.client.exp.MapExp;
import com.aerospike.client.policy.QueryPolicy;
import com.aerospike.client.policy.RecordExistsAction;
import com.aerospike.client.policy.WritePolicy;
import com.aerospike.client.query.Filter;
import com.aerospike.client.query.IndexType;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.Statement;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;


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


// Aerospike namespace, set, and key_id to be used for the Aerospike key
String namespace = "sandbox";
String set = "ufo";
String secondaryIndex = "sighting_index";
String sightingsBinName = "sightings";
String cityBinName = "city";


// Example JSON string
String sightings = "[{\"sighting\":{\"occurred\":20200912,\"reported\":20200916,\"posted\":20201105,\"report\":{\"city\":\"Kirkland\",\"duration\":\"~30 minutes\",\"shape\":[\"circle\"],\"state\":\"WA\",\"summary\":\"4 rotating orange lights in the Kingsgate area above the Safeway. Around 9pm the power went out in the Kingsgate area.  Four lights were spotted rotating above the local Safeway and surrounding streets.  They were rotating fast but staying relatively in the same spots.  Also described as orange lights. About thirty minutes later they disappeared.  The second they disappeared the power was restored.  Later a station of police from Woodinville and Kirkland came to guard the street where it happened.  They wouldn't let anyone go past the street, putting out search lights and flare signals so people couldn't drive past Safeway.  The police also would not let people walk past to go home.\"},\"location\":\"\\\"{\\\"type\\\":\\\"Point\\\",\\\"coordinates\\\":[-122.1966441,47.69328259]}\\\"\"}},\n" +
                "{\"sighting\":{\"occurred\":20200322,\"reported\":20200322,\"posted\":20200515,\"report\":{\"city\":\"Pismo Beach\",\"duration\":\"5 minutes\",\"shape\":[\"light\"],\"state\":\"CA\",\"summary\":\"About 20 solid, bright lights moving at the same altitude, heading and speed.  Spaced perfectly apart flying over the ocean headed south.\"},\"location\":\"\\\"{\\\"type\\\":\\\"Point\\\",\\\"coordinates\\\":[-120.6595,35.1546]}\\\"\"}},\n" +
                "{\"sighting\":{\"occurred\":20200530,\"reported\":20200531,\"posted\":20200625,\"report\":{\"city\":\"New York Staten Island\",\"duration\":\"2 minutes\",\"shape\":[\"disk\"],\"state\":\"NY\",\"summary\":\"Round shaped object observed over Staten Island NYC, while sitting in my back yard. My daughter also observed this object . Bright White shaped object moving fast from East to West . Observed over Graniteville, Staten Island towards the Elizabeth NJ area and appears to be fast. We then lost view of it due to the clouds.\"}}}]";


// Convert string to Java Map
List<Map<String, Object>> sightingMap = new Gson().fromJson(sightings,
new TypeToken<ArrayList<HashMap<String, Object>>>(){}.getType());


// Create the write policy
WritePolicy writePolicy = new WritePolicy();
writePolicy.recordExistsAction = RecordExistsAction.UPDATE;


for (int i = 0; i < sightingMap.size(); i++) {
    // Define Aerospike key
    Key key = new Key(namespace, set, 5000 + i);


    // Extracting value from record for secondary index secondary index column
    Map<String, Object> entry =  sightingMap.get(i);
    Map<String, Map<String, Object>> value = (Map<String, Map<String, Object>>) entry.get("sighting");


    // Set bins for "sightings" and "city"
    Bin sightingsBin = new Bin(sightingsBinName, Value.get(entry));
    Bin cityBin = new Bin(cityBinName, Value.get( value.get("report").get("city")));


    // Writing record to Aerospike
    try {
        client.put(writePolicy, key, cityBin, sightingsBin);
    } // Write failed
    catch (AerospikeException ae) {
        System.out.println("Read failed\\nError: " + ae.getMessage());
    }
}


// Creating secondary index
try {
    client.createIndex(writePolicy, namespace, set, secondaryIndex, sightingsBinName, IndexType.STRING);
} // Create index failed
catch (AerospikeException ae) {
    System.out.println("Create index failed\\nError: " + ae.getMessage());
}
// Fetching records from Aerospike using seconday index "city" as filter
// Create filter expression
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp =  Exp.build(
    Exp.eq(
        MapExp.getByKey(MapReturnType.VALUE, Exp.Type.STRING, Exp.val("city"), Exp.mapBin(sightingsBinName), CTX.mapKey(Value.get("sighting")), CTX.mapKey(Value.get("report"))),
        Exp.val("Kirkland")
    )
);


// Creating query statement
Statement statement = new Statement();
statement.setNamespace(namespace);
statement.setSetName(set);


// Submitting the query
RecordSet recordSet =  client.query(queryPolicy, statement);


// Iterating over read records
try {
    while (recordSet.next()) {
        Record record = recordSet.getRecord();
        System.out.printf("Record read using seconday index `%s` with key: %s and value: %s\n", secondaryIndex,
                record.getValue("city"), new Gson().toJson(record.getValue(sightingsBinName)));
    }
} catch(AerospikeException ae) {
    System.out.println("Failed to fetch records \\nError: " + ae.getMessage());
}
finally {
    // Cleanup
    client.dropIndex(writePolicy, namespace, set, secondaryIndex);
    client.close();
}
Feedback

Was this page helpful?

What type of feedback are you giving?

What would you like us to know?

+Capture screenshot

Can we reach out to you?