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, up to the 64-level CDT nesting limit (Database 8.2.0 and later). Prior to Database 6.1.0, List indexing was only on the top-level element, not nested elements.
• When creating an 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 integer and string data types, points-within-region, region-containing-points for GeoJSON data type are supported.

The following example uses asadm to create two indexes on a single List, one for integer values and one for string values. For further instructions, see Secondary index queries.

Admin+> manage sindex create integer 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

Note

Prior to Database 8.2.0, use numeric in place of integer. See Create an index for details on deprecated alias behavior and listing output.

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	integer	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 SDK

import com.aerospike.client.sdk.Cluster;
import com.aerospike.client.sdk.ClusterDefinition;
import com.aerospike.client.sdk.DataSet;
import com.aerospike.client.sdk.Session;
import com.aerospike.client.sdk.policy.Behavior;
import java.util.List;

Cluster cluster = new ClusterDefinition("127.0.0.1", 3000).connect();
Session session = cluster.createSession(Behavior.DEFAULT);
DataSet demo = DataSet.of("test", "demo");

session.upsert(demo.id("u1"))
    .bin("username").setTo("Bob Roberts")
    .bin("emails").setTo(List.of("bob.roberts@gmail.com", "bob@yahoo.com"))
    .execute();

session.upsert(demo.id("u2"))
    .bin("username").setTo("rocketbob")
    .bin("emails").setTo(List.of("bigb@gmail.com", "bob@yahoo.com"))
    .execute();

session.upsert(demo.id("u3"))
    .bin("username").setTo("samunwise")
    .bin("emails").setTo("pppreciousss@gmail.com")
    .execute();

Python SDK

from aerospike_sdk import Behavior, DataSet
from aerospike_sdk.sync import ClusterDefinition

cluster = ClusterDefinition("127.0.0.1", 3000).connect()
session = cluster.create_session(Behavior.DEFAULT)
demo = DataSet.of("test", "demo")

session.upsert(demo.id("u1")).put({
    "username": "Bob Roberts",
    "emails": ["bob.roberts@gmail.com", "bob@yahoo.com"],
}).execute()

session.upsert(demo.id("u2")).put({
    "username": "rocketbob",
    "emails": ["bigb@gmail.com", "bob@yahoo.com"],
}).execute()

session.upsert(demo.id("u3")).put({
    "username": "samunwise",
    "emails": "pppreciousss@gmail.com",
}).execute()

Rust

use aerospike::{as_bin, as_list, as_val, Client, ClientPolicy, Key, WritePolicy};

let client = Client::new(&ClientPolicy::default(), &"127.0.0.1:3000").await?;

let key1 = Key::new("test", "demo", as_val!("u1"))?;
client.put(&WritePolicy::default(), &key1, &[
    as_bin!("username", "Bob Roberts"),
    as_bin!("emails", as_list!("bob.roberts@gmail.com", "bob@yahoo.com")),
]).await?;

let key2 = Key::new("test", "demo", as_val!("u2"))?;
client.put(&WritePolicy::default(), &key2, &[
    as_bin!("username", "rocketbob"),
    as_bin!("emails", as_list!("bigb@gmail.com", "bob@yahoo.com")),
]).await?;

let key3 = Key::new("test", "demo", as_val!("u3"))?;
client.put(&WritePolicy::default(), &key3, &[
    as_bin!("username", "samunwise"),
    as_bin!("emails", "pppreciousss@gmail.com"),
]).await?;

C#

using System.Collections.Generic;
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")
);

Go

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

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

key1, _ := as.NewKey("test", "demo", "u1")
client.PutBins(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.PutBins(nil, key2,
    as.NewBin("username", "rocketbob"),
    as.NewBin("emails", []string{"bigb@gmail.com", "bob@yahoo.com"}),
)

key3, _ := as.NewKey("test", "demo", "u3")
client.PutBins(nil, key3,
    as.NewBin("username", "samunwise"),
    as.NewBin("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',
})

C

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

as_config config;
as_config_init(&config);
as_config_add_host(&config, "127.0.0.1", 3000);
aerospike as;
aerospike_init(&as, &config);
as_error err;
aerospike_connect(&as, &err);

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

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",
})

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 SDK

