RethinkDB: Getting all documents that contain a string in any field

Question

I want to perform a query that will return all the documents that contain a given string in ANY of their fields. For example, say I have a "users" table and I'm looking for all the documents that contain "john", the returned result can be:

[{"first_name": "jhon", "last_name": "watson"}, {"first_name": "elton", "last_name": "john"}, {"first_name": "sherlock", "last_name": "holmes", "best_friend": "john watson"}]

How do I do that in rethinkdb? javascript answer will do, python implementation will be better.

score 2 · Answer 1 · answered Jan 05 '14 at 20:24

Unfortunately this query is made harder by the fact that ReQL doesn't have a values function like python. However it does have a keys function so let's just use that to make a values function like so:

def values(doc):
    return doc.keys().map(lambda x: doc[x])

Now that we have that finding a document that contains a string in one of its keys is pretty easy:

def has_str(doc, str):
    return values(doc).map(match(str)).reduce(lambda x,y: x | y)

Finally we can put it all together:

r.table("users").filter(lambda doc: has_str(doc, str))

You could in theory do this all in one big query but I really like breaking up even moderately complicated queries. The really nice thing about this approach is that if it doesn't work each function has a pretty simple set of semantics so you can debug them individually.

score 2 · Answer 2 · answered May 11 '15 at 14:10

It looks like you can just coerce the entire document into a string, and then search on that:

r.db('database').table('table).filter(function(doc) {
  return doc.coerceTo('string').match('querystring');
});

This solution doesn't feel as slick as the other provided, but runs much faster for me and provides the same results so far.

mbroadst · Answer 3 · 2015-05-08T00:28:44.543

For anyone who has found themselves here trying to figure this out in javascript, this roughly translates to:

function values(doc) {
  return doc.keys().map(function(key) {
    return doc(key);
  });
}

function contains(doc, string) {
  return values(doc).map(function(val) {
    return r.branch(val.match(string), true, false);
  }).reduce(function(left, right) {
    return left.or(right);
  });
}

var query = r.db('database').table('table').filter(function(doc) {
  return contains(doc, "some string");
});

query.run().then(function(results) {
  console.log(results);
});

Improvements welcome!

RethinkDB: Getting all documents that contain a string in any field

3 Answers3