Creating an index in your keyspace using GraphQL

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 GraphQL API to create indexes, you can use mutations as shown below:

Use the application token you generated to create schema in your keyspace using the GraphQL playground.

Creating an index

You can create an index using a mutation in /graphql-schema. In the following example, three indexes are created for the tables book and reader. The table columns for these indexes are created are author, birthdate, and email.

An index name can be defined, such as author_idx in this example. An additional option, indexType can be defined to use SAI indexes if desired.

  • graphQL mutation

  • cURL command

  • Result

mutation createIndexes {
  book: createIndex(
    keyspaceName:"library",
    tableName:"book",
    columnName:"author",
    indexName:"author_idx"
  )
  reader: createIndex(
      keyspaceName:"library",
      tableName:"reader",
      columnName:"birthdate",
      indexName:"reader_bdate_idx"
  )
  reader2: createIndex(
      keyspaceName:"library",
      tableName:"reader",
      columnName:"email",
      indexName:"reader_email_idx"
  )
}
curl --location --request POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createIndexes {\n  book: createIndex(\n    keyspaceName:\"library\",\n    tableName:\"book\",\n    columnName:\"author\",\n    indexName:\"author_idx\"\n  )\n  reader: createIndex(\n      keyspaceName:\"library\",\n      tableName:\"reader\",\n      columnName:\"birthdate\",\n      indexName:\"reader_bdate_idx\"\n  )\n  reader2: createIndex(\n      keyspaceName:\"library\",\n      tableName:\"reader\",\n      columnName:\"email\",\n      indexName:\"reader_email_idx\"\n  )\n}","variables":{}}'
Result TBD

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

  • cURL command

  • CQL command

  • Result

curl --location --request POST 'http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema' \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createIndexes {\n  user1: createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"favorite_books\",\n    indexName:\"fav_books_idx\",\n    indexKind: VALUES\n  )\n  user2:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"top_three_tv_shows\",\n    indexName:\"tv_idx\",\n    indexKind: VALUES\n  )\n  user3:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"evaluations\",\n    indexName:\"evalv_idx\",\n    indexKind: VALUES\n  )\n   user4:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"evaluations\",\n    indexName:\"evalk_idx\",\n    indexKind: KEYS\n  )\n   user5:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"evaluations\",\n    indexName:\"evale_idx\",\n    indexKind: ENTRIES\n  )\n    users6: createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"current_country\",\n    indexName:\"country_idx\"\n  )\n}","variables":{}}'
CREATE INDEX books_idx ON users_keyspace.users (VALUES(favorite_books));
CREATE INDEX tv_idx ON users_keyspace.users (VALUES (top_three_tv_shows));
CREATE INDEX evalk_idx ON users_keyspace.users (KEYS (evaluations));
CREATE INDEX evalv_idx ON users_keyspace.users (VALUES (evaluations));
CREATE INDEX evale_idx ON users_keyspace.users (ENTRIES (evaluations));
CREATE INDEX country_idx ON users_keyspace.users (VALUES (current_country));
Result TBD

The CQL commands for creating these indexes is included here for reference. The cqlsh tool can be used to create the indexes if desired.