Perform joins with subqueries
Overview
Firestore Enterprise edition supports relational-style joins through correlated subqueries. Unlike many NoSQL databases that often require denormalizing data or performing multiple client-side requests, subqueries allow you to combine and aggregate data from related collections or subcollections directly on the server.
Subqueries are expressions that execute a nested pipeline for every document processed by the outer query. This enables complex data retrieval patterns, such as fetching a document alongside its related subcollection items or joining logically linked data across disparate root collections.
Concepts
This section introduces the core concepts behind using subqueries for performing joins in Pipeline operations.
Subqueries as expressions
A subquery is not a top-level stage; instead, it is an expressionthat
can be used in any stage that accepts expressions, such as select(...)
, add_fields(...)
, where(...)
, or sort(...)
.
Firestore supports three types of subqueries:
- Array Subqueries:Materialize the entire result set of the subquery as an array of documents.
- Scalar Subqueries:Evaluate to a single value, such as a count, an average, or a specific field from a related document.
-
subcollection(...)Subqueries:simplified joins for a one-to-many parent-child relation.
Scope and variables
When writing a join, the nested subquery often needs to reference fields
from the "outer" document (the parent). To bridge these scopes, you use the let(...)
stage (referred to as define(...)
in some
SDKs) to define variables in the parent scope that can then be referenced in the
subquery using the variable(...)
function.
Syntax
The following sections give an overview of the syntax for performing joins.
The let(...)
stage
The let(...)
stage (referred to as define(...)
in some
SDKs) is a non-filtering stage that explicitly brings data from the parent scope
into a named variable for use in subsequent nested scopes.
Array Subqueries
An Array subquery is a special case of expression subquery that materializes the
entire result set of the subquery into an array. If the subquery returns zero
rows, it evaluates to an empty array. It never returns a null
array. Such
queries are useful when the full results are required in the final result, such
as when materializing a nested or correlated collection.
Queries can filter, sort, & aggregate in the subquery to also reduce the amount
of data that needs to be fetched and returned to help reduce the cost of the
query. The order of the subquery is respected, meaning that a sort(...)
stage
in the subquery controls the order of results in the final array.
Use the toArrayExpression()
SDK wrapper to convert a query into an array.
Scalar Subqueries
Scalar subqueries are often used in a select(...)
or where(...)
stage as allow filtering or resulting the
result of a subquery without materializing the full query directly.
A scalar subquery which produce zero results will evaluate to null
itself,
while a subquery which evaluates to multiple elements will result in a runtime
error.
When a scalar subquery produces only a single field per-result, the field is elevated
to be the top-level result for the subquery. This is most commonly
seen when the subquery ends with a select(field("user_name"))
or aggregate(countAll().as("total"))
where the schema of the subquery is just a
single field. Otherwise when a subquery can produce multiple fields they are
wrapped in a map.
Use the toScalarExpression()
SDK wrapper to convert a query into a scalar
expression.
subcollection(...)
Subqueries
While offered as a stage, the subcollection(...)
input stage allows
performing joins over Firestore's hierarchical data model. In a
hierarchical model, queries often need to retrieve a document alongside data
from its own subcollections. While you can achieve this using a collection_group(...)
input stage followed
by a filter on the parent reference, subcollection(...)
provides a much more
concise syntax.
Other than the implicit join condition, this acts similarly to an array subquery, returning an empty result if no documents are matched, even if the nested collection does not exist.
It is fundamentally syntactic sugar: it automatically uses the __name__
of
the document in the outer scope as the join key to resolve the hierarchical
relationship. This makes it the preferred way to perform lookups across
collections linked in a parent-child relationship.
Examples
Example data
The following loads a set of test data to use in all following examples.
Node.js
// Load set of cities.
const
cities
=
collection
(
db
,
"cities"
);
await
setDoc
(
doc
(
cities
,
"SF"
),
{
name
:
"San Francisco"
,
state
:
"CA"
,
country
:
"USA"
,
});
await
setDoc
(
doc
(
cities
,
"LA"
),
{
name
:
"Los Angeles"
,
state
:
"CA"
,
country
:
"USA"
});
await
setDoc
(
doc
(
cities
,
"DC"
),
{
name
:
"Washington, D.C."
,
state
:
null
,
country
:
"USA"
});
await
setDoc
(
doc
(
cities
,
"TOK"
),
{
name
:
"Tokyo"
,
state
:
null
,
country
:
"Japan"
});
// Load restaurants in various cities.
const
sfRestaurants
=
collection
(
db
,
"cities"
,
"SF"
,
"restaurants"
);
const
laRestaurants
=
collection
(
db
,
"cities"
,
"LA"
,
"restaurants"
);
const
dcRestaurants
=
collection
(
db
,
"cities"
,
"DC"
,
"restaurants"
);
const
rest1
=
await
addDoc
(
sfRestaurants
,
{
name
:
"Golden Gate Pizza"
,
type
:
"pizza"
,
owner_id
:
"Mario Rossi"
});
const
rest2
=
await
addDoc
(
sfRestaurants
,
{
name
:
"Bay Area Burger"
,
type
:
"burger"
,
owner_id
:
"Sarah Jenkins"
});
const
rest3
=
await
addDoc
(
sfRestaurants
,
{
name
:
"Sunset Taco"
,
type
:
"mexican"
,
owner_id
:
"Edward"
});
const
rest4
=
await
addDoc
(
laRestaurants
,
{
name
:
"Hollywood Sushi"
,
type
:
"sushi"
,
owner_id
:
"Ken Kenji"
});
const
rest5
=
await
addDoc
(
laRestaurants
,
{
name
:
"Venice Pizza"
,
type
:
"pizza"
,
owner_id
:
"Luigi Romano"
});
const
rest6
=
await
addDoc
(
dcRestaurants
,
{
name
:
"Capitol Tacos"
,
type
:
"mexican"
,
owner_id
:
"Maria Garcia"
});
const
rest7
=
await
addDoc
(
dcRestaurants
,
{
name
:
"Georgetown Coffee"
,
type
:
"cafe"
,
owner_id
:
"David Kim"
});
// Load collection of reviews.
const
reviews
=
collection
(
db
,
"reviews"
);
await
addDoc
(
reviews
,
{
restaurant
:
rest1
,
rating
:
5
,
reviewer_id
"Alice"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest1
,
rating
:
4
,
reviewer_id
"Bob"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest2
,
rating
:
4
,
reviewer_id
"Charlie"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest3
,
rating
:
5
,
reviewer_id
"Diana"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest3
,
rating
:
4
,
reviewer_id
"Edward"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest3
,
rating
:
4
,
reviewer_id
"Fiona"
});
// rest4 has 0 reviews
await
addDoc
(
reviews
,
{
restaurant
:
rest5
,
rating
:
3
,
reviewer_id
"George"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest6
,
rating
:
5
,
reviewer_id
"Hannah"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest6
,
rating
:
4
,
reviewer_id
"Ian"
});
await
addDoc
(
reviews
,
{
restaurant
:
rest7
,
rating
:
5
,
reviewer_id
"Julia"
});
Lookup a Document in Another Collection
The following query on the reviews
collection group performs a lookup into
the restaurant
collection group using a primary key reference.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
define
(
field
(
"restaurant"
).
as
(
"restaurant_name"
))
.
addFields
(
db
.
pipeline
()
.
collectionGroup
(
"restaurant"
)
.
where
(
field
(
"__name__"
).
equal
(
variable
(
"restaurant_name"
)))
.
select
(
"name"
,
"type"
)
.
toScalarExpression
()
.
as
(
"restaurant"
)));
Response
{
rating: 5,
reviewer_id "Alice",
restaurant: { name: "Golden Gate Pizza", type: "pizza" }
},
{
rating: 4,
reviewer_id "Bob",
restaurant: { name: "Golden Gate Pizza", type: "pizza" }
},
{
rating: 4,
reviewer_id "Charlie",
restaurant: { name: "Bay Area Burger", type: "burger" }
},
{
rating: 5,
reviewer_id "Diana",
restaurant: { name: "Sunset Taco", type: "mexican" }
},
{
rating: 4,
reviewer_id "Edward",
restaurant: { name: "Sunset Taco", type: "mexican" }
},
{
rating: 4,
reviewer_id "Fiona",
restaurant: { name: "Sunset Taco", type: "mexican" }
},
{
rating: 3,
reviewer_id "George",
restaurant: { name: "Venice Pizza", type: "pizza" }
},
{
rating: 5,
reviewer_id "Hannah",
restaurant: { name: "Capitol Tacos", type: "mexican" }
},
{
rating: 4,
reviewer_id "Ian",
restaurant: { name: "Capitol Tacos", type: "mexican" }
},
{
rating: 5,
reviewer_id "Julia",
restaurant: { name: "Georgetown Coffee", type: "cafe" }
}
Combine Multiple Collections
The following query fetches all pizza places from the restaurants
collection
group, and uses an array subquery to fetch and embed their associated reviews
directly into the response.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"restaurants"
)
.
where
(
field
(
"type"
).
equal
(
"pizza"
))
.
define
(
field
(
"__name__"
).
as
(
"restaurant_name"
))
.
select
(
field
(
"name"
),
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
where
(
field
(
"restaurant"
).
equal
(
variable
(
"restaurant_name"
)))
.
select
(
"rating"
,
"reviewer_id"
)
.
toArrayExpression
()
.
as
(
"reviews"
)));
Response
{
name: "Golden Gate Pizza",
reviews: [
{ rating: 5, reviewer_id "Alice" },
{ rating: 4, reviewer_id "Bob" }
]
},
{
name: "Venice Pizza",
type: "pizza",
owner_id: "Luigi Romano",
reviews: [
{ rating: 3, reviewer_id "George" }
]
}
Aggregate Across Multiple Collections
The following query on the restaurants
collection group uses a correlated
subquery to get the average rating for each restaurant from the reviews
collection group.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"restaurants"
)
.
where
(
field
(
"type"
).
equal
(
"pizza"
))
.
define
(
field
(
"__name__"
).
as
(
"restaurant_name"
))
.
select
(
field
(
"name"
),
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
where
(
field
(
"restaurant"
).
equal
(
variable
(
"restaurant_name"
)))
.
aggregate
(
average
(
"rating"
).
as
(
"avg_rating"
))
.
toScalarExpression
()
.
as
(
"avg_rating"
)));
Response
{
name: "Golden Gate Pizza",
avg_rating: 4.5
},
{
name: "Venice Pizza",
avg_rating: 3.0
}
Top-N Per Group (Subquery with Limit)
The following query fetches all documents from the restaurants
collection
group, and uses a correlated subquery to fetch the top 2 highest-rated reviews
for each restaurant.
This ensures that the array of reviews does not grow too large and hits the query's memory limit.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"restaurants"
)
.
define
(
field
(
"__name__"
).
as
(
"restaurant_name"
))
.
select
(
field
(
"name"
),
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
where
(
field
(
"restaurant"
).
equal
(
variable
(
"restaurant_name"
)))
.
sort
(
field
(
"rating"
).
descending
())
.
limit
(
2
)
.
select
(
"rating"
,
"reviewer_id"
)
.
toArrayExpression
()
.
as
(
"top_reviews"
)));
Response
{
name: "Golden Gate Pizza",
top_reviews: [
{ rating: 5, reviewer_id "Alice" },
{ rating: 4, reviewer_id "Bob" }
]
},
{
name: "Bay Area Burger",
top_reviews: [
{ rating: 4, reviewer_id "Charlie" }
]
},
{
name: "Sunset Taco",
top_reviews: [
{ rating: 5, reviewer_id "Diana" },
{ rating: 4, reviewer_id "Edward" }
]
},
{
name: "Hollywood Sushi",
top_reviews: []
},
{
name: "Venice Pizza",
top_reviews: [
{ rating: 3, reviewer_id "George" }
]
},
{
name: "Capitol Tacos",
top_reviews: [
{ rating: 5, reviewer_id "Hannah" },
{ rating: 4, reviewer_id "Ian" }
]
},
{
name: "Georgetown Coffee",
top_reviews: [
{ rating: 5, reviewer_id "Julia" }
]
}
Join Subcollections
The following query scans the cities
collection and uses the subcollection(...)
stage to implicitly join
over documents from a nested collection to find the number of restaurants per
city.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collection
(
"cities"
)
.
addFields
(
subcollection
(
"restaurants"
)
.
toArrayExpression
()
.
length
()
.
as
(
"restaurant_count"
)));
Response
{
__name__: cities/SF,
name: "San Francisco",
state: "CA",
country: "USA",
restaurant_count: 3
},
{
__name__: cities/LA,
name: "Los Angeles",
state: "CA",
country: "USA",
restaurant_count: 2
},
{
__name__: cities/DC,
name: "Washington, D.C.",
state: null,
country: "USA",
restaurant_count: 2
},
{
__name__: cities/TOK,
name: "Tokyo",
state: null,
country: "Japan",
restaurant_count: 0
}
Express Multiple Join Conditions
The following query scans the restaurants
collection group and performs a
multi-field join with the reviews
collection group to find owners reviewing
their own restaurants.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"restaurants"
)
.
define
(
field
(
"owner_id"
),
field
(
"__name__"
))
.
where
(
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
where
(
field
(
"restaurant"
).
equal
(
variable
(
"__name__"
)))
.
where
(
field
(
"author"
).
equal
(
variable
(
"owner_id"
)))
.
aggregate
(
count
().
as
(
"c"
))
.
toScalarExpression
()
.
greaterThan
(
0
)));
Response
{
__name__: cities/SF/restaurants/X9An0HIlx29A9GPuRthS,
name: "Sunset Taco",
type: "mexican",
owner_id: "Edward"
}
Anti-Join ( NOT EXISTS
)
The following query scans the restaurants
collection group and finds all
restaurants that have no reviews yet.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"restaurants"
)
.
define
(
field
(
"__name__"
).
as
(
"restaurant_name"
))
.
where
(
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
where
(
field
(
"restaurant"
).
equal
(
variable
(
"restaurant_name"
)))
.
aggregate
(
count
().
as
(
"review_count"
))
.
toScalarExpression
()
.
equal
(
0
)));
Response
{
__name__: "cities/LA/restaurants/X9An0HIlx29A9GPuRthS",
name: "Hollywood Sushi",
type: "sushi",
owner_id: "Ken Kenji"
}
Subquery as Join
The following query flattens the relationship between each pizza place and its
reviews. By placing the subquery inside an unnest(...)
stage, the server duplicates the outer
restaurant document for each matching review, producing flat, joined documents
(similar to a SQL INNER JOIN
).
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collectionGroup
(
"restaurants"
)
.
where
(
field
(
"type"
).
equal
(
"pizza"
))
.
define
(
field
(
"__name__"
).
as
(
"restaurant_name"
))
.
unnest
(
db
.
pipeline
()
.
collectionGroup
(
"reviews"
)
.
where
(
field
(
"restaurant"
).
equal
(
variable
(
"restaurant_name"
)))
.
select
(
"rating"
,
"reviewer_id"
)
.
toArrayExpression
()
.
as
(
"review"
)));
Response
{
__name__: "cities/SF/restaurants/xU4pu8nFpnJDPZOwcSPP",
name: "Golden Gate Pizza",
type: "pizza",
owner_id: "Mario Rossi"
review: { rating: 5, reviewer_id "Alice" }
},
{
__name__: "cities/SF/restaurants/xU4pu8nFpnJDPZOwcSPP",
name: "Golden Gate Pizza",
type: "pizza",
owner_id: "Mario Rossi",
review: { rating: 4, reviewer_id "Bob" }
},
{
__name__: "cities/LA/restaurants/6CYntvNgbYzgaW652Gq1",
name: "Venice Pizza",
type: "pizza",
owner_id: "Luigi Romano",
review: { rating: 3, reviewer_id "George" }
}
Uncorrelated Subquery as Filter
The following query on the reviews
collection performs filters using a
uncorrelated subquery on itself to find reviews greater than the average rating.
Node.js
let
results
=
await
execute
(
db
.
pipeline
()
.
collection
(
"reviews"
)
// Average review rating is 4.3
.
where
(
field
(
"rating"
).
greaterThan
(
db
.
pipeline
()
.
collection
(
"reviews"
)
.
aggregate
(
average
(
"rating"
).
as
(
"avg"
))
.
toScalarExpression
())))
.
select
(
"rating"
,
"reviewer_id"
);
Response
{
rating: 5,
reviewer_id "Alice"
},
{
rating: 5,
reviewer_id "Diana"
},
{
rating: 5,
reviewer_id "Hannah"
},
{
rating: 5,
reviewer_id "Julia"
}
Best practices
- Manage memory with
toArrayExpression():Be cautious withtoArrayExpression()subqueries, since materializing a large number of documents can exhaust the query memory limit (128 MiB). To mitigate this, useselect(...)within the subquery to return only the necessary fields and applywhere(...)filters to limit the number of documents returned. Consider usinglimit(...)if appropriate to cap the number of documents returned by the subquery. - Indexing:Ensure that fields used in the
where(...)clause of a subquery are indexed. Performant joins rely on the ability to perform index seeks rather than full table scans.
For more query best practices, refer to our guide covering query optimization .
Limitations
-
subcollection(...)scope:Thesubcollection(...)input stage is only supported within subqueries, as it requires the context of a parent document to resolve the hierarchical relationship and perform the join. - Nesting Depth:Subqueries can be nested up to 20 layers deep.
- Memory Usage:The 128 MiB limit on materialized data applies across the entire query, including all joined documents.
