Creating an index in your keyspace

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.

To use the REST API to create indexes, you’ll need the application token you generated to create other schema in your keyspace.

Creating an index

Send a POST request to /api/rest/v2/schemas/keyspaces/{keyspace_name}/tables/{table_name}/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 http://$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 http://$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 http://$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 http://$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 http://$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 http://$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 http://$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.