Retrieve rows from your table
Retrieve data from your tables using Astra DB’s REST API.
After creating a table in your keyspace and adding rows, you can retrieve data. Use the Get a row endpoint to retrieve rows in the table you created.
Read data
Read data without WHERE clause
Data is most easily read using the primary key, supplied in the path to retrieve a row:
curl -s -L -X GET https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/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",
"address": {
"street": "500 Central Ave",
"city": "Albuquerque",
"state": "New Mexico",
"zip": "54321"
},
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
],
"top_three_tv_shows": [
"Supergirl",
"The Magicians",
"Agents of SHIELD"
],
"favorite_color": "red",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"monthValue": 1,
"dayOfMonth": 1,
"dayOfWeek": "FRIDAY",
"era": "CE",
"dayOfYear": 1,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"monthValue": 2,
"dayOfMonth": 2,
"dayOfWeek": "SUNDAY",
"era": "CE",
"dayOfYear": 33,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
],
"email": "mookie.betts.new-email@gmail.com",
"lastname": "Betts",
"favorite_books": [
"Native Son",
"The Color Purple"
]
}
]
}
This method can only retrieve one row per call. Let’s look at other calls that can retrieve more than one row at a time.
Operator options for reading with a WHERE clause
Most data retrieval uses a WHERE
clause containing operators to designate a range of rows to return.
The operators available are:
-
eq (equal)
-
notEq (not equal) - limited use
-
gt (greater than)
-
gte (greater than or equal to)
-
lt (less than)
-
lte (less than or equal to)
-
in (within) - limited use
-
nin (not within) - limited use
-
contains (a map contains the specified value)
-
containsKey (a map contains the specified key)
-
containsEntry (a map contains the specified key:value pair)
The WHERE
clause can use any of these operators, as applicable to the data type defined.
The primary key of the table can be used in the WHERE
clause, but non-primary
key columns cannot be used unless indexed.
Retrieve data using $in
Send a GET
request to
/api/rest/v2/keyspaces/{keyspace_name}/{table_name}?where={searchPath}
to retrieve the two users
that were entered using the $in
operator:
curl -s -L -X GET 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users?where=\{"firstname":\{"$in":\["Janesha","Mookie"\]\}\}' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
{
"count": 2,
"data": [
{
"firstname": "Janesha",
"address": {
"street": "1 Main St",
"city": "San Francisco",
"state": "California",
"zip": "12345"
},
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
],
"top_three_tv_shows": [
"The Magicians",
"The Librarians",
"Agents of SHIELD"
],
"favorite_color": "grey",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"monthValue": 1,
"dayOfMonth": 1,
"dayOfWeek": "FRIDAY",
"dayOfYear": 1,
"era": "CE",
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"monthValue": 2,
"dayOfMonth": 2,
"dayOfWeek": "SUNDAY",
"dayOfYear": 33,
"era": "CE",
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
],
"email": "janesha.doesha@gmail.com",
"lastname": "Doesha",
"favorite_books": [
"Emma",
"The Color Purple"
]
},
{
"firstname": "Mookie",
"address": {
"street": "500 Central Ave",
"city": "Albuquerque",
"state": "New Mexico",
"zip": "54321"
},
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
],
"top_three_tv_shows": [
"Supergirl",
"The Magicians",
"Agents of SHIELD"
],
"favorite_color": "red",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"monthValue": 1,
"dayOfMonth": 1,
"dayOfWeek": "FRIDAY",
"era": "CE",
"dayOfYear": 1,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"monthValue": 2,
"dayOfMonth": 2,
"dayOfWeek": "SUNDAY",
"era": "CE",
"dayOfYear": 33,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
],
"email": "mookie.betts.new-email@gmail.com",
"lastname": "Betts",
"favorite_books": [
"Native Son",
"The Color Purple"
]
}
]
}
A query for table rows must include a primary key in the path or a |
Let’s explore some of the other valid operators.
Send a GET
request to /api/rest/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?where=\{"firstname":\{"$eq":"Mookie"\}\}' \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json"
{
"count": 1,
"data": [
{
"firstname": "Mookie",
"address": {
"street": "500 Central Ave",
"city": "Albuquerque",
"state": "New Mexico",
"zip": "54321"
},
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
],
"top_three_tv_shows": [
"Supergirl",
"The Magicians",
"Agents of SHIELD"
],
"favorite_color": "red",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"monthValue": 1,
"dayOfMonth": 1,
"dayOfWeek": "FRIDAY",
"era": "CE",
"dayOfYear": 1,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"monthValue": 2,
"dayOfMonth": 2,
"dayOfWeek": "SUNDAY",
"era": "CE",
"dayOfYear": 33,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
],
"email": "mookie.betts.new-email@gmail.com",
"lastname": "Betts",
"favorite_books": [
"Native Son",
"The Color Purple"
]
}
]
}
The next query uses two operators, $eq
and $contains
in a multiple-part WHERE
clause.
To query on the non-primary key, favorite_books
in this case, an
index
must be created prior to executing the query.
Send a GET
request to /api/rest/v2/keyspaces/{keyspace_name}/{table_name}
to retrieve the row for Janesha
using $eq
, and Emma
using $contains
:
curl -s -L -X GET 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users?where=\{"firstname":\{"$eq":"Janesha"\},"favorite_books":\{"$contains":"Emma"\}\}' \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json"
{
"count": 1,
"data": [
{
"firstname": "Janesha",
"address": {
"street": "1 Main St",
"city": "San Francisco",
"state": "California",
"zip": "12345"
},
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
],
"top_three_tv_shows": [
"The Magicians",
"The Librarians",
"Agents of SHIELD"
],
"favorite_color": "grey",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"monthValue": 1,
"dayOfMonth": 1,
"leapYear": true,
"dayOfWeek": "FRIDAY",
"dayOfYear": 1,
"era": "CE",
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"monthValue": 2,
"dayOfMonth": 2,
"leapYear": true,
"dayOfWeek": "SUNDAY",
"dayOfYear": 33,
"era": "CE",
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
],
"email": "janesha.doesha@gmail.com",
"lastname": "Doesha",
"favorite_books": [
"Emma",
"The Color Purple"
]
}
]
}
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.
The indexes can be created with
REST,
GraphQL, or
CQL.
Here are the indexes created for use in the following examples, in CQL:
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));
SET:
curl -s -L -G 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users?where=\{"favorite_books":\{"$contains":"Emma"\}\}&fields=firstname,favorite_books' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
{
"count": 1,
"data": [
{
"firstname": "Janesha",
"favorite_books": [
"Emma",
"The Color Purple"
]
}
]
}
Also, note the use of the fields
parameter to retrieve only the values of the
firstname
and favorite_books
.
LIST:
curl -s -L -G 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users?where=\{"top_three_tv_shows":\{"$contains":"The%20Magicians"\}\}&fields=firstname,top_three_tv_shows' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
{
"count": 2,
"data": [
{
"firstname": "Janesha",
"top_three_tv_shows": [
"The Magicians",
"The Librarians",
"Agents of SHIELD"
]
},
{
"firstname": "Mookie",
"top_three_tv_shows": [
"Supergirl",
"The Magicians",
"Agents of SHIELD"
]
}
]
}
MAP:
curl -s -L -G 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users?where=\{"evaluations":\{"$containsKey":"2020"\}\}&fields=firstname,evaluations' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
{
"count": 2,
"data": [
{
"firstname": "Janesha",
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
]
},
{
"firstname": "Mookie",
"evaluations": [
{
"key": 2019,
"value": "okay"
},
{
"key": 2020,
"value": "good"
}
]
}
]
}
TUPLE:
# current_country currently uses a v1 style query
curl -s -G -L 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users?where=\{"current_country":\{"$eq":"('France','2016-01-01','2020-02-02')"\}\}&fields=firstname,current_country' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
{
"count": 2,
"data": [
{
"firstname": "Janesha",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"dayOfWeek": "FRIDAY",
"dayOfYear": 1,
"era": "CE",
"monthValue": 1,
"dayOfMonth": 1,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"dayOfWeek": "SUNDAY",
"dayOfYear": 33,
"era": "CE",
"monthValue": 2,
"dayOfMonth": 2,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
]
},
{
"firstname": "Mookie",
"current_country": [
"France",
{
"year": 2016,
"month": "JANUARY",
"dayOfWeek": "FRIDAY",
"dayOfYear": 1,
"era": "CE",
"monthValue": 1,
"dayOfMonth": 1,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
},
{
"year": 2020,
"month": "FEBRUARY",
"dayOfWeek": "SUNDAY",
"dayOfYear": 33,
"era": "CE",
"monthValue": 2,
"dayOfMonth": 2,
"leapYear": true,
"chronology": {
"calendarType": "iso8601",
"id": "ISO"
}
}
]
}
]
}
UDT:
# CURRENTLY DOES NOT WORK
curl -s -L -G 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/keyspaces/users_keyspace/users/Janesha/Doesha?where=\{"address":\{"$eq":\{"street":"1%20Main%20St","city":"San%20Francisco","state":"California","zip":12345\}\}\}&fields=firstname,address' \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
Example coming