RethinkDB - Filter using Match() by value in same dataset and table

Question

So, since I'm too dumb obviously to figure this out myself, I'll ask you better folks here on SO instead.

Basically i have a datastructure that looks like the following:

....,
{
 "id": 12345

 ....

 "policy_subjects": [
   {
     "compiled":  "^(user|max|anonymous)$",
     "template":  "<user|max|anonymous>"
   },
   {
     "compiled":  "^max$",
     "template":  "max"
   }
 ]
....
}

compiled is a "compiled" regex

template is the same regex without regex-modifiers

---

What I want is to do a simple query in RethinkDB using the "compiled" value and matching that against a string, say "max".

Basically 

r.table("regex_policies").filter(function(policy_row) {
  return "max".match("(?i)"+policy_row("policy_subjects")("compiled"))
}

Is what i want to do (+case-insensitive search)

There are of course lots of policy_subjects in the database so in this example the result should be the whole dataset (1 result) that matches "max". Since "max" exists twice in this case and it matches both regexes (once would have been enough).

"foobar" would likewise in this example yield 0 results, since any of the compiled regexes does not match "foobar".

Does anyone know how to do this relatively simple query?

Answer 1

You definitely want to use r.expr here and I got this example to work:

r.expr([{
 "id": 12345,
 "policy_subjects": [
   {
     compiled:  "^(user|max|anonymous)$",
     template:  "<user|max|anonymous>"
   },
   {
     compiled:  "^max$",
     template:  "max"
   }
 ]
}]).merge(function(policy_row) {  
  return {
    "policy_subjects": policy_row("policy_subjects").filter(function(item){
            return r.expr("max").match(r.expr("(?i)").add(item("compiled"))).ne(null);
        })
  }
})

Changing max to something else that does not match, returns the document with no elements inside policy_subjects.

For example, changing max => to wat (my favorite test string of all time) looks like this:

.merge(function(policy_row) {  
  return {
    "policy_subjects": policy_row("policy_subjects").filter(function(item){
            return r.expr("wat").match(r.expr("(?i)").add(item("compiled"))).ne(null);
        })
  }
})

And results in this:

[
  {
    "id": 12345 ,
    "policy_subjects": [ ]
  }
]

I think your logic for reducing to the one policy_subject document you want might be a little subjective to your use case so I'm not sure what the right answer is but you can use .reduce(...) to just return the right-most value.