How do I specify my WHERE conditions while using readUsingIndex method of Google Spanner

Question

I have the following code snippets from Google Spanner docs

How do I specify WHERE conditions in this?

It appears to me as if we can do only a regular SELECT from this and WHERE conditions to be applied while iterating ResultSet.

static void readStoringIndex(DatabaseClient dbClient) {

  // We can read MarketingBudget also from the index since it stores a copy of MarketingBudget.

  try (ResultSet resultSet = dbClient

          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle2",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget"))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong(0),
          resultSet.getString(1),
          resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
    }
  }
}

Answer 1

When using the readUsingIndex method, you filter the rows you want to read by specifying one or more index key values that you want to read.

Assume that the index AlbumsByAlbumTitle2 contains the column AlbumTitle and you want to read the albums with titles 'Great album' and 'Not so great album'. Your call should then be like this:

client
    .singleUse()
    .readUsingIndex(
        "Albums",
        "AlbumsByAlbumTitle2",
        KeySet.newBuilder()
            .addKey(Key.of("Great album"))
            .addKey(Key.of("Not so great album"))
            .build(),
        Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget"));

A KeySet could also be a range (begin/end) or a prefix. Note that when using the method readUsingIndex, you are specifying that you want to use the secondary index for filtering rows. This means that you cannot filter on any other columns than the columns in the secondary index.