CODEWITHSUNDEEP
javagoogle-cloud-datastoregql
Andreas Lundgren
Andreas Lundgren

Reputation: 12545

How to filter Google Content Store data based on a property that is an array using Java library

I have a Datastore where one property contains an array of strings. Each Entity can have a different size of that array. I want to make a query and filter upon the content of the array in each Entity.

I currently generate a set of filters, one for each required string in the array. I'm then building up a composite filter to be used in an EntityQuery. The filters are generated like this:

// arrayProperty is the name on the property in my Datastore that contains the array of strings.
List<PropertyFilter> pathFilters = Arrays.stream(new String[] {"a","b","c"})
    .map(s -> PropertyFilter.eq("arrayProperty", s))
    .collect(Collectors.toList());

This will however match both entities with arrayProperty = [a,b,c] and arrayProperty = [a,b,c,d].

Can I use Google Cloud Datastore Query from JAVA library in com.google.cloud.datastore to filter out entities whose property value is a particular array with exact the same elements (no more, no less and order independent)? Maybe by some sort of size filter or a completely different query/filter. Or could I use the GQL?

Upvotes: 2

Views: 869

Answers (1)

Sai Pullabhotla
Sai Pullabhotla

Reputation: 2207

You cannot do an equality filter (or exact match) on an Array Property. You can only query for entities that contain one or more individual elements. From your example data ([a, b, c] and [a, b, c, d]), following queries are possible:

  • arrayProperty="a" - returns both entities
  • arrayProperty="a" AND arrayProperty="b" - returns both entities
  • arrayProperty="a" AND arrayProperty="b" AND arrayProperty="c"- returns both entities
  • arrayProperty="a" AND arrayProperty="b" AND arrayProperty="c" AND arrayProperty="d" - returns just one entity
  • arrayProperty='x' - neither will match

You can expect the same behavior with the Query/Filter and GQL.

One workaround is to store the number of elements in the Array Property in a separate field, and then create a composite index on the Count property and the Array property. Then your query could be something like: 

WHERE count=3 AND arrayProperty="a" AND arrayProperty="b" AND arrayProperty="c"

This would return all entities whose arrayProperty has exactly 3 elements and the arrayProperty has a, b and c in any order.

You will also have to ensure the new count property stays in sync when entities are created/updated.

While this should give the desired result, your index size will be more.

You may also choose to dump the count into the original array field (as an Integer), assuming your primary data in the Array is of type String. With this approach, you can avoid the composite index.

Upvotes: 1

Related Questions