Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Google Datastore partial string matching

I have read that there is the Search API. But it seems like this API does not exist for Node.JS. How can I partially match strings for querying entities without knowing the full name of the attribute? For example I want to select all users that start with a G. How can I accomplish this? Thank you for your help!

like image 645
ReasoN Avatar asked Jan 07 '17 18:01

ReasoN


2 Answers

While you cannot do a "true" partial string matching (i.e. contains) with Datastore, you can do a "begins with" query as described in this post:

Basically, create a composite inequality filter like this:

SELECT * FROM USER WHERE USERNAME >= 'G' AND USERNAME < 'G\ufffd'. 

Here, \ufffd is the last valid unicode character.

This would return all entities with their usernames starting with 'G'. You can use the same technique for matching multiple characters (e.g. >= 'JA' and < 'JA\ufffd').

Note that the string values/indexes in the Datastore are case sensitive, so you need an indexed property with all characters in either lower case or upper case so you can perform the search accordingly.

You can also mimic a word search like this -

Let's say you have a property named name that stores the following values:

John Doe
John Smith 
James Mike Murphy

To do a word search (find entities with word smith or james and murphy) - create another property (e.g. nameIndex) and store the words from name as an array property (note that all words are converted to lower case).

["john","doe"]
["john", "smith"]
["james", "mike" "murphy"]

Now you can do a word search using the nameIndex property -

SELECT * FROM Entity WHERE nameIndex = 'smith' 
SELECT * FROM Entity WHERE nameIndex = 'james' AND nameIndex='murphy'

Again, note that the nameIndex need to store the data in a fixed case (lower or upper) and your query parameters should use that case. Also, OR queries not supported unless the client library you are using supports it (typically done by running multiple queries).

This approach won't work if your property has more than 1500 bytes of data (limit for indexed properties)

Again, the proposed solutions are not replacement for full text search engines, rather a couple of tricks you could do with Datastore alone and may satisfy simple requirements.

like image 135
Sai Pullabhotla Avatar answered Nov 14 '22 22:11

Sai Pullabhotla


You can't perform partial match searches on the Datastore entities (let alone without knowing the name of the property/attribute). See Appengine Search API vs Datastore

And the Search API is, indeed, not available in the flexible environment (that includes Node.JS). A potential alternative is indicated the Search section in Migrating Services from the Standard Environment to the Flexible Environment:

The Search service is currently unavailable outside of the standard environment. You can host any full-text search database such as ElasticSearch on Google Compute Engine and access it from both the standard and flexible environments.

UPDATE:

Node.JS is currently available in the standard environment as well, see:

  • Now, you can deploy your Node.js app to App Engine standard environment
  • Google App Engine Node.js Standard Environment Documentation
like image 45
Dan Cornilescu Avatar answered Nov 14 '22 21:11

Dan Cornilescu