Query to get all Cosmos DB documents referenced by another

Question

Assume I have the following Cosmos DB container with the possible doc type partitions:

{
  "id": <string>,
  "partitionKey": <string>, // Always "item"
  "name": <string>
}

{
  "id": <string>,
  "partitionKey": <string>, // Always "group"
  "items": <array[string]>  // Always an array of ids for items in the "item" partition
}

I have the id of a "group" document, but I do not have the document itself. What I would like to do is perform a query which gives me all "item" documents referenced by the "group" document.

I know I can perform two queries: 1) Retrieve the "group" document, 2) Perform a query with IN clause on the "item" partition.

As I don't care about the "group" document other than getting the list of ids, is it possible to construct a single query to get me all the "item" documents I want with just the "group" document id?

David Makogon David Makogon · Accepted Answer · 2021-01-16T00:14:34

You'll need to perform two queries, as there are no joins between separate documents. Even though there is support for subqueries, only correlated subqueries are currently supported (meaning, the inner subquery is referencing values from the outer query). Non-correlated subqueries are what you'd need.

Note that, even though you don't want all of the group document, you don't need to retrieve the entire document. You can project just the items property, which can then be used in your 2nd query, with something like array_contains(). Something like:

SELECT VALUE g.items
FROM g
WHERE g.id="1"
AND g.partitionKey="group"

SELECT VALUE i.name
FROM i
WHERE array_contains(<items-from-prior-query>,i.id)
AND i.partitionKey="item"

This documentation page clarifies the two subquery types and support for only correlated subqueries.

Query to get all Cosmos DB documents referenced by another

1 Answers