Firebase Database Structure For Better Queries

There are two ways in which you can solve this. The first one would be to create a sub-collection under each pharmacy to hold all available drugs:

Firestore-root
  |
  --- pharmacies (collection)
        |
        --- $pharmacyId (document)
               |
               --- drugs (sub-collection)
                     |
                     --- $drugId
                           |
                           --- name: "Aspirin"
                           |
                           --- inStock: true

To get all pharmacies that have, for example, Aspirin in stock, a collection group query is needed. In Android, the query should look like this:

db.collectionGroup("drugs").whereEqualTo("name", "Aspirin").whereEqualTo("inStock", "true");

The second option that you have is to create a single top-level collection of drugs:

Firestore-root
  |
  --- drugs (collection)
        |
        --- $drugId (document)
              |
              --- name: "Aspirin"
              |
              --- inStock: true
              |
              --- pharmacyId: $pharmacyId

And create a simple query that looks like this:

db.collection("drugs").whereEqualTo("name", "Aspirin").whereEqualTo("inStock", "true");

So you can choose to work or one or the other according to the use-case of your app.