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

  1. Getting an application token

  2. Creating a table in your keyspace using GraphQL

  3. Adding rows to your table

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

  • graphQL command

  • cURL command

  • Result

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

  • graphQL command

  • cURL command

  • Result

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

  • graphQL command

  • cURL command

  • Result

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

  • graphQL command

  • cURL command

  • Result

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

  • graphQL command

  • cURL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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

  • graphQL command

  • Result

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