Do an inequality and multiple "array-contains", or array-contains-like filters

Question

I'm trying to run a query with an inequality and multiple array-contains.

For simplicity, I simplified the data structure with imaginary data. Let's say in a collection called grocerie_packages I have many documents. I want to be able to proved searches based on the name of the package, the price of the package and the vegetables and fruits it contains. The current data structure is the following:

{
    price: 10,
    contained_fruits: ["apple", "pear", "grape"],
    contained_vegetables: ["tomato", "cucumber", "carrot"],
    package_name: ["f", "fa", "fam", "fami", "famil", "family", "family_", "family_s", "family_si", "family_siz", "family_size"]
}

At first, I used the following code to query based on the name:

.where("package_name", ">=", searchString)
.where("package_name", "<=", searchString + "\uf8ff");

But later, when I added the functionality of querying by price I ran into the issue of:

Invalid query. All where filters with an inequality (<, <=, >, or >=) must be on the same field.

I solved it by creating an array of the substrings of package_name. The code I used was something like this:

  .where("package_name", "array-contains", package_name_intput)
  .where("price", "<", max_price);

The problem I'm having is that now, since I did one array-contains to the query I can't query based on the contained vegetables and fruits. I don't want to sort on the client, since there are cases when I would return potentially thousands of items more than needed. I also can't combine the three array into one, because I only want to return a document if it matches all criteria of the query. For instance in a query where someone asks for apple and carrot I only want to return documents which contains both. If it was one array it would return the document even if only one item is found in the package. I only need to provide one item (like apple) search per vegetables and fruits.

What is the best way for me to run a query like this? I'm trying to find a solution where I don't fill the document with many-many flags just for querying porpuses. For instance:

{
   flags: ["apple_tomato_f", "apple_tomato_fa", "apple_tomato_fam", ] //and so on
 }

user_8061555 · Answer

Let me preface this by saying...

I just want to add that I wouldn't do this in a real project. Instead I would create an API on Cloud Run that performs multiple queries, structures the data, caches it temporarily and returns the results in whatever form I want.

However for the sake of flexing our pickled brains, here's how I would accomplish this with the following requirements:

Must query Firestore directly.
Only one query.
No client-side sorting.

Big brain time

First rethink your database structure. To accomplish all of this in a single query you need to store your data so that you can use all available single-use operators. To do that, store all of your fruits and vegetables as {[key: string]: boolean} like this:

{"grocerie_packages": {
        "abc1": {
            "price": 10,
            "tomato": true,
            "package_name": ["s", "si", "sin", "sing", "singl", "single", "single_s", "single_si", "single_siz", "single_size"]
        },
        "abc2": {
            "price": 20,
            "cucumber": true,
            "tomato": true,
            "package_name": ["f", "fa", "fam", "fami", "famil", "family", "family_", "family_s", "family_si", "family_siz", "family_size"]
        },
        "abc3": {
            "price": 30,
            "package_name": ["s", "si", "sin", "sing", "singl", "single", "single_s", "single_si", "single_siz", "single_size"]
        },
        "abc4": {
            "price": 40,
            "apple": true,
            "tomato": true,
            "package_name": ["f", "fa", "fam", "fami", "famil", "family", "family_", "family_s", "family_si", "family_siz", "family_size"]
        }
    }
}

That makes all of the following queries possible:

Package name contains "fam", has tomatoes and the price is < 40

    db.collection('grocerie_packages')
        .where('tomato', '==', true)
        .where('package_name', 'array-contains', 'fam')
        .where('price', '<', 40).get()
        .then((snap) => console.log("Results:", snap.docs.map(s => s.id).join(',')));

Results: abc2

Package name contains "fam", has tomatoes and the price is < 100

    db.collection('grocerie_packages')
        .where('tomato', '==', true)
        .where('package_name', 'array-contains', 'fam')
        .where('price', '<', 100).get()
        .then((snap) => console.log("Results:", snap.docs.map(s => s.id).join(',')));

Results: abc2,abc4

Package has cucumbers and costs > 10

    db.collection('grocerie_packages')
        .where('cucumber', '==', true)
        .where('price', '>', 10).get()
        .then((snap) => console.log("Results:", snap.docs.map(s => s.id).join(',')));

Results: abc2

Package name contains "sing", has tomatoes, cucumbers and apples and the price is > 100

    db.collection('grocerie_packages')
        .where('tomato', '==', true)
        .where('cucumber', '==', true)
        .where('apple', '==', true)
        .where('package_name', 'array-contains', 'sing')
        .where('price', '>', 100).get()
        .then((snap) => console.log("Results:", snap.docs.map(s => s.id).join(',')));

Results:

user_11423459 · Answer

I ended up ditching the inequality part of the query, by instead of letting the user give a specific price I only allow users to choose a price range, for instance:

Instead of setting the max price to $10, they can choose a range like $5-$10.

In production the solution is a little more complex, but for simplification this is the core idea.

So with the inequality and composite index problem out of the way, it was fairly simple to query with a new data structure. The new data structure:

{
    price: "$5-$10",
    contained_fruits: {
      peach: true,
      melon: true,
      cherry: true
    },
    contained_vegetables: {
        cucumber: true,
        tomato: true,
    },
    package_name: {
      b: true,
      bi: true,
      big: true, 
      big_: true,
      big_s: true,
      big_si: true,
      big_siz: true,
      big_size: true,
        
    }
}

Firebase lets multiple equality based queries, so this...:

.where("price", "==", price_input)
.where(`package_name.${package_name_intput}`, "==", true)
.where(`contained_vegetables.${vegetable_input}`, "==", true)
.where(`contained_fruits.${fruit_input}`, "==", true)

...works and is completely fine. A nice advantage of this is that I can let users search for multiple fruit and vegetable items per query, and I can manage the price based query fairly easily, although not as originally desired. I also don't have to bother with composite indexes, which is also nice.

That being said, if someone comes up with a viable solution to my original problem they are going to get bounty awarded.

Hope this helps, have a nice day!

user_14099883 · Answer

is it really impossible with your query to use the keyword OR in your queries?

for your multiple contains, use 'tomato' in fruit or 'tomato' in vegetables? A clause like this should handle both of your cases

It sounds to me like your problem really is that of boolean logic.

Do an inequality and multiple "array-contains", or array-contains-like filters

3 Answers