Secondary index query

The following example demonstrates using a secondary index to find a document record. It does the following:

Create a document.
Add the document to an Aerospike set.
Create a secondary index.
Query the document using the secondary index.

The example demonstrates two ways of setting up a secondary index. The first method extracts the secondary index target during insertion of the document, then uses it for the query. The second method uses the document bin directly for the query.

Setup

Import the necessary helpers, create a client connection, and create a key.

import aerospike
from aerospike_helpers.operations import map_operations
from aerospike import predicates
from aerospike_helpers import cdt_ctx

import pprint
import json


# Set Aerospike host config
config = {"hosts": [("localhost", 3000)]}

# Create the Aerospike client and connect
client = aerospike.client(config).connect()

# Aerospike namespace, set, and key_id to be
# used for the Aerospike key
namespace = "test"
as_set = "table1"

Create a JSON document

Prepare the JSON document to be sent to Aerospike.

# Example JSON document
transactions = """[{"txn_id":"1111", "name": "Davis", "item_id":"A1234", "count":1},
                  {"txn_id":"2222", "name": "Johnson", "item_id":"B2345", "count":2},
                  {"txn_id":"3333", "name": "Johnson", "item_id":"C3456", "count":2},
                  {"txn_id":"4444", "name": "Lee", "item_id":"D4567", "count":3}]"""

# Convert string to Python dict
transactions_dict = json.loads(transactions)
pprint.pprint(transactions_dict)

Write

Write the document to Aerospike.

# Create the write policy
write_policy = {"key": aerospike.POLICY_KEY_SEND}

# Insert the transactions
for transaction in transactions_dict:
    # Set an Aerospike bin with the bin name "transaction" and put
    # the JSON document in as a map.
    # Also add the "name" as a bin for indexing.
    bins = {"name": transaction["name"],
            "transaction": transaction}

    # define aerospike key
    key = (namespace, as_set, transaction["txn_id"])

    # Write the record to Aerospike
    try:
        # Insert the record
        client.put(key, bins, policy=write_policy)

        (key_, meta, bins) = client.get(key)
        print("Create succeeded\nKey: ", key[2], "\nRecord:")
        pprint.pprint(bins)
    except aerospike.exception.AerospikeError as e:
        print("Create failed\nError: {0} [{1}]".format(e.msg, e.code))

Create a secondary index - method 1

Create a secondary index and execute a query. This indexing method extracts the secondary index target during insertion of the document, then uses it for the query.

# Create a secondary index for transaction records, method 1:
# use bin extracted for secondary index when you inserted the record.
try:
    client.index_string_create(namespace, as_set, "name", "test_name_idx")
except aerospike.exception.AerospikeError as e:
    print("Create index failed\nError: {0} [{1}]".format(e.msg, e.code))
else:
    print("Successfully created index test_name_idx on the 'name' bin")

# Set up a query to use the secondary index
test_name_idx_qry = client.query(namespace, as_set)

# Add the projection
test_name_idx_qry.select("name", "transaction")

# Add the query predicate
test_name_idx_qry.where(predicates.equals("name", "Johnson"))

# Define callback function
def print_record(result_tuple):
    print(result_tuple)
    return

# Execute query
test_name_idx_qry.foreach(print_record)

# Clean up index
client.index_remove(namespace, "test_name_idx")

Create a secondary index - method 2

Create a secondary index and execute a query. This indexing method uses the document bin directly for the query.

# Create a secondary index for transaction records,
# method 2: use a map index
try:
    client.index_map_values_create(namespace, as_set, "transaction", aerospike.INDEX_STRING, "test_transaction_idx")
except aerospike.exception.AerospikeError as e:
    print("Create index failed\nError: {0} [{1}]".format(e.msg, e.code))
else:
    print("Successfully created index test_transaction_idx on the 'transaction' bin")

# Set up a query to use the secondary index
test_transaction_idx_qry = client.query(namespace, as_set)

# Add the projection
test_transaction_idx_qry.select("name", "transaction")

# Add the query predicate
test_transaction_idx_qry.where(predicates.contains("transaction", aerospike.INDEX_TYPE_MAPVALUES, "Johnson"))

# Define callback function
def print_record(result_tuple):
    print(result_tuple)
    return

# Execute query
test_transaction_idx_qry.foreach(print_record)

