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.

Prerequisites

Get your application token.
- Create a table in your keyspace
- Add rows to your table

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 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/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 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?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 WHERE clause to retrieve a row.

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 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?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 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?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 command (/v2)
Result

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 command (/v2)
Result

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 command (/v2)
Result

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:

cURL command (/v2)
Result

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

cURL command (/v2)
Result

# 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