Index all fields or a key/value entry

[tobiasBora]

The documents I'm deading with can have properties (key/value) where the keys are provided by the users in real time while creating new data, like a name or email property. Since I don't know when loading the database all possible key names, I can't really create an index… hence I don't know how to efficiently search through them. And I'd like to avoid re-openning the database everytime a new field is added as this seems fairly inneficient and I'm worried that reloading the database might break the svelte UI listeners.

Any idea? Is the only approach to create two tables, one for the objects and known entries, and one that maps object id, key, and value, with a compound index on id + key?

[dfahlander]

You can have a mutliEntry indexed property (maybe 'tags' or 'indexedValues'. Then you can choose at runtime which fields to index by storing them also there. A search query is also simpler as it doesn't need to combine multiple properties.

[tobiasBora]

@dfahlander Thanks, but I'm confused, my understanding of multiEntry is that it behaves as a list of entries rather than a dictionary. For instance, is it possible with multiEntry to query all items where email is foo@bar.com? Would you store it like ["email", "foo@bar.com"]

[dfahlander]

You could take one of two approaches.

1. Have the property name and its value in the multiEntry array:

{
  email: "foo@bar.com",
  phone: "xxxxxxx".
  indexedValues: [
    ["email", "foo@bar.com"],
    ["phone", "xxxxxxx"],
    ...
  ]
}

Using this approach you would serach like this:

db.table
  .where('indexedValues')
  .equals(["email", "foo@bar.com"])
  .toArray()

2. Store only the values and let IndexedDB filter on the values. Then filter the rest manually. The upside with this is that you can do fuzzy searches on all fields in one query. The majority of filtering would happen in indexedDB still - and that's the key importance for optimization.

{
  email: "foo@bar.com",
  phone: "xxxxxxx".
  indexedValues: ["foo@bar.com", "xxxxxxxx"],
}

To query:

db.table
  .where({indexedValues: 'foo@bar.com'})
  .filter(x => x.email === 'foo@bar.com')
  .toArray()

[tobiasBora]

Thanks, nice trick I forgot one could query it with an array.

[tobiasBora]

But I guess this can't be adapted to stuff like "select all people whose age is above XX" right? Only the third method that creates a new table object/key/value seems to handle more complex queries. EDIT: or maybe one can actually use .between to create more advanced filter also on multiEntry arrays… need to try. Curious also to see how fast it is compared to having two tables.

[dfahlander]

Well, if storing entries of [field, value], that would actually be possible with between as you say. Haven't thought about it :)