Google Datastore combine (union) multiple sets of entity results to achieve OR condition

user_2183592 0
Viewed 2037

I am working with NodeJS on Google App Engine with the Datastore database.

Due to the fact that Datastore does not have support the OR operator, I need to run multiple queries and combine the results.

I am planning to run multiple queries and then combine the results into a single array of entity objects. I have a single query working already.

Question: What is a reasonably efficient way to combine two (or more) sets of entities returned by Datastore including de-duplication? I believe this would be a "union" operation in terms of set theory.

Here is the basic query outline that will be run multiple times with some varying filters to achieve the OR conditions required.

  //Set requester username
  const requester = req.user.userName;
  //Create datastore query on Transfer Request kind table
  const task_history = datastore.createQuery('Task');
  //Set query conditions
  task_history.filter('requester', requester);
  //Run datastore query
  datastore.runQuery(task_history, function(err, entities) {
    if(err) {
      console.log('Task History JSON unable to return data results. Error message: ', err);
      return;
      //If query works and returns any entities
    } else if (entities[0]) {
      //Else if query works but does not return any entities return empty JSON response
      res.json(entities); //HOW TO COMBINE (UNION) MULTIPLE SETS OF ENTITIES EFFICIENTLY?
      return;
    }
  });

Here is my original post: Google Datastore filter with OR condition

node.js google-cloud-datastore google-app-engine
2 Answers

IMHO the most efficient way would be to use Keys-only queries in the 1st stage, then perform the combination of the keys obtained into a single list (including deduplication), followed by obtaining the entities simply by key lookup. From Projection queries:

Keys-only queries

A keys-only query (which is a type of projection query) returns just the keys of the result entities instead of the entities themselves, at lower latency and cost than retrieving entire entities.

It is often more economical to do a keys-only query first, and then fetch a subset of entities from the results, rather than executing a general query which may fetch more entities than you actually need.

Here's how to create a keys-only query:

const query = datastore.createQuery()
  .select('__key__')
  .limit(1);

This method addresses several problems you may encounter when trying to directly combine lists of entities obtained through regular, non-keys-only queries:

  • you can't de-duplicate properly because you can't tell the difference between different entities with identical values and the same entity appearing in multiply query results
  • comparing entities by property values can be tricky and is definitely slower/more computing expensive than comparing just entity keys
  • if you can't process all the results in a single request you're incurring unnecessary datastore costs for reading them without actually using them
  • it is much simpler to split processing of entities in multiple requests (via task queues, for example) when handling just entity keys

There are some disadvantages as well:

  • it may be a bit slower because you're going to the datastore twice: once for the keys and once to get the actual entities
  • you can't take advantage of getting just the properties you need via non-keys-only projection queries
user_44950810
Related