List indexing and querying

This page describes how to create a secondary index on bins whose data type is a list.

Indexing on List elements

• Similar to basic indexing, the indexable list element data types are numeric, string, and GeoJSON.
• You can index a List at any depth. Prior to Database 6.1.0, list indexing was only on the top-level element, not nested elements.
• When creating and index, specify explicitly that list bins should be indexed, and what data type to index on.
• When querying, specify that the query should be applied on a CDT data type.
• Similar to basic querying, equality, range for numeric and string data type, points-within-region, region-containing-points for GeoJSON data type are supported.

In this example we use the asadm tool to create two indexes on a single LIST, one for values in the list with numeric data type, one for values in the list with string data type. For further instructions, see Secondary index queries.

Admin+> manage sindex create numeric foo_list_int in list ns test set demo bin foo
Admin+> manage sindex create string foo_list_string in list ns test set demo bin foo

Elements of the indexed list are type checked, so a record whose foo bin contains [ 1, "2", 3, [4], 5 ] results in the following indexing:

Index On	Key Type	Index Type	Eligible Secondary Index Key
foo	string	LIST	”2”
foo	numeric	LIST	1, 3, 5

List index queries

The following example inserts three records, creates a string list index, and queries for records whose emails list contains a specific value.

Insert data

Two records have a list of email addresses in the emails bin. The third stores a scalar string instead of a list.

PK	username	emails
"u1"	"Bob Roberts"	["bob.roberts@gmail.com", "bob@yahoo.com"]
"u2"	"rocketbob"	["bigb@gmail.com", "bob@yahoo.com"]
"u3"	"samunwise"	"pppreciousss@gmail.com" (scalar string)

Java

import com.aerospike.client.AerospikeClient;
import com.aerospike.client.Bin;
import com.aerospike.client.Key;

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

Key key1 = new Key("test", "demo", "u1");
client.put(null, key1,
    new Bin("username", "Bob Roberts"),
    new Bin("emails", java.util.Arrays.asList("bob.roberts@gmail.com", "bob@yahoo.com"))
);

Key key2 = new Key("test", "demo", "u2");
client.put(null, key2,
    new Bin("username", "rocketbob"),
    new Bin("emails", java.util.Arrays.asList("bigb@gmail.com", "bob@yahoo.com"))
);

Key key3 = new Key("test", "demo", "u3");
client.put(null, key3,
    new Bin("username", "samunwise"),
    new Bin("emails", "pppreciousss@gmail.com")
);

Python

import aerospike

client = aerospike.client({"hosts": [("127.0.0.1", 3000)]}).connect()

key1 = ("test", "demo", "u1")
client.put(key1, {
    "username": "Bob Roberts",
    "emails": ["bob.roberts@gmail.com", "bob@yahoo.com"],
})

key2 = ("test", "demo", "u2")
client.put(key2, {
    "username": "rocketbob",
    "emails": ["bigb@gmail.com", "bob@yahoo.com"],
})

key3 = ("test", "demo", "u3")
client.put(key3, {
    "username": "samunwise",
    "emails": "pppreciousss@gmail.com",
})

C

#include <aerospike/aerospike.h>
#include <aerospike/aerospike_key.h>
#include <aerospike/as_arraylist.h>
#include <aerospike/as_record.h>

as_key key1;
as_key_init_str(&key1, "test", "demo", "u1");
as_record rec1;
as_record_inita(&rec1, 2);
as_record_set_str(&rec1, "username", "Bob Roberts");
as_arraylist emails1;
as_arraylist_init(&emails1, 2, 0);
as_arraylist_append_str(&emails1, "bob.roberts@gmail.com");
as_arraylist_append_str(&emails1, "bob@yahoo.com");
as_record_set_list(&rec1, "emails", (as_list*)&emails1);
aerospike_key_put(&as, &err, NULL, &key1, &rec1);
as_record_destroy(&rec1);

