How can I replicate an SQL EXISTS subquery in Firestore?

Fire­store doesn’t support joining or subqueries the way SQL does. In other words, you can’t write a query that “checks” if a school’s urn exists in another query result. Instead, you must restructure your data or perform multiple queries.

Here are two common approaches:

1. Denormalize Your Data:
   To support an efficient single-query read, you can store the fields that you need for filtering directly in the quadrant document. For example, include a copy of the minimal school data or at least the filtering fields (like urn, geohash, etc.) inside each quadrant’s “schools” array. This way you can run a query against the quadrant’s schools without needing to join in data from the separate schools collection.

2. Two-Step Query with the In Operator:
   If denormalization isn’t an option, one alternative is to perform two separate queries:

   • First, query the schools collection using your filters to get the list of matching urn values.
   • Then use those urns (by building an “in” query) to filter the quadrant’s schools.

   Note that Firestore’s “in” operator accepts a maximum of 10 values per query, so this approach works best when that list is small or can be batched.

For your use case where you want to load a small chunk of data for a table while displaying a map of all matching schools, denormalization is usually the preferred strategy. Having the necessary fields stored within the quadrant documents lets you filter on geohash and the school attributes in one go without additional round trips.

In summary, Firestore’s query capabilities require you to either redesign your data model or handle the join client-side (or via multiple queries) since direct SQL-like subqueries aren’t available.