Add columns to your table

After creating a table in your keyspace, you can add columns to your table definition.

Use the Add a column definition endpoint to replace a column definition in the table you created.

Adding columns to table schema

Add a column with a simple data type

If you need to add an attribute to something you are storing in a table, you can add a column by sending a POST request that specifies the column name and data type in the --data (shortcut -d).

cURL command (/v2)
Result

curl -s -L -X POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
   "name": "email",
   "typeDefinition": "text"
}'

{ "name": "email" }

Change a column name or data type

To change the name or data type of an existing column, use a similar command, but send a PUT request instead:

cURL command (/v2)
Result

curl -s -L \
-X PUT https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns/firstname \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
      "name": "first",
      "typeDefinition": "varchar"
}'

{
  "name": "first"
}

Notice that the request changes the column name firstname to first by specifying the new name and that the data type is still required.

Collections can be defined when tables are created or added later. Recall that collections can be defined as frozen, meaning that the entire collection must be retrieved, rather than a part of the collection.

See this CQL document to learn more about the keyword frozen and its uses.

To add a set to a table, specify the data type set along with the data type of the set contents, such as text:

cURL command (/v2)
Result

curl -s -L -X POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
   "name": "favorite_books",
   "typeDefinition": "set<text>"
}'

{"name":"favorite_books"}

If the set were defined as a frozen set, specify "typeDefinition": "frozen<set<text>>".

To add a list to a table, specify the data type list along with the data type of the list contents, such as text. The difference between a set and a list is that a set is unordered, whereas a list is ordered:

cURL command (/v2)
Result

curl -s -L -X POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
   "name": "top_three_tv_shows",
   "typeDefinition": "list<text>"
}'

{"name":"top_three_tv_shows"}

To add a map to a table, specify the data type map along with the data type of the map key and the map value:

cURL command (/v2)
Result

curl -s -L -X POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
   "name": "evaluations",
   "typeDefinition": "map<int,text>"
}'

{"name":"evaluations"}

Tuple

To add a tuple to a table, specify the data type tuple along with the data type of the each item in the tuple. A tuple can consist of two or more values that can be retrieved together. Tuples are frozen by default, and do not need to be specified with the keyword frozen.

cURL command (/v2)
Result

curl -s -L -X POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
   "name": "current_country",
   "typeDefinition": "tuple<text, date, date>"
}'

{"name":"current_country"}

User-defined type (UDT)

User-defined types (UDTs) are a useful feature that allows you to add a custom data type consisting of two or more associated attributes. UDTs must currently be created in Cassandra Query Language (CQL) or GraphQL before specifying for a column in a table definition with the REST API. Once the UDT exists in the keyspace as a resource, you can add a column that specifies the data type as udt.

First let’s look at how to create a UDT with either CQL or GraphQL.

GraphQL mutation
GraphQL cURL command
CQL (execute in cqlsh)

# create a user-defined type (UDT)
mutation createAddressUDT {
  createType(
    keyspaceName: "users_keyspace"
    typeName: "address_type"
    fields: [
      { name: "street", type: { basic: TEXT } }
      { name: "city", type: { basic: TEXT } }
      { name: "state", type: { basic: TEXT } }
      { name: "zip", type: { basic: TEXT } }
    ]
  )
}

curl -sL --request POST --url https://$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 createAddressUDT {\n  createType(\n    keyspaceName: \"'$KEYSPACE_NAME'\"\n    typeName: \"address_type\"\n    fields: [\n      { name: \"street\", type: { basic: TEXT } }\n      { name: \"city\", type: { basic: TEXT } }\n      { name: \"state\", type: { basic: TEXT } }\n      { name: \"zip\", type: { basic: TEXT } }\n    ]\n  )\n}","variables":{}}'

CREATE TYPE IF NOT EXISTS users_keyspace.address_type (
  street text,
  city text,
  state text,
  zip text
);

Now we can add a UDT column to our table:

cURL command (/v2)
Result

curl -s -L -X POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H  "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
   "name": "address",
   "typeDefinition": "address_type"
}'

{ "name": "address" }

Checking column existence

To check if a column exists, execute a REST API query with cURL to find all the columns:

cURL command (/v2)
Result

curl -s -L \
-X GET https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json"

{
  "data": [
    {
      "name": "firstname",
      "typeDefinition": "varchar",
      "static": false
    },
    {
      "name": "lastname",
      "typeDefinition": "varchar",
      "static": false
    },
    {
      "name": "address",
      "typeDefinition": "address_type",
      "static": false
    },
    {
      "name": "current_country",
      "typeDefinition": "frozen<tuple<varchar, date, date>>",
      "static": false
    },
    {
      "name": "email",
      "typeDefinition": "varchar",
      "static": false
    },
    {
      "name": "evaluations",
      "typeDefinition": "map<int, varchar>",
      "static": false
    },
    {
      "name": "favorite_books",
      "typeDefinition": "set<varchar>",
      "static": false
    },
    {
      "name": "favorite_color",
      "typeDefinition": "varchar",
      "static": false
    },
    {
      "name": "top_three_tv_shows",
      "typeDefinition": "list<varchar>",
      "static": false
    }
  ]
}

To get a particular column, specify the column in the URL:

cURL command (/v2)
Result

curl -s -L \
-X GET https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns/email \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

{
  "data":{
    "name":"email",
    "typeDefinition":"varchar",
    "static":false
  }
}