Issues with GQL query, Google Datastore. Error with multiple conditions and greater than and less than operators

Question

I am trying to query the Datastore, and my query looks like this:

SELECT *
FROM mydb
WHERE Latitude = "18.1" AND Number > "1"

It doesn't work though. I get this error in the Datastore query box:

GQL query error: Your Datastore does not have the composite index (developer-supplied) required for this query.

And this error when I run my code:

no matching index found. recommended index is:\n- kind: mydb\n properties:\n - name: Location\n - name: Number\n

Simple requests like this work:

SELECT *
FROM mydb
WHERE Number > "1" AND Number < "5"

I am only accessing a single column here maybe that is why?

Nope,

Then I tried a request like this:

SELECT *
FROM mydb
WHERE Latitude = "18.1" AND Number = "1"

This worked.

I tried to look up a solution, and I came across this page: https://cloud.google.com/datastore/docs/tools/indexconfig#Datastore_About_index_yaml

After going through that page, I gathered that I need an index.yaml file somewhere. It is supposed to go in a folder called WEB-INF. But I don't have this folder.

This is a little snippet of my code:

Query<Entity> query = Query
                .gqlQueryBuilder(Query.ResultType.ENTITY,
                        "SELECT * FROM " + kind + " WHERE Location = @location AND Number <= @number")
                .setBinding("number", "5").setBinding("location", "18.1").build();
QueryResults<Entity> results = datastore.run(query);

Whomp

Where is your appengine-web.xml located? – Pettifog

@Pettifog I don't have it anywhere, my program can deploy and run without it. – Whomp

Can you post the full directory structure of your application? – Pettifog

@Pettifog Sure, this is what my project is based on, github.com/GoogleCloudPlatform/java-docs-samples/tree/master/… I don't have -- test/java/com/google/appengine/sparkdemo as I did not need it. – Whomp

Answer 1

The error you get is because the query you're attempting requires Composite indexes which are not available by default. They must be specified within index.yaml.

The article Creating index files which is somewhat different than the one posted is specifically for Java applications running in the flexible environment.

There are 2 ways to create an index.yaml:

1. Manually using your favorite text editor following the rules and structure as prescribed in Index definitions.
2. Generate the file as you test locally. This can be done using the gcloud beta emulators datastore start command. You can also use the --data-dir <dir> option to specify where the generated index.yaml should be written.

Then, once you have index.yaml and the same directory as app.yaml, you can deploy it with gcloud preview app deploy index.yaml from that directory. This process is briefly documented in Deploying the index file.

I would also recommend reading Organizing yaml Configuration Files.

Pettifog

Thank you, I'll go ahead and try this, however there are two places that contain app.yaml files, one is in src/main/appengine and the other one is in target/appengine-staging. Which directory should I put index.yaml in? – Whomp

src/main/appengine as I assume where you are running gcloud preview app deploy – Pettifog

Thank you, I'll give this a shot and let you know how it goes. – Whomp

Hello again, I was able to upload the index file, and I can see it on Datastore, however, I am getting the same error in the GQL query box. – Whomp

It began working. Initially, I put in all the property names in index.yaml, but then I updated it and only added the properties that I needed to work with in the file and that got it to work. Thanks for your help, I appreciate it! :) – Whomp

Answer 2

If you are still having issues with this topic. Make sure that the properties you add on the index.html are exactly the ones you need. In my case, I was adding two numeric properties, as Datastore does not allow multiple inequality operators (greater than, etc) you can't query neither of both with that.

So I upload a new index with just one of them and it allow me to do the query.

Hope it helps in some way,

Cheers!

Ody