as_key key2;
as_key_init_str(&key2, "test", "demo", "u2");
as_record rec2;
as_record_inita(&rec2, 2);
as_record_set_str(&rec2, "username", "rocketbob");
as_arraylist emails2;
as_arraylist_init(&emails2, 2, 0);
as_arraylist_append_str(&emails2, "bigb@gmail.com");
as_arraylist_append_str(&emails2, "bob@yahoo.com");
as_record_set_list(&rec2, "emails", (as_list*)&emails2);
aerospike_key_put(&as, &err, NULL, &key2, &rec2);
as_record_destroy(&rec2);

as_key key3;
as_key_init_str(&key3, "test", "demo", "u3");
as_record rec3;
as_record_inita(&rec3, 2);
as_record_set_str(&rec3, "username", "samunwise");
as_record_set_str(&rec3, "emails", "pppreciousss@gmail.com");
aerospike_key_put(&as, &err, NULL, &key3, &rec3);
as_record_destroy(&rec3);

Go

import (
    as "github.com/aerospike/aerospike-client-go/v6"
)

client, _ := as.NewClient("127.0.0.1", 3000)

key1, _ := as.NewKey("test", "demo", "u1")
client.Put(nil, key1,
    as.NewBin("username", "Bob Roberts"),
    as.NewBin("emails", []string{"bob.roberts@gmail.com", "bob@yahoo.com"}),
)

key2, _ := as.NewKey("test", "demo", "u2")
client.Put(nil, key2,
    as.NewBin("username", "rocketbob"),
    as.NewBin("emails", []string{"bigb@gmail.com", "bob@yahoo.com"}),
)

key3, _ := as.NewKey("test", "demo", "u3")
client.Put(nil, key3,
    as.NewBin("username", "samunwise"),
    as.NewBin("emails", "pppreciousss@gmail.com"),
)

C#

using Aerospike.Client;

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

Key key1 = new Key("test", "demo", "u1");
client.Put(null, key1,
    new Bin("username", "Bob Roberts"),
    new Bin("emails", new List<string> { "bob.roberts@gmail.com", "bob@yahoo.com" })
);

Key key2 = new Key("test", "demo", "u2");
client.Put(null, key2,
    new Bin("username", "rocketbob"),
    new Bin("emails", new List<string> { "bigb@gmail.com", "bob@yahoo.com" })
);

Key key3 = new Key("test", "demo", "u3");
client.Put(null, key3,
    new Bin("username", "samunwise"),
    new Bin("emails", "pppreciousss@gmail.com")
);

Node.js

const Aerospike = await import("aerospike");

const client = await Aerospike.connect({ hosts: '127.0.0.1:3000' })

const key1 = new Aerospike.Key('test', 'demo', 'u1')
await client.put(key1, {
    username: 'Bob Roberts',
    emails: ['bob.roberts@gmail.com', 'bob@yahoo.com'],
})

const key2 = new Aerospike.Key('test', 'demo', 'u2')
await client.put(key2, {
    username: 'rocketbob',
    emails: ['bigb@gmail.com', 'bob@yahoo.com'],
})

const key3 = new Aerospike.Key('test', 'demo', 'u3')
await client.put(key3, {
    username: 'samunwise',
    emails: 'pppreciousss@gmail.com',
})

Create the list index

Use asadm to create a string index on the list elements of the emails bin:

Admin+> manage sindex create string email_idx in list ns test set demo bin emails

Query the list index

Query for records whose emails list contains a specific string value.

Query 1: Find records where emails contains "bigb@gmail.com" — matches 1 record (rocketbob).

Query 2: Find records where emails contains "bob@yahoo.com" — matches 2 records (Bob Roberts and rocketbob), since both lists contain that address.

Query 3: Find records where emails contains "pppreciousss@gmail.com" — matches 0 records, because that value is stored as a scalar string (not inside a list), so the list index does not cover it.

Java

