---
title: "Secondary index"
description: "Guide to creating, removing, and querying Aerospike secondary indexes using the Java client and filter expressions."
---

# Secondary index

> For the complete documentation index see: [llms.txt](https://aerospike.com/docs/llms.txt)
> 
> All documentation pages available in markdown.

::: note
Prior to Server 6.0.0, secondary index (SI) queries were called _queries_. See [Queries](https://aerospike.com/docs/develop/learn/queries/) for more information.
:::

Jump to the [Code block](#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](https://aerospike.com/docs/develop/data-types/geospatial#geospatial-index) comparisons against geo indexes.

You can also use [record filtering expressions](https://aerospike.com/docs/develop/expressions/#record-filtering-with-expressions) with secondary index queries.

## Policies

See [Basic Queries](https://aerospike.com/docs/develop/client/java/usage/multi/queries/basic#policies) 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.

```java
import com.aerospike.client.AerospikeClient;

import com.aerospike.client.Key;

import com.aerospike.client.Record;

import com.aerospike.client.BatchRecord;

import com.aerospike.client.BatchResults;

import com.aerospike.client.Value;

import com.aerospike.client.cdt.MapOperation;

import com.aerospike.client.cdt.MapPolicy;

import com.aerospike.client.cdt.MapReturnType;

import com.aerospike.client.exp.Exp;

import com.aerospike.client.policy.QueryPolicy;

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.aerospike.client.task.IndexTask;

import java.util.Arrays;

import java.util.List;

// Establishes a connection to the server

AerospikeClient client = new AerospikeClient("127.0.0.1", 3000);
```

The record structure:

```asciidoc
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](https://aerospike.com/docs/database/manage/planning/capacity/secondary-indexes). 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 (asadm)](https://aerospike.com/docs/database/tools/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.

```plaintext
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.

```java
// 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.waitTillComplete();
```

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)](https://aerospike.com/docs/database/tools/asadm) to remove the index created above.

```plaintext
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.

```java
// 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.waitTillComplete();
```

## 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`.

```java
// 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.getKey();

        Record record = recordSet.getRecord();

        // Do something

        System.out.format("Key: %s | Record: %s\\n", key.userKey, record.bins);

    }

}

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

```java
// 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.

```java
// 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))

);

// 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.getKey();

        Record record = recordSet.getRecord();

        // Do something

        System.out.format("Key: %s | Record: %s\\n", key.userKey, record.bins);

    }

}

finally{

    recordSet.close();

}

// Close the connection to the server

client.close();
```

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

The following example shows how a basic query and a batch operation can be combined when projection ops are not used. It queries the same namespace and set name, while using the same index filter, geo region, and Filter Expression as the earlier examples, 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.

```java
// 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 ArrayList<Key>();

// Execute the query

RecordSet recordSet = client.query(queryPolicy, stmt);

// Get the results

try{

    while(recordSet.next()){

        keysList.add(recordSet.getKey());

    }

}

finally{

    recordSet.close();

}

// Convert list to array

Key[] keys = keysList.toArray(new Key[0]);

// Create map key list

List<Value> mapKeys = Arrays.asList(Value.get("city"), Value.get("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

for (BatchRecord batchRecord : batchResult.records){

    Record record = batchRecord.record;

    Key key = batchRecord.key;

    if(record != null){

        // Do something

        System.out.format("Key: %s | Record: %s\\n", key.userKey, record.bins);

    }

}

// Close the connection to the server

client.close();
```

## Pagination

See [Pagination](https://aerospike.com/docs/develop/client/java/usage/multi/queries/primary#pagination) for more information.

## Code block

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

```java
import com.aerospike.client.AerospikeClient;

import com.aerospike.client.Key;

import com.aerospike.client.Record;

import com.aerospike.client.BatchRecord;

import com.aerospike.client.BatchResults;

import com.aerospike.client.Value;

import com.aerospike.client.cdt.MapOperation;

import com.aerospike.client.cdt.MapPolicy;

import com.aerospike.client.cdt.MapReturnType;

import com.aerospike.client.exp.Exp;

import com.aerospike.client.policy.QueryPolicy;

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.aerospike.client.task.IndexTask;

import java.util.Arrays;

import java.util.List;

// Establishes a connection to the server

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

// 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] " +

"       ] " +

"   ] " +

"}";

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

);

// 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.getKey();

        Record record = recordSet.getRecord();

        // Do something

        System.out.format("Key: %s | Record: %s\\n", key.userKey, record.bins);

    }

}

finally{

    recordSet.close();

}

// Close the connection to the server

client.close();
```