# Clean up index
client.index_remove(namespace, "test_transaction_idx")`

Code block - method 1

Expand this section for a single code block to run the example with indexing method 1.

import aerospike
from aerospike_helpers.operations import map_operations
from aerospike import predicates
from aerospike_helpers import cdt_ctx

import pprint
import json


# Set Aerospike host config
config = {"hosts": [("localhost", 3000)]}

# Create the Aerospike client and connect
client = aerospike.client(config).connect()

# Aerospike namespace, set, and key_id to be
# used for the Aerospike key
namespace = "test"
as_set = "table1"

# Example JSON document
transactions = """[{"txn_id":"1111", "name": "Davis", "item_id":"A1234", "count":1},
                  {"txn_id":"2222", "name": "Johnson", "item_id":"B2345", "count":2},
                  {"txn_id":"3333", "name": "Johnson", "item_id":"C3456", "count":2},
                  {"txn_id":"4444", "name": "Lee", "item_id":"D4567", "count":3}]"""

# Convert string to Python dict
transactions_dict = json.loads(transactions)
pprint.pprint(transactions_dict)

# Create the write policy
write_policy = {"key": aerospike.POLICY_KEY_SEND}

# Insert the transactions
for transaction in transactions_dict:
    # Set an Aerospike bin with the bin name "transaction" and put
    # the JSON document in as a map.
    # Also add the "name" as a bin for indexing.
    bins = {"name": transaction["name"],
            "transaction": transaction}

    # define aerospike key
    key = (namespace, as_set, transaction["txn_id"])

    # Write the record to Aerospike
    try:
        # Insert the record
        client.put(key, bins, policy=write_policy)

        (key_, meta, bins) = client.get(key)
        print("Create succeeded\nKey: ", key[2], "\nRecord:")
        pprint.pprint(bins)
    except aerospike.exception.AerospikeError as e:
        print("Create failed\nError: {0} [{1}]".format(e.msg, e.code))

# Create a secondary index for transaction records, method 1:
# use bin extracted for secondary index when you inserted the record.
try:
    client.index_string_create(namespace, as_set, "name", "test_name_idx")
except aerospike.exception.AerospikeError as e:
    print("Create index failed\nError: {0} [{1}]".format(e.msg, e.code))
else:
    print("Successfully created index test_name_idx on the 'name' bin")

# Set up a query to use the secondary index
test_name_idx_qry = client.query(namespace, as_set)

# Add the projection
test_name_idx_qry.select("name", "transaction")

# Add the query predicate
test_name_idx_qry.where(predicates.equals("name", "Johnson"))

# Define callback function
def print_record(result_tuple):
    print(result_tuple)
    return

# Execute query
test_name_idx_qry.foreach(print_record)

# Clean up index
client.index_remove(namespace, "test_name_idx")

Code block - method 2

Expand this section for a single code block to run the example with indexing method 2.

import aerospike
from aerospike_helpers.operations import map_operations
from aerospike import predicates
from aerospike_helpers import cdt_ctx

import pprint
import json


# Set Aerospike host config
config = {"hosts": [("localhost", 3000)]}

# Create the Aerospike client and connect
client = aerospike.client(config).connect()

# Aerospike namespace, as_set, and key_id to be
# used for the Aerospike key
namespace = "test"
as_set = "table1"

# Example JSON document
transactions = """[{"txn_id":"1111", "name": "Davis", "item_id":"A1234", "count":1},
                  {"txn_id":"2222", "name": "Johnson", "item_id":"B2345", "count":2},
                  {"txn_id":"3333", "name": "Johnson", "item_id":"C3456", "count":2},
                  {"txn_id":"4444", "name": "Lee", "item_id":"D4567", "count":3}]"""

# Convert string to Python dict
transactions_dict = json.loads(transactions)
pprint.pprint(transactions_dict)

# Create the write policy
write_policy = {"key": aerospike.POLICY_KEY_SEND}

# Insert the transactions
for transaction in transactions_dict:
    # Set an Aerospike bin with the bin name "transaction" and put
    # the JSON document in as a map.
    # Also add the "name" as a bin for indexing.
    bins = {"name": transaction["name"],
            "transaction": transaction}

    # define aerospike key
    key = (namespace, as_set, transaction["txn_id"])

    # Write the record to Aerospike
    try:
        # Insert the record
        client.put(key, bins, policy=write_policy)

        (key_, meta, bins) = client.get(key)
        print("Create succeeded\nKey: ", key[2], "\nRecord:")
        pprint.pprint(bins)
    except aerospike.exception.AerospikeError as e:
        print("Create failed\nError: {0} [{1}]".format(e.msg, e.code))

# Create a secondary index for transaction records,
# method 2: use a map index
try:
    client.index_map_values_create(namespace, as_set, "transaction", aerospike.INDEX_STRING, "test_transaction_idx")
except aerospike.exception.AerospikeError as e:
    print("Create index failed\nError: {0} [{1}]".format(e.msg, e.code))
else:
    print("Successfully created index test_transaction_idx on the 'transaction' bin")

# Set up a query to use the secondary index
test_transaction_idx_qry = client.query(namespace, as_set)

# Add the projection
test_transaction_idx_qry.select("name", "transaction")

# Add the query predicate
test_transaction_idx_qry.where(predicates.contains("transaction", aerospike.INDEX_TYPE_MAPVALUES, "Johnson"))

# Define callback function
def print_record(result_tuple):
    print(result_tuple)
    return

# Execute query
test_transaction_idx_qry.foreach(print_record)

# Clean up index
client.index_remove(namespace, "test_transaction_idx")