import com.aerospike.client.query.Filter;
import com.aerospike.client.query.IndexCollectionType;
import com.aerospike.client.query.RecordSet;
import com.aerospike.client.query.Statement;

// Query 1: emails contains "bigb@gmail.com"
Statement stmt1 = new Statement();
stmt1.setNamespace("test");
stmt1.setSetName("demo");
stmt1.setFilter(Filter.contains("emails", IndexCollectionType.LIST, "bigb@gmail.com"));

RecordSet rs1 = client.query(null, stmt1);
while (rs1.next()) {
    System.out.println(rs1.getRecord().getString("username"));
}
rs1.close();
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
Statement stmt2 = new Statement();
stmt2.setNamespace("test");
stmt2.setSetName("demo");
stmt2.setFilter(Filter.contains("emails", IndexCollectionType.LIST, "bob@yahoo.com"));

RecordSet rs2 = client.query(null, stmt2);
while (rs2.next()) {
    System.out.println(rs2.getRecord().getString("username"));
}
rs2.close();
// Output: Bob Roberts
//         rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
Statement stmt3 = new Statement();
stmt3.setNamespace("test");
stmt3.setSetName("demo");
stmt3.setFilter(Filter.contains("emails", IndexCollectionType.LIST, "pppreciousss@gmail.com"));

RecordSet rs3 = client.query(null, stmt3);
while (rs3.next()) {
    System.out.println(rs3.getRecord().getString("username"));
}
rs3.close();
// Output: (none)

Python

from aerospike_helpers.operations import list_operations
from aerospike import predicates as p

# Query 1: emails contains "bigb@gmail.com"
query1 = client.query("test", "demo")
query1.where(p.contains("emails", aerospike.INDEX_TYPE_LIST, "bigb@gmail.com"))
for rec in query1.results():
    print(rec[2]["username"])
# Output: rocketbob

# Query 2: emails contains "bob@yahoo.com"
query2 = client.query("test", "demo")
query2.where(p.contains("emails", aerospike.INDEX_TYPE_LIST, "bob@yahoo.com"))
for rec in query2.results():
    print(rec[2]["username"])
# Output: Bob Roberts
#         rocketbob

# Query 3: emails contains "pppreciousss@gmail.com"
query3 = client.query("test", "demo")
query3.where(p.contains("emails", aerospike.INDEX_TYPE_LIST, "pppreciousss@gmail.com"))
for rec in query3.results():
    print(rec[2]["username"])
# Output: (none)

C

#include <aerospike/aerospike_query.h>
#include <aerospike/as_query.h>

bool query_cb(const as_val* val, void* udata) {
    if (!val) return false;
    as_record* rec = as_record_fromval(val);
    printf("%s\n", as_record_get_str(rec, "username"));
    return true;
}

// Query 1: emails contains "bigb@gmail.com"
as_query q1;
as_query_init(&q1, "test", "demo");
as_query_where_inita(&q1, 1);
as_query_where(&q1, "emails", as_contains(LIST, STRING, "bigb@gmail.com"));
aerospike_query_foreach(&as, &err, NULL, &q1, query_cb, NULL);
as_query_destroy(&q1);
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
as_query q2;
as_query_init(&q2, "test", "demo");
as_query_where_inita(&q2, 1);
as_query_where(&q2, "emails", as_contains(LIST, STRING, "bob@yahoo.com"));
aerospike_query_foreach(&as, &err, NULL, &q2, query_cb, NULL);
as_query_destroy(&q2);
// Output: Bob Roberts
//         rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
as_query q3;
as_query_init(&q3, "test", "demo");
as_query_where_inita(&q3, 1);
as_query_where(&q3, "emails", as_contains(LIST, STRING, "pppreciousss@gmail.com"));
aerospike_query_foreach(&as, &err, NULL, &q3, query_cb, NULL);
as_query_destroy(&q3);
// Output: (none)

Go

import (
    as "github.com/aerospike/aerospike-client-go/v6"
)

