Firestore composite index merging is documented in the Firebase docs. Unfortunately the docs do not specify explicitly in what situation composite indexes can be merged. We can assume the following conditions must be true for index merging to occur:

• all indexes must end with the same field in the same direction: star_rating ASC in the linked docs example

• in order to utilize index merging, the query must also order by this same field: .orderBy("star_rating") in the linkded docs example

If the above conditions are met, we should be able to filter our query based on any of the fields that have a composite index satisfying the above.

Unfortunately there seem to be cases where the merging randomly fails. I have constructed a minimal example below. The errors can be reproduced by just creating the indexes and running the queries, no data is actually necessary in the collection as Firestore throw any errors before examining the contents of the collection.

indexes.json
{
  "indexes": [
    {
      "collectionGroup": "test_employees",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "name", "order": "ASCENDING" },
        { "fieldPath": "age", "order": "ASCENDING" }
      ]
    },
    {
      "collectionGroup": "test_employees",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "job", "order": "ASCENDING" },
        { "fieldPath": "age", "order": "ASCENDING" }
      ]
    }
  ]
}

When performing various queries the merge the above two indexes, most work, but some constructed similarly randomly fail for no apparent reason:

// succeeds:
const query_2ages_2names = db.collection('test_employees')
  .where('age', 'in', [35, 42])
  .where('name', 'in', ['John', 'Bill'])
  .where('job', '==', 'designer')
  .orderBy('age', 'asc')

// succeeds:
const query_1age_2names = db.collection('test_employees')
  .where('age', 'in', [35])
  .where('name', 'in', ['John', 'Bill'])
  .where('job', '==', 'designer')
  .orderBy('age', 'asc')

// fails:
const query_2ages_1name = db.collection('test_employees')
  .where('age', 'in', [35, 42])
  .where('name', 'in', ['John'])
  .where('job', '==', 'designer')
  .orderBy('age', 'asc')

// succeeds:
const query_1age_1name = db.collection('test_employees')
  .where('age', 'in', [35])
  .where('name', 'in', ['John'])
  .where('job', '==', 'designer')
  .orderBy('age', 'asc')

The one that fails says it requires the (age ASC, job ASC, name ASC) composite index, i.e. it is failing to merge the two existing indexes. What is causing that specific query to fail when the others succeed, and how can we force index merging in this case?