Untitled :: DataStax Astra DB Classic Documentation

Retrieve 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 unless indexed. 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
Result

# get one book using the primary key title with a value
query oneBook {
    book (value: {title:"Moby Dick"}) {
      values {
      	title
      	author
      }
    }
}

{
  "data": {
    "books": {
      "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
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
     }
   }
}

{
  "data": {
    "book": {
      "values": [
        {
          "title": "Catch-22",
          "author": "Joseph Heller"
        },
        {
          "title": "Moby Dick",
          "author": "Herman Melville"
        }
      ]
    }
  }
}

To display the contents of a UDT, notice the inclusion of addresses in the values displayed for this read query:

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

{
  "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
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
      }
     }
  }
}

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

Filter options for reading

It’s possible to customize the condition of each parameter with WHERE with the following arguments:

column: the GraphQL column name to which the condition applies
predicate: the conditional predicate to use

The filters available are:

Predicate

columns that can have condition applied

eq (equal)

partition key, clustering column, regular indexed column

notEq (not equal)

partition key, clustering column, regular indexed column; allowed in conditional updates, but not selects

in (within)

partition key, clustering column, regular indexed column

gt (greater than)

clustering column

gte (greater than or equal to)

clustering column

lt (less than)

clustering column

lte (less than or equal to)

clustering column

contains

regular indexed column that is a , set or list, and has an index target of VALUES

containsKey

map contains the specified key

containsEntry

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

{
  "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"
            }
          ]
        }
      ]
    }
  }
}