// Query 1: emails contains "bigb@gmail.com"
stmt1 := as.NewStatement("test", "demo")
stmt1.SetFilter(as.NewContainsFilter("emails", as.ICT_LIST, "bigb@gmail.com"))
rs1, _ := client.Query(nil, stmt1)
for rec := range rs1.Results() {
    fmt.Println(rec.Record.Bins["username"])
}
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
stmt2 := as.NewStatement("test", "demo")
stmt2.SetFilter(as.NewContainsFilter("emails", as.ICT_LIST, "bob@yahoo.com"))
rs2, _ := client.Query(nil, stmt2)
for rec := range rs2.Results() {
    fmt.Println(rec.Record.Bins["username"])
}
// Output: Bob Roberts
//         rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
stmt3 := as.NewStatement("test", "demo")
stmt3.SetFilter(as.NewContainsFilter("emails", as.ICT_LIST, "pppreciousss@gmail.com"))
rs3, _ := client.Query(nil, stmt3)
for rec := range rs3.Results() {
    fmt.Println(rec.Record.Bins["username"])
}
// Output: (none)

C#

using Aerospike.Client;

// Query 1: emails contains "bigb@gmail.com"
Statement stmt1 = new Statement();
stmt1.SetNamespace("test");
stmt1.SetSetName("demo");
stmt1.SetFilter(Filter.Contains("emails", IndexCollectionType.LIST, "bigb@gmail.com"));

RecordSet rs1 = client.Query(null, stmt1);
while (rs1.Next()) {
    Console.WriteLine(rs1.Record.GetString("username"));
}
rs1.Close();
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
Statement stmt2 = new Statement();
stmt2.SetNamespace("test");
stmt2.SetSetName("demo");
stmt2.SetFilter(Filter.Contains("emails", IndexCollectionType.LIST, "bob@yahoo.com"));

RecordSet rs2 = client.Query(null, stmt2);
while (rs2.Next()) {
    Console.WriteLine(rs2.Record.GetString("username"));
}
rs2.Close();
// Output: Bob Roberts
//         rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
Statement stmt3 = new Statement();
stmt3.SetNamespace("test");
stmt3.SetSetName("demo");
stmt3.SetFilter(Filter.Contains("emails", IndexCollectionType.LIST, "pppreciousss@gmail.com"));

RecordSet rs3 = client.Query(null, stmt3);
while (rs3.Next()) {
    Console.WriteLine(rs3.Record.GetString("username"));
}
rs3.Close();
// Output: (none)

Node.js

const Aerospike = await import("aerospike");
const filter = Aerospike.filter;

// Query 1: emails contains "bigb@gmail.com"
const query1 = client.query('test', 'demo')
query1.where(filter.contains('emails', 'bigb@gmail.com', Aerospike.indexType.LIST))
const stream1 = query1.foreach()
stream1.on('data', (rec) => console.log(rec.bins.username))
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
const query2 = client.query('test', 'demo')
query2.where(filter.contains('emails', 'bob@yahoo.com', Aerospike.indexType.LIST))
const stream2 = query2.foreach()
stream2.on('data', (rec) => console.log(rec.bins.username))
// Output: Bob Roberts
//         rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
const query3 = client.query('test', 'demo')
query3.where(filter.contains('emails', 'pppreciousss@gmail.com', Aerospike.indexType.LIST))
const stream3 = query3.foreach()
stream3.on('data', (rec) => console.log(rec.bins.username))
// Output: (none)

The third query returns no results because "pppreciousss@gmail.com" is stored as a scalar string in record u3, not as an element inside a list. The list index only covers values that are elements of a list bin. Querying without the list index (WHERE emails = "pppreciousss@gmail.com") would result in an AEROSPIKE_ERR_INDEX_NOT_FOUND error.

Known limitations

When using range queries on lists, records can be returned multiple times if the list contains multiple values that fall within the range.