CODEWITHSUNDEEP
javascriptfirebasegoogle-cloud-firestore
RyanM
RyanM

Reputation: 4594

In Firestore, how can you do a compound query involving a key in a map without creating an index for every key?

In Firestore, how can you do a compound query involving a key in a map without creating an index for every key?

For example, consider a collection which holds blog posts, and each blog post has categories. 

Post {
    title: ..
    ...
    categories: {
        cats: true
        puppies: true
    }   
}

In order to query posts in a particular category in a paginated way, we would do something like this:

let query = db.collection(`/posts`)
    .where(`categories.${categoryId}`, '==', true)
    .orderBy('createdAt')
    .startAfter(lastDate)
    .limit(5);

But it seems that this would require a composite index (categories.<categoryId> and createdAt) for every single category. Is there any way around this?

In my case, it isn't feasible to create composite indices for every category since categories are user-generated, and could easily exceed 200 (the limit for composite indices in Firestore).

Upvotes: 42

Views: 30417

Answers (5)

Jonathan
Jonathan

Reputation: 4709

Sorting on multiple where clauses is possible without creating on-the-fly indexes if you create a compound index on the doc id, then sort by it.

posts/field__2ls2kdlsk2
posts/field__3ksl2fjes2
posts/field__j23kslewsl

and your posts doc would look like ${field}__${createId()}:

let query = db.collection(`/posts`)
    .where(`categories.${categoryId}`, '==', true)
    .where(`categories.${categoryID2}`, '==', true)
    .orderBy(firebase.firestore.FieldPath.documentId())
    .startAfter(docID)
    .limit(5);

J

Upvotes: 1

wonsuc
wonsuc

Reputation: 3625

Now Firestore allows the array-contains operator.
If you want to filter documents which contain specific value, try this.

First, change Map field to Array field.

Post {
    title: ..
    ...
    categories: [
        cats,
        puppies
    ]
}

Second, use array-contains and orderBy for each different fields.

let query = db.collection(`/posts`)
    .where('categories', 'array-contains', 'cats')
    .orderBy('createdAt')
    .startAfter(lastDate)
    .limit(5);

You can check the official document about array-contains operator from here.

Upvotes: 7

abraham
abraham

Reputation: 47893

This is doable by setting the value of each category to what you want to sort on. Firestore has a guide that covers this.

Post {
    title: ..
    ...
    categories: {
        cats: createdAt
        puppies: createdAt
    }   
}

let query = db.collection(`/posts`)
    .where(`categories.${categoryId}`, '>', 0)
    .orderBy(`categories.${categoryId}`)
    .startAfter(lastDate)
    .limit(5);

Upvotes: 31

Ronnie Smith
Ronnie Smith

Reputation: 18595

Try restructuring your data store. Firebase documentation is very helpful here.

Query limitations

Cloud Firestore does not support the following types of queries:

  • Queries with range filters on different fields, as described in the previous section.
  • Single queries across multiple collections or subcollections. Each query runs against a single collection of documents. For more information about how your data structure affects your queries, see Choose a Data Structure.
  • Queries of individual array members. You can, however, model and query array-like data using the techniques in Working with Arrays, Lists, and Sets.
  • Logical OR queries. In this case, you should create a separate query for each OR condition and merge the query results in your app.
  • Queries with a != clause. In this case, you should split the query into a greater-than query and a less-than query. For example, although the query clause where("age", "!=", "30") is not supported, you can get the same result set by combining two queries, one with the clause where("age", "<", "30") and one with the clause where("age", ">", 30).

Upvotes: 2

Frank van Puffelen
Frank van Puffelen

Reputation: 599706

As far as I know Firestore should auto-generate those indexes. From the documentation page on arrays, lists, and sets:

Consider this alternative data structure, where each category is the key in a map and all values are true:

// Sample document in the 'posts' collection
{
    title: "My great post",
    categories: {
        "technology": true,
        "opinion": true,
        "cats": true
    }
}

Now it's easy to query for all blog posts within a single category:

// Find all documents in the 'posts' collection that are
// in the 'cats' category.
db.collection('posts')
    .where('categories.cats', '==', true)
    .get()
    .then(() => {
        // ...
    });
)

This technique relies on the fact that Cloud Firestore creates built-in indexes for all document fields, even fields in a nested map.

While the lefthand-side of your where condition may be variable, that doesn't change the fact that these indexes should auto-generated (as far as I can see).

Upvotes: 21

Related Questions