Search Dexie table for a key in an array

Question

If I have the following record in an indexedDB/Dexie table:

{
  id: 1,
  name: "foo",
  children: [{id: 12, bar: "b"},
             {id: 14, bar: "c"}]
}

each child id will be unique. That is to say only 1 record will have a child with a given id of 14.

1. how do I set up an index on children such that I can search for the record which contains a child with an id of 14?
2. how do I then most efficiently search for multiple records containing a child with a key in anyOf([3, 7, 9])

Answer 1

IndexedDB allows MultiEntry indexing but only on simple arrays, and not arrays of objects. But you can workaround that by maintaining an array on the side with all the contained ids and index that property instead:

{
  id: 1,
  name: "foo",
  children: [{id: 12, bar: "b"},
             {id: 14, bar: "c"}],
  childrenIds: [12, 14]
}

// Dexie schema:
const db = new Dexie("db");
db.version(1).stores({
  table: "++id, *childrenIds"
});

MultiEntry indexes works across Chromium, Firefox and Safari. IE11 does not support it but the upcoming Chromium-based Edge browser will.

Alternate Approach

Another approach is to design your tables in a relational way:

const db = new Dexie("relDB");
db.version(1).stores({
  parents: "++id",
  children: "++id, parentId"
});

Parent objects:

{
  id: 1,
  name: "foo",
}

Child objects:

{id: 12, bar: "b", parentId: 1}
{id: 14, bar: "c", parentId: 1}

Then, to retrieve a parent based on a set of child ids [12, 14], use an anyOf() query on the children table:

const children = await db.children.where('id').anyOf([12, 14]).toArray();
const parentIds = new Set(children.map(child => child.parentId));
const parents = await db.parents.bulkGet([...parentIds]);

Conclusion

I've presented two optional approaches that would both be efficient solutions for you question. The first option is to add a simple array of child IDs and index that using MultiEntry index, The other option is to put the children in a separate table. If you'd go for the second option, you might also have some help from using the dexie-relationships addon to query children with parents included, or parents with children included.

Note

Reason I was indexing parentId on children was that it's a good practice to do that in relational schemas, in order to be able to query all children for a certain parent (which is not examplified here).