Secondary index

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.

Filter Expressions can also be used 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.

const Aerospike = require('aerospike');
const batchType = Aerospike.batchType;
const op = Aerospike.operations;
const map = Aerospike.maps;
const list = Aerospike.lists;
const exp = Aerospike.exp;
const GeoJSON = Aerospike.GeoJSON;

// Define host configuration
let config = {hosts: '127.0.0.1:3000'};

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

The following command uses the Aerospike Admin (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 Aersopike 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 job
let indexJob = await client.createIntegerIndex({
    ns: 'sandbox', // namespace
    set: 'ufodata', // set name
    bin: 'occurred', // bin name
    index: 'occurred_idx', // index name
});

// Wait for the task to complete
indexJob.wait()
.then(() => {
    console.info('Job complete');
});

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

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 Aersopike Client API can be used to remove a secondary index as well. The following example removes the same index as the example above.

// Remove index
client.indexRemove(
    "sandbox", // namespace
    "occurred_idx", // index name
)
.then(() => {
    console.info('Job complete');
});

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.

;(async () => {
    // Establishes a connection to the server
    let client = await Aerospike.connect(config);

    // Create query
    let query = client.query('sandbox', 'ufodata');

    // Create the index filter
    query.where(Aerospike.filter.range('occurred', 20210101, 20211231));

    // Execute the query
    let recordStream = await query.foreach();

    recordStream.on('error', err => {
        // Handle error
    });
    recordStream.on('data', record => {
        // Do something
        console.info('Key: %o | Record: %o\\n', record.key.key, record.bins);
    });
    recordStream.on('end', () => {
        // 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
let region = GeoJSON({
    '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
let queryPolicy = new Aerospike.QueryPolicy({
    filterExpression: exp.cmpGeo(exp.binGeo('location'), exp.geo(region))
});

;(async () => {
    // Establishes a connection to the server
    let client = await Aerospike.connect(config);

    // Create query
    let query = client.query('sandbox', 'ufodata');

    // Create the index filter
    query.where(Aerospike.filter.range('occurred', 20210101, 20211231));

    // Execute the query
    let recordStream = await query.foreach(queryPolicy);

    recordStream.on('error', err => {
        // Handle error
    });
    recordStream.on('data', record => {
        // Do something
        console.info('Key: %o | Record: %o\\n', record.key.key, record.bins);
    });
    recordStream.on('end', () => {
        // 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
let queryPolicy = new Aerospike.QueryPolicy({
    filterExpression: exp.cmpGeo(exp.binGeo('location'), exp.geo(region))
});

// Array to hold keys
let keys = [];

;(async () => {
    // Establishes a connection to the server
    let client = await Aerospike.connect(config);

    // Create query
    let query = client.query('sandbox', 'ufodata', {nobins: true});

    // Create the index filter
    query.where(Aerospike.filter.range('occurred', 20210101, 20211231));

    // Execute the query
    let recordStream = await query.foreach(queryPolicy);

    recordStream.on('error', err => {
        // Handle error
    });
    recordStream.on('data', record => {
        keys.push(record.key);
    });
    recordStream.on('end', async () => {
        let expr1 = exp.maps.getByKey(exp.binMap("report"), exp.str("city"), exp.type.STR, map.returnType.VALUE);
        let expr2 = exp.maps.getByKey(exp.binMap("report"), exp.str("state"), exp.type.STR, map.returnType.VALUE);

        // Create batch of records
        let batchRecords = [];
        for(i = 0; i < keys.length; i++){
            batchRecords.push({
                type: batchType.BATCH_READ,
                key: keys[i],
                ops: [
                    exp.operations.read('city', expr1, 0),
                    exp.operations.read('state', expr2, 0)
                ]
            });
        }

        // Get 'city' and 'state' from report map for each record
        let batchResult = await client.batchRead(batchRecords);

        // Access the records
        batchResult.forEach(result => {
            console.info(result.record.bins);
        });

        // 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

const Aerospike = require('aerospike');
const batchType = Aerospike.batchType;
const op = Aerospike.operations;
const map = Aerospike.maps;
const list = Aerospike.lists;
const exp = Aerospike.exp;
const GeoJSON = Aerospike.GeoJSON;

// Define host configuration
let config = {hosts: '127.0.0.1:3000'};

// Create geo region
let region = GeoJSON({
    'type': 'Polygon',
    'coordinates': [
        [
            [-109.061279296875, 36.97622678464096],
            [-102.01904296874999, 36.97622678464096],
            [-102.01904296874999, 41.0130657870063],
            [-109.061279296875, 41.0130657870063],
            [-109.061279296875, 36.97622678464096]
        ]
    ]
});

// Create new query policy
let queryPolicy = new Aerospike.QueryPolicy({
    filterExpression: exp.cmpGeo(exp.binGeo('location'), exp.geo(region))
});

;(async () => {
    // Establishes a connection to the server
    let client = await Aerospike.connect(config);

    // Create query
    let query = client.query('sandbox', 'ufodata');

    // Create the index filter
    query.where(Aerospike.filter.range('occurred', 20210101, 20211231));

    // Execute the query
    let recordStream = await query.foreach(queryPolicy);

    recordStream.on('error', err => {
        // Handle error
    });
    recordStream.on('data', record => {
        // Do something
        console.info('Key: %o | Record: %o\\n', record.key.key, record.bins);
    });
    recordStream.on('end', () => {
        // Close the connection to the server
        client.close();
    });
})();