How to query a variable field and apply order without first creating an index?

Question

Edit: I simplified the question and examples, because this problem only applies as soon as you start using orderBy .

I have a user collection in which each user subscribes to the number of such variables:

 user { var1: true, var2: true, var2: true, metric: 10 } 

I am doing this because I need to request something like:

 async function getUsersForVar(varId) { const snapshot = await db.collection('users') .where(varId, '==', true) .get() ... } 

This works fine until I need to select a user group based on the metric:

 async function getTopUsersForVar(varId) { const snapshot = await db.collection('users') .where(varId, '==', true) .orderBy('metric') .limit(100) .get() ... } 

When you do this, Firestore will tell you that it needs to create an index specific to the varId used plus the metric. But since the elements are variables, I cannot know or index each combination in advance.

This document describes how you can encode a property for sorting by key value, but for me this is not an option, because metric changes over time.

Is it impossible?

It might be too simple to expect the database to be able to efficiently complete such a request if the metric changes.

The number of user documents returned from the request will be in thousands. Is there anything I can do to prevent all data requesting and client side sorting?

AFAIK Firestore will select the complete document for each request. This request will be executed several times per minute, so if I collect thousands of user documents every time I execute this request, there will be a lot of overhead.

If there is no other way, I should probably separate all the relevant data from the user into a separate model / root collection so that all other user data is not selected to make this choice.

Will it be different when using Firebase Realtime DB?

Answer 1

So the deal:

If you want to fulfill a query - inequality or equality - in one field you do not need an index. (Even if this field is a property in the map field, for example, your top example.)

If you want to execute an equality query for multiple fields, you also do not need a special index.

If you want to execute a query consisting of 1 or more queries for equality, followed by either a search for inequality, or “order by” in another field, then you need a special index. And you are currently limited to 125 different user indexes in the same database.

So, with that in mind, I’m not sure that you can execute exactly the query you are looking for without creating custom indexes for each of them.

One way to solve the problem would be to fulfill the request without ordering the results, and then order them on the client.

Or, as nshmura suggested, you can try another workaround to add the metric value to the variable you are requesting. Therefore, instead of structuring your data, for example:

 user { var1: true, var2: true, var3: true, metric: 10 } 

You might want to structure your data, for example:

 user { var1: 10, var2: 10, var3: 10, metric: 10 } 

Then you can do a search like

  const snapshot = await db.collection('users') .where(varId, '>', 0) .limit(100) .get() 

And it will be automatically sorted by this value. Regardless of whether it really works, your particular use case probably depends a lot.

Answer 2

This document describes similar issues.

As described above, you can do it by storing metric values ​​as shown below:

 user { var1: { metric1: 10, metric2: 1502144665 } var2: { metric1: 10, metric2: 1502144665 } var3: { metric1: 10, metric2: 1502144665 } metric1: 10 metric2: 1502144665 } 

Now you can query without any custom indexes, for example below:

 const column = `${varId}.${metric}`; const snapshot = await db.collection("users") .where(column, ">", 0) // check dose user subscribe varId .orderBy(column) .limit(100) .get()