Primary index

Note

Prior to Server 6.0, primary index (PI) queries were called scans and had policies defined through the scan policy. See Queries for more information.

Jump to the Code block for a combined complete example.

Basic PI queries have the following features:

• Filter records by set name.
• Filter records by filter expressions.
• Limit the number of records returned, useful for pagination.
• Return only record digests and metadata (generation and TTL).
• Return specified bins.

Setup

The following examples use the setup and record structure below to illustrate primary index queries in an Aerospike database.

import com.aerospike.client.AerospikeClient;
import com.aerospike.client.Key;
import com.aerospike.client.Operation;
import com.aerospike.client.Record;
import com.aerospike.client.Value;
import com.aerospike.client.cdt.MapOperation;
import com.aerospike.client.cdt.MapReturnType;
import com.aerospike.client.exp.Exp;
import com.aerospike.client.exp.ListExp;
import com.aerospike.client.exp.MapExp;
import com.aerospike.client.policy.QueryPolicy;
import com.aerospike.client.query.PartitionFilter;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.Statement;

import java.util.Arrays;

// Establishes a connection to the server
AerospikeClient client = new AerospikeClient("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

Policies

See Basic Queries for query policy information.

Query a set

The following example queries the sandbox namespace and ufodata set name, while limiting the record set to only 20 records.

Note

If the you are querying a smaller set, creating a set index may provide better performance.

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.setNamespace("sandbox");
stmt.setSetName("ufodata");

// Set max records to return
stmt.setMaxRecords(20);

// 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 with projection operations

Use projection operations to project read operations or read expressions in the result, similar to batched operations. The following example returns only city and state from the report map for each matching record:

Basic read operations (Operation.get(String), Operation.get(), Operation.getHeader()) are supported for query projection on server versions prior to 8.1.2. Extended read operations, such as the CDT MapOperation.getByKeyList() used below, require server version 8.1.2 or later.

// Create statement
Statement stmt = new Statement();
stmt.setNamespace("sandbox");
stmt.setSetName("ufodata");
stmt.setMaxRecords(20);

// Project map keys from report bin
stmt.setOperations(Operation.array(
    MapOperation.getByKeyList("report", Arrays.asList(Value.get("city"), Value.get("state")), MapReturnType.VALUE)
));

RecordSet recordSet = client.query(null, stmt);
try {
    while (recordSet.next()) {
        Record record = recordSet.getRecord();
        // record.bins contains the projected map values
    }
} finally {
    recordSet.close();
}

Query with a metadata filter

The following example queries the same namespace and set as the example above, but also adds a metadata Filter Expression that will only return records that are greater than 16 KiB.

// Create query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.gt(Exp.deviceSize(), Exp.val(1024 * 16))
);

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.setNamespace("sandbox");
stmt.setSetName("ufodata");

// 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 with a data filter

The following example queries the same namespace and set as the example above, but also adds a data Filter Expression that will only return records where the occurred bin value is in the inclusive range 20200101 to 20211231.

Take a look at secondary index queries to see how this same query can be run more efficiently with an index.

// Create query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.let(
        Exp.def("bin", Exp.intBin("occurred")),
        Exp.and(
            Exp.ge(Exp.var("bin"), Exp.val(20210101)),
            Exp.le(Exp.var("bin"), Exp.val(20211231))
        )
    )
);

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.setNamespace("sandbox");
stmt.setSetName("ufodata");

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

Pagination

Pagination uses a combination of a partition filter and a defined maximum records to return query results. The partition filter maintains a cursor identifying the end of the current page and the beginning of the next. Moving to the next page of results is as simple as executing the query again, with the previously defined partition filter.

Note

Defining a maximum number of records per page to return guarantees that no page will contain more than the maximum number, but some pages may contain fewer than the maximum number. Also, if you run the same paginated query multiple times, the number of results per page may differ, depending on the order in which they are delivered by the nodes in the cluster.

The following example executes a query with an Expression Filter identifying records with more than 3 shape items in the report map, returning 10 records per page. The partition filter is set to query all 4096 partitions in the database.

// Create query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.gt(
        ListExp.size(
            MapExp.getByKey(MapReturnType.VALUE, Exp.Type.LIST, Exp.val("shape"), Exp.mapBin("report"))
        ),
        Exp.val(3)
    )
);

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.setNamespace("sandbox");
stmt.setSetName("ufodata");

// Set max records to return
stmt.setMaxRecords(10);

// Create the partition filter
PartitionFilter partitionFilter = PartitionFilter.all();

// Execute the query
RecordSet recordSet = client.queryPartitions(queryPolicy, stmt, partitionFilter);

// Get the results
try{
    int page = 0;
    while(!partitionFilter.isDone()){
        int count = 0;
        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);
            count++;
        }
        page++;
        System.out.format("\\nPage %s | %s records\\n\\n", page, count);
        // Execute the query reusing the partitionFilter until all records returned
        recordSet = client.queryPartitions(queryPolicy, stmt, partitionFilter);
    }
}
finally{
    recordSet.close();
}

// Close the connection to the server
client.close();

Code block

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

import com.aerospike.client.AerospikeClient;
import com.aerospike.client.Key;
import com.aerospike.client.Record;
import com.aerospike.client.cdt.MapReturnType;
import com.aerospike.client.exp.Exp;
import com.aerospike.client.exp.ListExp;
import com.aerospike.client.exp.MapExp;
import com.aerospike.client.policy.QueryPolicy;
import com.aerospike.client.query.PartitionFilter;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.Statement;

// Establishes a connection to the server
AerospikeClient client = new AerospikeClient("127.0.0.1", 3000);

// Create query policy
QueryPolicy queryPolicy = new QueryPolicy();
queryPolicy.filterExp = Exp.build(
    Exp.let(
        Exp.def("bin", Exp.intBin("occurred")),
        Exp.and(
            Exp.ge(Exp.var("bin"), Exp.val(20210101)),
            Exp.le(Exp.var("bin"), Exp.val(20211231))
        )
    )
);

// Create statement
Statement stmt = new Statement();

// Set namespace and set name
stmt.setNamespace("sandbox");
stmt.setSetName("ufodata");

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