Projection query and indexes

[Lisa Guinn]

FYI, I have also posted this at

https://stackoverflow.com/questions/73263174/google-cloud-firestore-in-datastore-mode-projection-query-and-indexes

Duplicating text here:

I've read all the docs, but am struggling with this query in GCP Firestore in Datastore Mode:

SELECT id, title FROM `Book` WHERE author = 'Twain'

I've tried all the permutations of properties and indexes and where clauses that I can think of... What works:

SELECT id, title FROM `Book`

because I have created this index.yaml and built it with gcloud datastore indexes create index.yaml

indexes: 

- kind: Book 

  ancestor: no 

  properties: 

  - name: id 

  - name: title

I can also

SELECT * FROM `Book` WHERE author = 'Twain'

because author is an indexed property of the Book entity.

I've tried adding author to the index (in addition to title and id), but Datastore still complains "GQL Query error: Your Datastore does not have the composite index (developer-supplied) required for this query."

What am I missing? The entity is quite large, and I just want to retrieve a list of the titles to populate the web page, not every property! I also don't want to retrieve the entire set of entities, since obviously only a few of them have been written by a particular author.

[Lisa Guinn]

The question is also answered on StackOverflow by Jim Morrison, but recapping here for the community:

Bottom line: the property used in the where clause must also appear first in the index. (I didn't see this requirement in the documentation.)

so the correct index is

indexes: 

- kind: Book 

  ancestor: no 

  properties: 

  - name: author 

  - name: id 

  - name: title

  - name: cover

I added an additional property, cover, to the index so that I could also comment on something else that obscured my real problem. Assume that only a small number of books have a cover property (a link to an image in Cloud Storage).  Entities that are missing any property in the index, will not be indexed. (Makes sense if you think for a moment.) So only a small number of entities appear in the index.

So if I want to be sure that every book appears in this index (and the query results based on it), I need to be sure that every entity has all of these properties. It is okay if the values of the properties are blank or empty, but they must exist. This is described in the documentation, but I didn't think about how it affected my testing.