The proper way of adding an index in a nested firebase realtime database structure

Question

The structure of by database is as follows:

my-db
    |
    Users
        |
        CreatedAt: "SOME_VALUE"
        |
        Email: "SOME_EMAIL"
        |
        Mailboxes
                |
                445566
                    |
                    Address: "My address 1"
                    |
                    Status: Active
                |
                112233
                    |
                    Address: "My address 2"
                    |
                    Status: Active

What I am trying to do is to filter only the users who have an active mailbox with a specific id. I am using firebase cloud functions for that purpose and the way I do it is as follows:

db.ref('/Users')
    .orderByChild('mailboxes/' + mailboxId + '/status')
    .equalTo('Active')
    .once("value", function(snapshot){
      snapshot.forEach(function(childSnapshot){
        // GET SPECIFIC USER DATA FROM childSnapshot
      })
    })

It seems to be working fine so far, but I am getting a warning error in firebase functions log saying that:

@firebase/database: FIREBASE WARNING: Using an unspecified index. Your data will be downloaded and filtered on the client. Consider adding ".indexOn": "mailboxes/445566/status" at /Users to your security rules for better performance.

I have tried to add an index in the rules file:

{
  "rules": {
    ".read": true,
    ".write": true,
    "users": {
      "$userid": {
        "mailboxes": {
          "$mailboxid": {
            ".indexOn": ["status"]
            } 
        }
      }
    }
  }
}

but still no success. I am now wondering what is not correct here.

score 4 · Accepted Answer · answered Nov 14 '18 at 14:43

An index needs to be defined on the location where you run the query. So since you run your query on /Users, that's where the index needs to be defined. As the error message says:

{
  "rules": {
    ".read": true,
    ".write": true,
    "users": {
      ".indexOn": "mailboxes/445566/status"
    }
  }
}

But you'll immediately notice that this means you'll need an index for each UID, which is not feasible. The reason for this is that the Firebase Database can only query flat lists, it cannot query across multiple levels.

Your current data structure allows you to efficiently read all mailboxes for a given user. It does not however easily allow you to find all users for a specific mailbox status. To allow the latter, you'll need an additional data structure where you store a list of mailboxes (the entity you're querying), their status (the condition that you're filtering on) and their user (the result you want).

mailboxes: {
  "445566": {
    "Status": "Active",
    "user": "uid1"
  },
  "112233" {
    "Status": "Active",
    "user": "uid2"
  }
}

With the above additional structure, you can use this index:

"mailboxes": {
  ".indexOn": "Status"
}

Next you query this /mailboxes to get all mailboxes with a certain status, and then determine the unique users in there to display.

Also see:

So, you basically say that having my current data structure (kind of a schema) it is not possible to define an index rule and I need to re-structure it anyway? — user2128702, Nov 14 '18 at 19:34

The proper way of adding an index in a nested firebase realtime database structure

1 Answers1

Linked