Cannot query Firestore database on multiple values

Question

I have a database full with cars. The JSON format is this:

docId: {
        "countries": ["Netherlands"]
        "cities": ["Amsterdam"]
   }

I cannot query both arrays.

db.collection("EU-CARS")
    .whereArrayContains("countries", "Netherlands")
    .whereArrayContains("cities", "Amsterdam");

How to solve this?

Answer 1

As you already noticed, Firestore allow you to filter your cars using only one whereArrayContains() method call. If you will use more than one, an error like this will occur:

Caused by: java.lang.IllegalArgumentException: Invalid Query. Queries only support having a single array-contains filter.

There are two ways in which you can solve this. The first one would be to change the structure your car document like this:

docId
  |
  --- filters
       |
       --- "country": "Netherlands"
       |
       --- "city": "Amsterdam"

In this way you can filter your database using:

Query query = db.collection("EU-CARS").
    .whereEqualTo("filters.country", "Netherlands")
    .whereEqualTo("filters.city", "Amsterdam");

Chaining multiple whereEqualTo() methods is permitted in Firestore.

Or if you still want to use arrays, simply concatenate both names into a sigle one:

docId
  |
  --- "countries_cities": ["Netherlands_Amsterdam", "Netherlands_Utrecht"]

The corresponding query should look like this:

db.collection("EU-CARS")
    .whereArrayContains("countries_cities", "Netherlands_Amsterdam");

Edit:

If you have multiple countries/cities, then the structure of your document should look like this:

docId
  |
  --- filters
       |
       --- "Netherlands": true
       |
       --- "Amsterdam": true
       |
       --- "Utrecht": true

In this way you can filter your database using:

Query query = db.collection("EU-CARS").
    .whereEqualTo("filters.Netherlands", true)
    .whereEqualTo("filters.Amsterdam", true);