How to make compound queries on Firestore between two different documents?

Question

I have a project on Firebase Firestore and I want to execute a compound query between two documents under the same subcollection. I have already created the indexes that Firestore requires and the query executes perfectly if the fields are on the same document.

To exemplify my situation:

Take this structure:

- Users (root collection)
  - User (document)
    - userId
    - username
    - ...
    --- Personalization (subcollection)
        --- Alerts (document)
            - myTags (array of strings)
            - ...
        --- Location (document)
            - region (string)
            - ...

I want to perform a query that first verifies if the user region (/Users/{userId}/Personalization/Location) is equal to a reference value. If so, I want it to verify if the array myTags (/Users/{userId}/Personalization/Alerts) contains a certain tag.

This is the closest I could get so far:

db.collectionGroup('Personalization').where('region', '==', 'California').where('myTags', 'array-contains', 'Macbook')
    .get().then(querySnapshot => {
        if (querySnapshot.empty) {
            console.log('Result is empty');
        } else {
            console.log('Result found. ', querySnapshot.size, ' result(s).');
        }
        querySnapshot.forEach(doc => {
            console.log(doc.id, ' => ', doc.data());
        });
    }).catch(error => {
        console.error(error);
    });

This query works perfectly if I have all my fields under the same document, like:

    --- Personalization (subcollection)
        --- Alerts (document)
            - myTags (array of strings)
            - region (string)
            - ...

Also, I can make both queries work perfectly separately like this:

db.collectionGroup('Personalization').where('myTags', 'array-contains', 'Macbook').get().then(querySnapshot => {
    querySnapshot.forEach(element => {
        console.log('Search only by tag => ', element.id, ' => ', element.data());
    });

Is there a way I can make the query work while still using two different documents under the same subcollection or I have obligatorily use the same document to make a compound query? Maybe there's some configuration on the index or something like that as well that I don't know about since I only followed the error link Firebase gives you when you first try a compound query to create the index.

Additional information:

This code is being developed for deploy on Firebase Functions and it triggers every time there's an onWrite event on a certain collection I have.

Also, if someone knows some good examples of compound queries on Firestore I would appreciate it. I read the documentation already and saw a couple of videos from the Firebase team explaining how Firestore works, but I feel the lack of more complex samples to grasp how it works in practice.

Your query defined with `.where('region', '==', 'California').where('myTags', 'array-contains', 'Macbook')` will not return the two different documents. It will only return the documents that match the two `where` clauses. In other words it will return the docs that have "all the fields", as you have already experienced. You most probably need to adapt your data model. Why do you put these fields in two separate documents in a sub-collection and not in the parent user document? — Renaud Tarnec, Nov 02 '19 at 17:34
It's actually my first experience with Firestore so I still finding the best way to model the data, so that's open for changes. I did this separation because I don't want to retrieve all the data stored on the subcollections every time I retrieve a user. I have no problem adding one extra field (region) to the Alerts document though. — Leonardo Dias, Nov 02 '19 at 17:40
"I have no problem adding one extra field (regions) to the Alerts document though." -> that seems to be a solution! — Renaud Tarnec, Nov 02 '19 at 17:41
Yeah. I'm already implementing that on my code. The question is part of my curiosity as well, to know if I was doing something wrong or if I could improve it. Also, I wanted to know if this is the only way if I ever come across with this problem again. — Leonardo Dias, Nov 02 '19 at 17:43

score 2 · Answer 1 · answered Nov 02 '19 at 18:13

2

Firestore currently does not support logical OR type queries where you can provide more than one condition to match:

How to perform compound queries with logical OR in Cloud Firestore?

It only supports logical AND, as you've seen. When you provide two where clauses, the query will give you only documents that meet both conditions.

If you want to implement a logical OR for multiple conditions, you will have to perform a query for each condition separately, and merge the results in your client code. This means you will be doing two queries:

where('region', '==', 'California')
where('myTags', 'array-contains', 'Macbook')

Then waiting for both queries to finish and looking at the results of each one.

answered Nov 02 '19 at 18:13

Doug Stevenson

297,357
32
422
441

Actually, this question is different of the link you sent me. I don't want a logical OR operation, I want AND operation between two documents. Two different problems. – Leonardo Dias Nov 03 '19 at 02:00
1

I don't understand the problem then. Your original query is doing exactly that - a logcal AND that requires both conditions to be true in order to match a document. The way you're describing the result is exactly as I would expect in that case. Please try asking a new question, and explain how the result is different than what you expect given the data you're working with. – Doug Stevenson Nov 03 '19 at 06:29
Like I said in another comment, it was more a curiosity I had while making some code. I wanted to know if I could make a query on a subcollection with two conditions being met on two different documents inside that subcollection. What I need is actually the parent collection, not the document itself. But thanks to Renaud, I realize that is not possible cause the query returns one document only so both conditions have to be found on the same document. – Leonardo Dias Nov 03 '19 at 19:34
Yeah, what you're describing is called a logical OR query, even if that term doesn't make sense at first. – Doug Stevenson Nov 03 '19 at 19:41
Oh. Now I get it. Then I wasn't understanding the term. Thanks for your explanation! – Leonardo Dias Nov 03 '19 at 19:48

score 1 · Accepted Answer · answered Nov 02 '19 at 18:14

Your compound query defined with .where('region', '==', 'California').where('myTags', 'array-contains', 'Macbook') will not return the two different documents.

As explained in the documentation (link above) this query will only return the documents that match the two where clauses ("logical AND").

As explained in the same documentation:

Cloud Firestore does not support the following types of queries:

...

Logical OR queries. In this case, you should create a separate query for each OR condition and merge the query results in your app.

...

As discussed in the comments below your question, one solution is to add an extra regions field to the Alert document.

How to make compound queries on Firestore between two different documents?

2 Answers2

Linked