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