How to perform firestore query where array-contains-all with order by

Question

I'd like to perform a Firestore query to return an ordered list of blogs where the blog has an array that contains all elements of a query array.

An example is blog data such as this:

{
  "cities": ["Tokyo", "Kyoto", "Osaka", "Nara"],
  "modified": 1645457445
}

{
  "cities": ["Prague", "Bratislava", "Budapest"],
  "modified": 1645450245
}

{
  "cities": ["Hiroshima", "Kyoto", "Tokyo"],
  "modified": 1645453845
}

I want to get a page of 20 blogs that contains both "Tokyo" and "Kyoto", ordered by the most recently modified (descending).

I have seen the following questions
Firestore query - array contains all
How to perform compound queries with logical AND on array in Cloud Firestore?

When applying the above suggestions so the blog data looks like this:

{
  "cities": {
    "Hiroshima": true,
    "Kyoto": true,
    "Tokyo": true
  },
  "modified": 1645453845
}

And using the query

    var db = admin.firestore();
    var query = db.collection("blogs")

    data.cities.forEach(tag => {
          query = query.where("cities." + tag, "==", true)
    })

    query = query.orderBy("modified", "desc");

    if (typeof data.modified !== "undefined") {
      query = query.startAfter(data.modified)
    }

    return query.limit(20).get()

Firestore errors and requests to create an index such as:

cities.Tokyo Ascending modified Descending

The suggested index is not suitable as the blogs can contain any city in the world so it isn't feasible to create an index for every city

How can I achieve this query in firestore?

Can you share your query? You'll need to create some indexes when ordering. If you just use where() to match cities and then order after fetching results, it might be easier. — Dharmaraj
– Dharmaraj, Commented Feb 21, 2022 at 15:56
@Dharmaraj, I've edited the question to include the mention of paging and added an example of my query. If I don't use order as part of the query, I can't use limit as I would need the full set for accurate ordering. This could result in a greater number of reads and an increased cost — thirstycoda
– thirstycoda, Commented Feb 21, 2022 at 16:13
Please check the duplicate to see how you can create an index. — Alex Mamo
– Alex Mamo, Commented Feb 21, 2022 at 17:53
@AlexMamo sorry but I don't understand how this question is a duplicate of the one you have suggested. The question you have linked this to does not have an array of values that the document must match. Also, the answer of just create a index is not feasible here when the values in the array can be any city in the world. I've edited the question to clarify that the index suggested by Firestore is not suitable. Please reopen this question — thirstycoda
– thirstycoda, Commented Feb 21, 2022 at 18:10
As long as you get that warning, an index is required, otherwise, it won't work. Besides that, please check the second duplicate where Frank provided a solution that solves such problems. Since you cannot create an index for each city, "you should consider augmenting your data structure to allow a reverse lookup.". — Alex Mamo
– Alex Mamo, Commented Feb 21, 2022 at 18:34

dsl400 · Accepted Answer · 2023-04-04 07:45:47Z

1

one viable solution is to create an index array that contains the elements together

{
  index: ['a', 'b', 'c', 'a b', 'a c', 'b c', 'a b c']
  or 
  index: ['a', 'b', 'c', 'ab', 'ac', 'bc', 'abc']
}

this document can be found by:

one of the categories
some of the categories
all of the categories

you have to sort the elements inside every index alphabetically before insertion and the elements inside the query

edited Apr 4, 2023 at 7:45

answered Apr 4, 2023 at 6:55

dsl400

4525 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vir us Over a year ago

This can work when the sizes of both the query array and the destination data array are controlled and rather small (like up to 100 in the data and 2-3 in the query). In general such index will scale like crazy - it's a sum of Cnk for all k=1..n. If you have 10 elements, the size of the full index is 2 * (10 + 45 + 120+ 210) + 252 + 1 = 1023 elements. Unless you limit the size of the query array to like 2 or 3 in which case the index for 10 elements is gonna be 55 and 175 elements respectively. Full index for 50 elements will go beyond 1Mb limit. Really need array-contains-all

Collectives™ on Stack Overflow

How to perform firestore query where array-contains-all with order by

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related