Retrieving rows in your table using GraphQL
Retrieve data from your tables using Astra’s GraphQL API.
After creating a table in your keyspace using GraphQL and adding rows using GraphQL, you can retrieve data.
Prerequisites
Read data
Let’s check that the data was inserted.
Now let’s search for a particular record using a WHERE
clause. The primary
key of the table can be used in the WHERE
clause, but non-primary key columns
cannot be used.
The following query, looking at the location
http://localhost:8080/graphql/library
will get both the title
and the author
for the specified book WHERE
title:"Moby Dick"
:
# get one book using the primary key title with a value
query oneBook {
book (value: {title:"Moby Dick"}) {
values {
title
author
}
}
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# get one book using the primary key title with a value\nquery oneBook {\n book (value: {title:\"Moby Dick\"}) {\n values {\n title\n author\n }\n }\n}","variables":{}}'
{
"data": {
"book": {
"values": [
{
"title": "Moby Dick",
"author": "Herman Melville"
}
]
}
}
}
To find multiple books, an addition to the WHERE
clause is required, to denote that
the list of titles desired is IN
a group:
# get 3 books using the primary keys with an "in" filter clause of the primary key title
query ThreeBooks {
book(filter: { title: { in: ["Native Son", "Moby Dick", "Catch-22"] } } ) {
values {
title
author
}
}
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"query ThreeBooks {\n book(filter: { title: { in: [\"Native Son\", \"Moby Dick\", \"Catch-22\"] } } ) {\n values {\n title\n author\n }\n }\n}","variables":{}}'
{
"data": {
"book": {
"values": [
{
"title": "Catch-22",
"author": "Joseph Heller"
},
{
"title": "Moby Dick",
"author": "Herman Melville"
},
{
"title": "Native Son",
"author": "Richard Wright"
}
]
}
}
}
To display the contents of a UDT, notice the inclusion of addresses
in the values displayed for this read query:
# query the author to see the UDT
query getReaderWithUDT{
reader(value: { name:"Allen Ginsberg" user_id: "e0ed81c3-0826-473e-be05-7de4b4592f64" }) {
values {
name
birthdate
addresses {
street
city
zip
}
}
}
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# query the author to see the UDT\nquery getReaderWithUDT{\n reader(value: { name:\"Allen Ginsberg\" user_id: \"e0ed81c3-0826-473e-be05-7de4b4592f64\" }) {\n values {\n name\n birthdate\n addresses {\n street\n city\n zip\n }\n }\n }\n}","variables":{}}'
{
"data": {
"reader": {
"values": [
{
"name": "Allen Ginsberg",
"birthdate": "1926-06-03",
"addresses": [
{
"street": "Haight St",
"city": "San Francisco",
"zip": "94016"
}
]
}
]
}
}
}
To display the contents of a map collection, notice the inclusion of earned
in the values displayed for this read query:
# query a badge record that has a MAP (earned) with only the partition key
query oneGoldBadge {
badge(value: { badge_type: "Gold" } ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
curl --location --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# query a badge record that has a MAP (earned) with only the partition key\nquery oneGoldBadge {\n badge(value: { badge_type: \"Gold\" } ) {\n values {\n badge_type\n badge_id\n earned {\n key\n value\n }\n }\n }\n","variables":{}}'
{
"data": {
"badge": {
"values": [
{
"badge_type": "Gold",
"badge_id": 100,
"earned": [
{
"key": "Writer",
"value": "2020-11-20"
}
]
}
]
}
}
}
Filter options for reading
The filters available are:
-
eq (equal)
-
notEq (not equal) - limited use in conditional statements
-
gt (greater than)
-
gte (greater than or equal to)
-
lt (less than)
-
lte (less than or equal to)
-
in (within)
-
contains (a map contains the specified value)
-
containsKey (a map contains the specified key)
-
containsEntry (a map contains the specified key:value pair)
Note that these can only be used with primary key columns, just like in Cassandra, unless indexing is created.
The next examples will query the same table, badge
, using a variety of filters to illustrate
the versatility of such filters.
The first example finds the record that has the partition key badge_type
equal to Gold
, and
the badge_id
equal to 100
:
# query a badge record that has a MAP (earned) with the partition key and the clustering key
query oneGold100Badge {
badge(filter: { badge_type: {eq:"Gold"} badge_id: {eq:100}} ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
curl --location --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# query a badge record that has a MAP (earned) with the partition key and the clustering key\nquery oneGold100Badge {\n badge(filter: { badge_type: {eq:\"Gold\"} badge_id: {eq:100}} ) {\n values {\n badge_type\n badge_id\n earned {\n key\n value\n }\n }\n }\n}","variables":{}}'
{
"data": {
"badge": {
"values": [
{
"badge_type": "Gold",
"badge_id": 100,
"earned": [
{
"key": "Writer",
"value": "2020-11-20"
}
]
}
]
}
}
}
Now if we use a different operator gt
with the same query, notice that the query will fail,
because no badge_id
greater than a value of 100 is found:
# query a badge record that has a MAP (earned) with the partition key and the clustering key
# filter badge_id: {gt:100 will fail}
query oneGold100BadgeFail {
badge(filter: { badge_type: {eq:"Gold"} badge_id: {gt:100}} ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
{
"data": {
"badge": {
"values": []
}
}
}
In order to use filters for any columns that are not part of the primary key, currently you need to use CQL to create a secondary index using the CQL shell. The next three examples show the CQL creation of an index in order to query a column that is a map collection.
In this example, an index is created on the keys of the map earned
, so the containsKey
filter can be used to query in GraphQL.
# query a badge record that has a MAP (earned) with the partition key, clustering key, and a MAP key
# Requires: CREATE INDEX badge_idx ON library.badge(KEYS(earned));
query oneWriterBadge {
badge(filter: { badge_type: {eq:"Gold"} badge_id: {eq:100} earned: { containsKey: "Writer"} } ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
{
"data": {
"badge": {
"values": [
{
"badge_type": "Gold",
"badge_id": 100,
"earned": [
{
"key": "Writer",
"value": "2020-11-20"
}
]
}
]
}
}
}
Because the index now exists, it is also possible to just filter based on the map key itself:
# query a badge record that has a MAP (earned) with only a MAP key
# CREATE INDEX badge_idx ON library.badge(KEYS(earned));
query oneWriterKeyBadge {
badge(filter: { earned: { containsKey: "Writer"} } ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
{
"data": {
"badge": {
"values": [
{
"badge_type": "Gold",
"badge_id": 100,
"earned": [
{
"key": "Writer",
"value": "2020-11-20"
}
]
}
]
}
}
}
In this next example, an index is created on the values of the map earned
, so the contains
filter can be used to query in GraphQL.
# query a badge record that has a MAP (earned) with only a MAP value
# Requires: CREATE INDEX badge2_idx ON library.badge(VALUES(earned));
query oneWriterValueBadge {
badge(filter: { earned: { contains: "2020-11-20"} } ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
{
"data": {
"badge": {
"values": [
{
"badge_type": "Gold",
"badge_id": 100,
"earned": [
{
"key": "Writer",
"value": "2020-11-20"
}
]
}
]
}
}
}
To make a complete set of filters, an index is created on the entries of the map earned
, so the containsEntry
filter can be used to query in GraphQL.
# query a badge record that has a MAP (earned) with only a MAP entry
# Requires: CREATE INDEX badge3_idx ON library.badge(ENTRIES(earned));
query oneWriterEntryBadge {
badge(filter: { earned: { containsEntry: {key:"Writer", value:"2020-11-20"}} } ) {
values {
badge_type
badge_id
earned {
key
value
}
}
}
}
{
"data": {
"badge": {
"values": [
{
"badge_type": "Gold",
"badge_id": 100,
"earned": [
{
"key": "Writer",
"value": "2020-11-20"
}
]
}
]
}
}
}
Limiting and paging results using query options
You can add options to the query to limit and add paging to the results.
Add the limit
option to set the maximum number of results returned by the query.
# get books using a limit of 10
query readBooksWithLimit {
book(options: {limit:10}) {
values {
title
author
}
}
}
{
"data": {
"book": {
"values": [
{
"title": "Catch-22",
"author": "Joseph Heller"
},
{
"title": "Moby Dick",
"author": "Herman Melville"
}
]
}
}
}
The query results can be paged by using the pageSize
and pageState
options.
Set pageSize
to the number of results returned at each step.
The default value of pageSize
is 100.
# get books using a pageSize of 2
query readBooksWithPageSize {
book( options: { pageSize: 2 } ) {
values {
title
author
}
pageState
}
}
{
"data": {
"book": {
"values": [
{
"title": "Catch-22",
"author": "Joseph Heller"
},
{
"title": "Moby Dick",
"author": "Herman Melville"
}
],
"pageState": "CU1vYnkgRGljaxEAD0hlcm1hbiBNZWx2aWxsZfB////98H////4="
}
}
}
The pageState
value is returned in the data of the last query.
Pass the value of pageState
from the previous query to retrieve the next page
in the results.
# get booksi using pageState
query readBooksNextPage {
book( options: {
pageSize: 2,
pageState: "CU1vYnkgRGljaxEAD0hlcm1hbiBNZWx2aWxsZfB//98H////4="
}
) {
values {
title
author
}
pageState
}
}
{
"data": {
"book": {
"values": [
{
"title": "Pride and Prejudice",
"author": "Jane Austen"
},
],
"pageState": null
}
}
}