Querying for object key in Firestore

user_11718195 0
Viewed 951

I currently have a few issues with my Firestore querying technique. As per this stackoverflow post I made recently, Querying with two array with firestore security rules

The answer proposed to add the the "ids" into a object, with the key as the id, and the value simply being "true". I have completed this, and now my structure looks like so:

enter image description here

This leaves me with this query:

db.collection('Depots')
  .where(`products.${productId}`, '==', true)
  .where(`users.${userId}`, '==', true)
  .where('created', '>', 1585998560500)
  .orderBy('created', 'asc')
  .get();

This query leaves me with throwing an error, asking to create an index:

The query requires an index. You can create it here: ...

However, this tries to index the specific object key, i.e. QXooVYGBIFWKo6C so products.QXooVYGBIFWKo6C. Which is certianly not what I want, as this query changes, and can have an infinite number of possibilities, which means I would have to create another index for each key entry in order to query it.

Is there any way to solve this issue? I am assuming it needs to index this query due to the different operators used in the query, so I was wondering if there were any workarounds to this issue.

Thank you very much in advance.

google-cloud-firestore firebase-authentication firebase javascript
3 Answers

What you have here is a map field, for which indexes should usually be created automatically.

That indeed means that you'll have as many indexes as you have products, which means:

  1. You are limited in how many products you can have, as there is a maximum of 40,000 index entries per document.
  2. You pay more per document, as you pay for the storage of each index.

If these are not what you want, you'll have to switch back to your original model, with the query limitations you had there. There doesn't seem to be a solution that fits both of your requirements.

user_2091030

After our discussion in chat, this is the starting point I would suggest. Who knows what the end architecture would look like, but I think this or very close to this. You say that a user can exist in multiple depots at the same time and multiple depots can contain the same products, also at the same time. You also said that a depot can never have more than 40 users at a given time, so an array of 40 users would certainly not encroach on Firestore's document limit of 1,048,576 bytes.

[collection]
    <documentId>
        - field: value

[depots]

    <UUID>
        - depotId: string       "depot456"
        - productCount: num     5,000

    <UUID>
        - depotId: string       "depot789"
        - productCount: num     4,500


[products]

    <UUID>
        - productId: string     "lotion123"
        - depotId: string       "depot456"
        - users: [string]       ["user10", "user27", "user33"]

    <UUID>
        - productId: string     "lotion123"
        - depotId: string       "depot789"
        - users: [string]       ["user10", "user17", "user50"]


[users]

    <userId>
        - depots: [string]      ["depot456", "depot999"]

    <userId>
        - depots: [string]      ["depot333", "depot999"]

In NoSQL, storage is cheap and computation isn't so denormalize your data as much as you need to make your queries possible and efficient (fast and cheap).

To find all depots in a single query where user10 and lotion123 are both true, query the products collection where productId equals x and users array-contains y and collect the depotId values from those results. If you want to preserve the array-contains operation for something else, you'd have to denormalize your data further (replace the array for a single user). Or you could split this query into two separate queries.

With this model, when a user leaves a depot, get all products where users array-contains that user and remove that userId from the array. And when a user joins a depot, get all products where depotId equals x and append that userId to the array.

Watch this video, and others by Rick, to get a solid handle on NoSQL: https://www.youtube.com/watch?v=HaEPXoXVf2k

user_90867700

@danwillm If you are not sure about the number of users and products then your DB structure seems unfit for this situation because there are size and length limitations of the firestore document. You should rather create a separate collection for products and users i.e normalize your data and have a reference for the user in the product collection.

User : 
       {
          userId: documentId,
          name: John,
          ...otherInfo
       }

Product : 

       {
          productId: documentId,
          createdBy: userId,
          createdOn:date,
          productName:"exa",
          ...otherInfo
       }

This way you there will be the size of the document would be limited, i.e try avoiding using maps/arrays in firestore if you are not sure about there size. Also, in this case, the number of queries would be increased but you don't need many indexes in this case.

user_80874680
Related