Untitled :: DataStax Astra DB Classic Documentation

Create an index

Tables can contain partition keys and clustering keys, both of which define the primary key. A table can also include non-primary keys.

If you wish to create a table query that uses anything other than the partition key to define which row or rows are to be retrieved, a column index must be created on each column to read the data.

Currently, those indexes can be created with REST, CQL, or GraphQL.

Send a POST request to /api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes to create an index. Set the index definitions in the JSON body in the --data. In this example, an index name is defined as fav_books_idx for the column favorite_books in the keyspace users_keyspace and table users. An option to only create the index if it already exists is set with ifNotExists: true; the default is false.

Two additional options are available:

type can be defined to use SAI indexes if desired; the default is secondary indexes.
kind defines the kind of index for map collections; the choices available are VALUES/KEYS/ENTRIES/FULL.

cURL command (/v2)
Result

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "favorite_books",
  "name": "fav_books_idx",
  "ifNotExists": true
}'

{
  "success": true
}

Here is an additional example, which creates indexes that are used in the REST API examples:

cURL command
Result

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "favorite_books",
  "name": "fav_books_idex",
  "ifNotExists": true
}'

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "top_three_tv_shows",
  "name": "tv_idx",
  "ifNotExists": true
}'

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "evaluations",
  "name": "evalv_idx",
  "ifNotExists": true
}'

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "evaluations",
  "name": "evalk_idx",
  "kind": "KEYS",
  "ifNotExists": true
}'

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "evaluations",
  "name": "evale_idx",
  "kind": "ENTRIES",
  "ifNotExists": true
}'

curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
  "column": "current_country",
  "name": "country_idx",
  "ifNotExists": true
}'

{
  "success": true
}
{
  "success": true
}
{
  "success": true
}
{
  "success": true
}
{
  "success": true
}
{
  "success": true
}

Note the use of the kind in the definitions used for the map collection evaluations.