Google Datastore partial string matching

Question

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!

Armalda

Answer 1

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.

Oneness

post link is empty? – Hypervitaminosis

@Hypervitaminosis - Not sure what happened to that link, but this answer has all the relevant information that is needed. – Oneness

Here's the working link - #48286. Also, I tried the unicode approach, but didn't work for me. Perhaps it's disabled now. But what did work for me was - SELECT * FROM USER WHERE USERNAME >= 'G' AND USERNAME < 'Gz'. Adding the letter z at the end does the same trick. – Adna

Answer 2

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

Hamlett

Sorry if I didn't express me clearly. I know the property name, but thank you for your reply. I want to host my node.js code in compute/container engine so is that a problem? – Armalda

It is as compute/container engine is outside the standard GAE environment, thus you can't use the Search API. – Hamlett