import com.aerospike.client.sdk.RecordStream;

// Query 1: emails contains "bigb@gmail.com"
try (RecordStream rs = session.query(demo).where("'bigb@gmail.com' in $.emails").execute()) {
    rs.forEachRemaining(r -> System.out.println(r.recordOrThrow().getString("username")));
}
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
try (RecordStream rs = session.query(demo).where("'bob@yahoo.com' in $.emails").execute()) {
    rs.forEachRemaining(r -> System.out.println(r.recordOrThrow().getString("username")));
}
// Output, in no guaranteed order:
// Bob Roberts
// rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
try (RecordStream rs = session.query(demo).where("'pppreciousss@gmail.com' in $.emails").execute()) {
    rs.forEachRemaining(r -> System.out.println(r.recordOrThrow().getString("username")));
}
// Output: (none)

Python SDK

# Query 1: emails contains "bigb@gmail.com"
for row in session.query(demo).where("'bigb@gmail.com' in $.emails").execute():
    print(row.record_or_raise().bins["username"])
# Output: rocketbob

# Query 2: emails contains "bob@yahoo.com"
for row in session.query(demo).where("'bob@yahoo.com' in $.emails").execute():
    print(row.record_or_raise().bins["username"])
# Output, in no guaranteed order:
# Bob Roberts
# rocketbob

# Query 3: emails contains "pppreciousss@gmail.com"
for row in session.query(demo).where("'pppreciousss@gmail.com' in $.emails").execute():
    print(row.record_or_raise().bins["username"])
# Output: (none)

Rust

use aerospike::{query::Filter, Bins, CollectionIndexType, PartitionFilter, QueryPolicy, Statement};
use futures::StreamExt;

// Query 1: emails contains "bigb@gmail.com"
let mut stmt1 = Statement::new("test", "demo", Bins::All);
stmt1.add_filter(Filter::contains("emails", "bigb@gmail.com", CollectionIndexType::List));
let mut rs1 = client.query(&QueryPolicy::default(), PartitionFilter::all(), stmt1)
    .await?.into_stream();
while let Some(rec) = rs1.next().await {
    println!("{}", rec?.bins["username"]);
}
// Output: rocketbob

// Query 2: emails contains "bob@yahoo.com"
let mut stmt2 = Statement::new("test", "demo", Bins::All);
stmt2.add_filter(Filter::contains("emails", "bob@yahoo.com", CollectionIndexType::List));
let mut rs2 = client.query(&QueryPolicy::default(), PartitionFilter::all(), stmt2)
    .await?.into_stream();
while let Some(rec) = rs2.next().await {
    println!("{}", rec?.bins["username"]);
}
// Output, in no guaranteed order:
// Bob Roberts
// rocketbob

// Query 3: emails contains "pppreciousss@gmail.com"
let mut stmt3 = Statement::new("test", "demo", Bins::All);
stmt3.add_filter(Filter::contains("emails", "pppreciousss@gmail.com", CollectionIndexType::List));
let mut rs3 = client.query(&QueryPolicy::default(), PartitionFilter::all(), stmt3)
    .await?.into_stream();
while let Some(rec) = rs3.next().await {
    println!("{}", rec?.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, in no guaranteed order:
// 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)

Go

import (
    "fmt"

    as "github.com/aerospike/aerospike-client-go/v8"
)

// 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, in no guaranteed order:
// 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)

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))
for (const rec of await query1.results()) {
    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))
for (const rec of await query2.results()) {
    console.log(rec.bins.username)
}
// Output, in no guaranteed order:
// 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))
for (const rec of await query3.results()) {
    console.log(rec.bins.username)
}
// Output: (none)

C

#include <stdio.h>
#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, in no guaranteed order:
// 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)

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, in no guaranteed order:
// 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 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, in no guaranteed order:
# 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)

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. A Developer SDK query that allows the scan with allowScansWithWhere runs as a primary-index query instead, reading every record in the set, and returns samunwise. See Control PI fallback.

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.