Full Text Query with additional qualifiers

[Brendan Duddridge]

Hi,

I have a data model something like this:

{

dateCreated : some date,

formId : some form identifier

values : {

 fieldId-1-text : "Terminator 2: Judgement Day",

 fieldId-2-text : "Arnold Schwarzenegger",

 fieldId-3-note : "The Terminator is a cyborg from the future."

}

I'd like to be able to do a full text query in order to find the resulting documents. However, when the user searches for example "Terminator", I want to restrict that to the key "fieldId-1-text" (or fieldId-3-note depending on the user's intent) in my values dictionary. So if fieldId-1-text represents the Movie Title and fieldId-3-note represents the Movie Summary, the user may want to just search the Movie Summary field for the word "Terminator".

How can I make sure that documents would come back from the search that contained "Terminator" ONLY when it occurs in the value corresponding to the "fieldId-3-note" field?

Here's how I was thinking my map reduce block would look:

CBLView *fullTextView = [database viewNamed: @"fullText"];

[fullTextView setMapBlock: MAPBLOCK({

  if ([doc[@"type"] isEqualToString:@"TFFormEntry"]) {

     NSDictionary *valuesDict = doc[@"values"];

      NSArray *keys = valuesDict.allKeys;

      // get keys from all the forms and build up one big search index somehow.

     for (NSString *key in keys) {

       if (([key hasSuffix:@"text"] || [key hasSuffix:@"note"] || [key hasSuffix:@"email"] || [key hasSuffix:@"web_site"] || [key hasSuffix:@"location"] || [key hasSuffix:@"contact"])) {

           NSString *value = valuesDict[key];

          if ([NSString ts_isNotEmptyString:value]) {

            emit(CBLTextKey(value), doc);

         }

      }

   }

 }

}) reduceBlock: NULL version: @"1"];

Is it possible to add another column to the full text search index which would hold the "fieldId-1-text", "fieldId-2-text", and "fieldId-3-note", etc. keys so that I could restrict the results to just the ones for the keys that I'm looking for?

Thanks,

Brendan

[Jens Alfke]

On Jun 17, 2015, at 6:14 AM, Brendan Duddridge <bren...@gmail.com> wrote:

How can I make sure that documents would come back from the search that contained "Terminator" ONLY when it occurs in the value corresponding to the "fieldId-3-note" field?

Define a view that emits only the full text of that field.

If you have multiple fields you want to search this way, you’ll need a view/index for each one.

—Jens

[Brendan Duddridge]

Hi Jens,

Hmm... I guess then there could be hundreds of views created then. Some customers may have a couple hundred fields within a single form and then might have a few dozen forms.

Perhaps one alternative might be for me to prefix the value with the field identifier and then whenever I query, I could include the value they enter prefixed with the field identifier? So something like:

fld-3-note AND The AND Terminator

or something like that. I know the ANDs are implied. I just put them there for clarification. But this may work since every query would include the field identifier and that should then match along with the other parts of the search string. I'll probably have to make sure my identifier doesn't have those dashes in it because I know that FTS will treat those as negative queries. Although that also depends on how SQLite is compiled. I compile my own because I use SQLCipher.

Thanks,

Brendan

[Jens Alfke]

On Jun 17, 2015, at 4:29 PM, Brendan Duddridge <bren...@gmail.com> wrote:

Perhaps one alternative might be for me to prefix the value with the field identifier and then whenever I query, I could include the value they enter prefixed with the field identifier? So something like:

That’s a possibility, as long as you put a prefix or something on the field identifier to make it not match any real word.

Although that also depends on how SQLite is compiled. I compile my own because I use SQLCipher.

Yeah, and in general don’t rely on anything about SQLite, because in the future we’ll have a different storage engine.

—Jens

[Brendan Duddridge]

Will SQLite at least always be an option? Because I need SQLCipher for the encryption. Unless ForestDB will have encryption? Forgive me if it already does. Also FTS would definitely be useful. I think I read somewhere that ForestDB's implementation doesn't have all the capabilities of SQLite's FTS4? But perhaps it's good enough for what I need, which is basic AND, OR, and prefixed searches (e.g. Term* to return "Terminator"). Although being able to search for substrings would be a huge bonus, especially for Chinese, Japanese, Korean, etc. type languages.

Thanks!

Brendan

[Jens Alfke]

On Jun 18, 2015, at 8:12 AM, Brendan Duddridge <bren...@gmail.com> wrote:

Will SQLite at least always be an option? 

For a while. We’ll need to get encryption support in ForestDB before we can phase out SQLite.

—Jens