Firestore: Multiple 'array-contains'

Question

Update on this question

Firestore launched another feature similar to the in query, the array-contains-any query. This feature allows you to perform array-contains queries against multiple values at the same time.

Use the array-contains-any operator to combine up to 10 array-contains clauses on the same field with a logical OR. An array-contains-any query returns documents where the given field is an array that contains one or more of the comparison values:

ref = ref.where('node_sku' , 'array-contains-any' , list);

Firestore - Array contains any

Question

I am trying to filter data with multiple where() methods using the array-contains operator, however I am having the following error:

An error occured: Error: 3 INVALID_ARGUMENT: 
A maximum of 1 'ARRAY_CONTAINS' filter is allowed.

Basically, I have a collection containing the following docs:

product1 
  - node_sku ['sku1', 'sku2', 'sku3' ]

product2 
  - node_sku ['sku4', 'sku5', 'sku6' ]

Then I have my array with items I wish to find on the database:

const list = ['sku4', 'sku2']

I want to search for sku4 and sku2, if finds any returns the object.

My problem is that array-contains is not allowed to run more than once.

Here's the code I am using to filter:

const list = ['sku4', 'sku2'];


let database = firestore_db.collection('glasses');
let ref = database;

list.forEach( (val) => { 
    ref = ref.where('node_sku' , 'array-contains' , val);
});
ref.get() 
.then( (snapshot) => glassesRequestComplete(snapshot, req, response))
.catch( (error) => glassesRequestError(error, response) );

As you notice, my problem is that I am looping through my list array, however my ref is throwing an error since I am firing 'array-contains' more than once.

I have also tried to compare against the array:

ref.where('node_sku' , 'array-contains' , list);

which does not return any , I believe array-contains does not compare against arrays, only strings/numbers/booleans maybe?

Does anybody know a solution for that without having to query them individually?

Thank you

anothermh · Accepted Answer · 2019-09-08 23:28:53Z

34

One solution would be to store the 'tags' in an Object as its property names.

node_sku = {
  "sku1": true,
  "sku2": true,
  ...
  "skun": true
}

Then you can create the AND WHERE clauses like this:

list.forEach( (val) => { 
  ref = ref.where(`node_sku.${val}` , '==' , true);
});

This approach is an answer to the question "without having to query them individually". However, as soon as you want ordering and pagination, you will run into another invisible brick wall of compound indices.

edited Sep 8, 2019 at 23:28

anothermh

10.7k3 gold badges42 silver badges61 bronze badges

answered Oct 24, 2018 at 12:34

mrnagydavid

4794 silver badges6 bronze badges

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

Comments

Frank van Puffelen · Accepted Answer · 2018-10-16 14:52:19Z

14

As you've found you can only have a single array-contains operation in a query. The idea of allowing multiple is currently not supported, but could be valid. So I recommend you file a feature request.

The only solution I can think of for your use-case right now, is to also include the combinations. So if you want to allow testing for the existing of two SKUs, you explode the array to also contain each combination (merging keys in lexicographical order):

product1 
  - node_sku ['sku1', 'sku2', 'sku3', 'sku1_sku2', 'sku1_sku3', 'sku2_sku3' ]

For two that might still be reasonable, but the combinatory explosion will make it infeasible beyond that.

answered Oct 16, 2018 at 14:52

Frank van Puffelen

603k85 gold badges895 silver badges869 bronze badges

1 Comment

gugateider Over a year ago

My problem is that I have thousands of sku's, which will make impossible for me to create that since all the combinations would be just too long. I feel like every time I come with an issue with Firebase is like hitting a wall with my head. I'll make a request to them. You helpful as always! Thanks Frank

Dan Philip Bejoy · Accepted Answer · 2021-01-05 23:34:17Z

8

Firestore launched another feature similar to the in query, the array-contains-any query. This feature allows you to perform array-contains queries against multiple values at the same time.

ref = ref.where('node_sku' , 'array-contains-any' , list);

Firestore - Array contains any

answered Jan 5, 2021 at 23:34

Dan Philip Bejoy

4,4032 gold badges20 silver badges34 bronze badges

5 Comments

gugateider Over a year ago

Thank you for posting this. I've updated my question based on your link and comments.

Seddik Fredj Over a year ago

i think that solve the problem : val citiesRef = db.collection("cities") citiesRef.whereIn("country", listOf("USA", "Japan"))

Muzu Over a year ago

what if i want to find users that contain ONLY item1 AND item2? array contains will get all that contain EITHER 1 OR 2 right?

maganap Over a year ago

@Muzu We once had that problem and we solved it by making sure the array is sorted before being stored in Firestore (i.e, make sure ['item1', 'item2'] and not ['item2', 'item1'] is stored). Then you can query with where(field, '==', ['item1', 'item2']) to make sure those are the only items in the array (yes, looks horrible but it works)

Bawantha Over a year ago

This doesn't give AND

Steve Gaetjens · Accepted Answer · 2022-12-22 12:45:07Z

0

My solution is to pull back all the items using 'array-contains-any', then use lodash _.intersection() to find the items that match all the terms. It works for single term searching as well.

      const searchTerms = searchKey.split(" ");
      const searchTermCount = searchTerms.length;
      const productsRef = collection(this.db, "products");
      const termsQuery = query(productsRef,
            where("search", "array-contains-any", searchTerms),
            orderBy("popularity", "desc"),
            orderBy("name_lower"),
            limit(resultLimit));

        const snaps = await getDocs(termsQuery);   
        let products = [];
             
        snaps.forEach((doc) => {
          const p = doc.data();
          const allTerms = _.intersection(p.search, searchTerms).length === searchTermCount;
          if (allTerms)
            products.push(p);
      });

edited Dec 22, 2022 at 12:45

answered Dec 22, 2022 at 12:42

Steve Gaetjens

112 bronze badges

2 Comments

Steve Gaetjens Over a year ago

Not sure why I got downvoted. The 'array-contains-any' returns the OR results. My post-processing converts the result into an AND. If you search for ['sku4, 'sku2'], firebase will give you back results that have either. My post-process filters that down to only results with both.

moken Over a year ago

Edit your answer if including additional or clarifying information, don't add as a comment.

Philippe Vincent · Accepted Answer · 2021-03-19 16:31:05Z

-2

Use something like this :

ref.where("node_sku", "in", list).get()
.then((snapshot) => glassesRequestComplete(snapshot, req, response)).catch( (error) => glassesRequestError(error, response) );

answered Mar 19, 2021 at 16:31

Philippe Vincent

911 silver badge8 bronze badges

Collectives™ on Stack Overflow

Firestore: Multiple 'array-contains'

5 Answers 5

Comments

1 Comment

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related