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 [email protected]? Would you store it like ["email", "[email protected]"]

[dfahlander]

You could take one of two approaches.

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

{
  email: "[email protected]",
  phone: "xxxxxxx".
  indexedValues: [
    ["email", "[email protected]"],
    ["phone", "xxxxxxx"],
    ...
  ]
}

Using this approach you would serach like this:

db.table
  .where('indexedValues')
  .equals(["email", "[email protected]"])
  .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: "[email protected]",
  phone: "xxxxxxx".
  indexedValues: ["[email protected]", "xxxxxxxx"],
}

To query:

db.table
  .where({indexedValues: '[email protected]'})
  .filter(x => x.email === '[email protected]')
  .toArray()