How can I index child object properties in an array using ydn-db-fulltext?

Question

I'm using Ydn-Db-Fulltext to allow users to search a local database of contacts in an HTML5 app. So far, when it comes to searching for names of people, it works great, is smart, and returns results instantly.

Here's an example of a contact object that contains an array of contact Methods:

{
   "source": "COMPANY",
   "ownerPin": "12345",
   "name": "brian wilkins",
   "dateUpdated": "2014-03-18T14:41:05.217Z",
   "dateAdded": "2014-03-18T14:41:05.217Z",
   "isFavorite": false,
   "socialId": "54321",
   "id": "1",
   "deleted": false,
   "edited": false,
   "favorite": false,
   "contactMethods": [
    {
     "id": "4321",
     "contactKey": "12321",
     "contactId": "1",
     "value": "brian.wilkins@geemail.com",
     "kind": "email",
     "ownerPin": "12345",
     "isPrimary": false
    },
    {
     "id": "5432",
     "contactKey": "2",
     "contactId": "1",
     "kind": "phone",
     "ownerPin": "12345",
     "isPrimary": false
    },
    {
     "id": "23",
     "contactKey": "333",
     "contactId": "1",
     "value": "112345",
     "kind": "extension",
     "ownerPin": "12345",
     "isPrimary": false
    }
   ]
  }

To create the index on the "name" property, I setup the fullTextCatalog as follows:

fullTextCatalogs: [{
      name: 'name',
      lang: 'en',
      sources: [
        {
          storeName: 'contacts',
          keyPath: 'id',
          weight: 1.0
        }, {
          storeName: 'contacts',
          keyPath: 'name',
          weight: 0.5
        }
      ]
    }],
    stores: [
      {
        name: 'contacts',
        keyPath: 'id',
        autoIncrement: true
      }
    ]
  };
  this.db = new ydn.db.Storage('thedatabase', db_schema);

I can search by name or by id (the key) and get a list of contacts that match. Little appears to be stored in memory. Every search queries the local backing indexedDB database.

The challenge is that I also want to be able to search based on email address and extension, which are stored in the contactMethods property inside an array of contactMethods. The "value" property is where we store the email address and/or extension depending on the contactMethod type.

I tried adding contactMethods as a secondary searchable object store, but this resulted in searches for "Brian" returning two results, both the contact containing the name, and the contactMethod containing the email address. Ideally, I'd want to take the contactId (foreign key to the contact) and use it to pull the actual contact object, but it seems like this could create very expensive overhead and negate the benefits of this great search tool.

Is there a way to index object properties that are not at the parent level? How can I approach this in a way that would scale and not eat up all of the resources?

  this.db.get(entry.storeName, entry.primaryKey).done(function(x) {
    this.textContent += ' [Full name: ' + x.name + ']';  // this is in the contact
    this.textContent += ' [email: ' + x.value + ']';     // but this is in the contactMethod
  }, span);

Answer 1

Is there a way to index object properties that are not at the parent level?

keyPath can refer to deep object property by using dotted notation. For example, you could specify contactMethods.value to index email, but unfortunately it does not work with array value - as in this case.

So, obvious choice is keeping contactMethods record in separate object store using parent-child relationship. Since ydn-db currently does not support embedded attribute in the schema, you will have to load all child records when loading parent object.

Alternatively, IndexedDB v2 may have virtual index generated by a function expression. You can use in ydn-db by generator in index schema, for example:

 stores: [
   {
     name: 'contacts',
     keyPath: 'id',
     autoIncrement: true,
     indexes: [{
       name: '$emails',
       multiEntry: true,
       generator: function(record) {
         return record.contactMethods.map(function(x) {return x.value};
       })
     }]
   }
 ]

One thing to note though, the generated field $emails will appear when you load the data. It likely will be removed from the record so as to match with v2 spec.

We are using this generator index heavily in multiple projects, so I will fix bug.

Indexing id and email address in full text search is convenient, but does not make sense because phonetic base full text search will be index them as it is without normalization.