Is partition key needed in queries even though JSON is indexed

Question

I'm planning on using Cosmos Db (Document Db) and I'm trying to understand how the queries, indexing and partitions relate to each other.

How to partition and scale in Azure Cosmos Db talks about the partition key and other documentation indicates that partition key + id = unique id for the document. But then SQL Query and SQL syntax in Azure Cosmos Db says it provides automatic indexing of JSON documents without requiring explicit schema or creation of secondary indexes.

I understand that partition key is important for scalability and how data is stored. But if we think about searching is the partition key kind of like extra filter/where clause? All the documents are indexed so I can execute query like:

SELECT * 
FROM Families
WHERE Families.address.state = "NY"

Should I still specify the partition key or indicate some how that cross partition queries are allowed when using this SQL query syntax?

Dioscuri

Answer 1

Your first link gives the answer for this:

For partitioned collections, you can use PartitionKey to run the query against a single partition (though Cosmos DB can automatically extract this from the query text), and EnableCrossPartitionQuery to run queries that may need to be run against multiple partitions.

So, yes, you either need to specify the WHERE clause which will make query run against a single partition, or set EnableCrossPartitionQuery to true in query options.

Selfassertion

Thanks! I was confused how these things work together when querying documents. I guess the index cosmos db builds is also per partition. – Dioscuri

@Toni: I believe your guess is correct, the partitions are isolated from a query perspective unless something prompts a cross partition query. A Channel9 video on this subject stated that the partition map is even downloaded to the DocumentDb api client object, this in my mind emphasises just how independent the partitions are once a CosmosDb usage threshold triggers physical partitioning. – Sadomasochism

By the way, this doesn't work. Even if your where has nothing but a search by the partition key value, it will sometimes fail with the "cress partition" error, and then the next second work fine. Just the latest Azure bug I have ran into. – Deuteranope

@TonyGutierrez have you got confirmation this is a bug? I am getting the error even though my partition key is in the where clause. – Suiter

It's probably because you recently rebuilt your collection?: #47401237 – Deuteranope

Answer 2

You don't have to do that anymore, EnableCrossPartitionQuery is set to true by default nowadays. This means Cosmos won't complain if you don't skip the partition key in your query.

More info here.

Chipman

Answer 3

You don't need to specify a partition key to the query. Recent version enabled cross partition queries by default

Mordvin

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Lowis