Untitled :: DataStax Astra DB Classic Documentation

Read data

Let’s check that the data was inserted. Send a GET request to /v2/keyspaces/{keyspace_name}/{table_name}?where={searchPath} to retrieve the two users that were entered:

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/users?where=\{"firstname":\{"$in":\["Janesha","Mookie"\]\}\}' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
-H "Accept: application/json" \
   --data-urlencode 'where={
     "firstname": {"in": ["Janesha","Mookie"]}
   }'

{
  "count": 2,
  "data": [
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    },
    {
      "firstname": "Mookie",
      "evaluations": {},
      "top_three_tv_shows": [],
      "favorite color": "blue",
      "current_country": null,
      "email": "mookie.betts.new-email@email.com",
      "lastname": "Betts",
      "favorite_books": []
    }
  ]
}

This query uses $in to find the two users. The WHERE clause can be used with other valid search terms: $eq, $lt, $lte, $gt, $gte, $ne, and $exists, if applicable. The primary key of the table can be used in the WHERE clause, but non-primary key columns cannot be used unless indexed.

Send a GET request to /v2/keyspaces/{keyspace_name}/{table_name} to retrieve the row for Mookie using $eq:

cURL command (/v2)
Result

curl -s -L -X GET 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users' \
   -H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   --data-urlencode 'where={
     "firstname": {"$eq": "Mookie"}
   }'

{
  "count": 1,
  "data": [
    {
      "firstname": "Mookie",
      "evaluations": {},
      "top_three_tv_shows": [],
      "favorite color": "blue",
      "current_country": null,
      "email": "mookie.betts.new-email@email.com",
      "lastname": "Betts",
      "favorite_books": []
    }
  ]
}

If the CQL indexes exist, a multiple WHERE can be used:

Send a GET request to /v2/keyspaces/{keyspace_name}/{table_name} to retrieve the row for Janesha using $eq, and Emma using $contains:

cURL command (/v2)
Result

curl -s -L -G 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users' \
   -H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   --data-urlencode 'where={
      "firstname": {"$eq": "Janesha"},
      "favorite_books": {"$contains": "Emma"}
   }'

{
  "count": 1,
  "data": [
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    }
  ]
}

A primary key can be supplied to retrieve a row:

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/users/Mookie/Betts \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

{
  "count": 1,
  "data": [
    {
      "firstname": "Mookie",
      "evaluations": {},
      "top_three_tv_shows": [],
      "favorite color": "blue",
      "current_country": null,
      "email": "mookie.betts.new-email@email.com",
      "lastname": "Betts",
      "favorite_books": []
    }
  ]
}

Adding /rows instead of a WHERE clause or primary key returns all table rows.

Returning all rows in a large table can negatively impact your database. The page-size parameter limits the number of results returned, and is recommended for large tables. The pageState is useful for pagination of the results in queries.

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/users/rows \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

{
  "count": 2,
  "data": [
    {
      "firstname": "Mookie",
      "evaluations": {},
      "top_three_tv_shows": [],
      "favorite color": "blue",
      "current_country": null,
      "email": "mookie.betts.new-email@email.com",
      "lastname": "Betts",
      "favorite_books": []
    }, 
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    }
  ]
}

In this example both Mookie and Betts are supplied in the path. Mookie is the partition key firstname and Betts is the clustering key lastname. Together these keys form the primary key of a row. To retrieve any rows using the partition keys and clustering keys, the primary key can be part of the request, in order and separated by a forward slash, such as pk1/pk2/ck1/ck2/…. Each key must be included in order, but subsequent clustering keys do not have to be included. Note that the inclusion of clustering keys may return a range of rows.

To return only desired fields in a response object, use the fields query parameter:

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/users/Janesha/Doesha?fields=firstname,lastname,top_three_tv_shows \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json"

{
  "count": 1,
  "data": [
    {
      "firstname": "Janesha",
      "lastname": "Doesha",
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ]
    }
  ]
}

Collections, tuples, and UDTs

Some data types require special handling to read the data. Examples of set, list, map, tuple, and udt are shown, using the column schema created earlier. Because these columns are not part of the partition or clustering key, an index is required to read the data. Currently, for Cassandra, those indexes must be created with CQL. Here are the indexes created for use in the following examples:

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));

If you are using DataStax Enterprise, you may optionally use Storage-Attached Indexing (SAI):

CREATE CUSTOM INDEX books_idx 
  ON users_keyspace.users (VALUES(favorite_books)) 
  USING 'StorageAttachedIndex'
  WITH OPTIONS = ('case_sensitive': 'false');
CREATE CUSTOM INDEX tv_idx
  ON users_keyspace.users (VALUES(top_three_tv_shows))
  USING 'StorageAttachedIndex'
  WITH OPTIONS = ('case_sensitive': 'false');
CREATE CUSTOM INDEX eval_idx
  ON users_keyspace.users (KEYS(evaluations))
  USING 'StorageAttachedIndex'
  WITH OPTIONS = ('case_sensitive': 'false');

SAI does not support tuples. Searches using SAI do not support the operator $in.

SET:

cURL command (/v2)
Result

curl -s -L -G https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/users \
   -H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   --data-urlencode 'where={
     "firstname": {"$eq": "Janesha"},
     "lastname": {"$eq": "Doesha"},
     "favorite_books": {"$contains": "Emma"}
   }'

{
  "count": 1,
  "data": [
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    }
  ]
}

LIST:

cURL command (/v2)
Result

curl -s -L -G https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/users \
   -H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   --data-urlencode 'where={
     "firstname": {"$eq": "Janesha"},
     "lastname": {"$eq": "Doesha"},
     "top_three_tv_shows": {"$contains": "The Magicians"}
   }'

{
  "count": 1,
  "data": [
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    }
  ]
}

MAP:

cURL command (/v2)
Result

curl -s -L -G https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/users \
   -H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   --data-urlencode 'where={
     "firstname": {"$eq": "Janesha"},
     "lastname": {"$eq": "Doesha"},
     "evaluations": {"$containsKey": "2020"}
   }'

{
  "count": 1,
  "data": [
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    }
  ]
}

TUPLE:

cURL command (/v2)
Result

curl -s -G -L https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/users \
   -H  "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H  "Content-Type: application/json" \
   -H  "Accept: appication/json" \
   --data-urlencode 'where={
     "firstname": {"$eq": "Janesha"},
     "lastname": {"$eq": "Doesha"},
     "current_country": {"$eq": "( 'France', '2016-01-01', '2020-02-02' )"}
   }'

{
  "count": 1,
  "data": [
    {
      "firstname": "Janesha",
      "evaluations": 
        {
          "2019" : "okay",
          "2020" : "good"
        },
      "top_three_tv_shows": [
        "The Magicians",
        "The Librarians",
        "Agents of SHIELD"
      ],
      "favorite color": "grey",
      "current_country": [
        "France", 
        "2023-05-22T10:21:00.000Z",
        "2023-05-23T10:21:00.000Z"
      ],
      "email": "janesha.doesha@gmail.com",
      "lastname": "Doesha",
      "favorite_books": [
        "Emma",
        "The Color Purple"
      ]
    }
  ]
}

UDT:

cURL command (/v2)
Result

curl -s -L -G https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/users \
   -H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
   -H "Content-Type: application/json" \
   -H "Accept: application/json" \
   --data-urlencode 'where={
     "firstname": {"$eq": "Janesha"},
     "lastname": {"$eq": "Doesha"},
     "address": {"$eq": { "street": "1, Main St", "zip": 12345 }}
   }'

Example coming