A social media application stores user posts as JSON documents in Azure Cosmos DB. Each post includes fields such as postId, userId, content, timestamp, and an array of tags. The development team wants to query posts by userId and timestamp range using a SQL-like syntax. Which Azure Cosmos DB API should they choose?
Azure Cosmos DB NoSQL API stores documents natively as JSON, and its SQL-like query syntax (SELECT * FROM c WHERE c.userId = @id AND c.timestamp > @time) is directly optimized for these documents. The API auto-indexes every property, enabling efficient range scans on timestamp and equality filters on userId without manual index tuning. This makes it the most natural fit for a social media post store requiring flexible schemas and rich queries.
Why this answer
The Azure Cosmos DB for NoSQL API (Core SQL API) is the correct choice because it natively supports SQL-like querying (SELECT, WHERE, ORDER BY) over JSON documents. The team's requirement to query posts by userId and timestamp range using SQL-like syntax is directly supported by this API, which treats each JSON document as an item and allows filtering on nested fields like userId and timestamp. Other APIs either lack native SQL-like syntax or are optimized for different data models (e.g., MongoDB uses a JSON-like query language, Table API uses OData, Cassandra uses CQL).
Exam trap
The trap here is that candidates confuse 'SQL-like syntax' with any API that supports querying, but only the Core SQL API provides native SQL SELECT statements over JSON documents, while other APIs use different query languages (e.g., MongoDB's query operators, Cassandra's CQL) that are not SQL-like in the standard sense.
Why the other options are wrong
The MongoDB API uses a MongoDB-compatible query language, not SQL-like syntax. The question specifically requires SQL-like queries, which is a feature of the Core (SQL) API.
The Table API uses key/attribute-based lookups and does not support SQL-like queries with WHERE clauses on non-key fields like userId and timestamp range. It is designed for simple key-value access, not complex queries on JSON documents.
The Cassandra API uses CQL (Cassandra Query Language) and is optimized for wide-column, high-throughput workloads, not for SQL-like queries on JSON documents with nested arrays like tags.
When would these options actually be correct?
If the development team needed to use MongoDB tools, drivers, and query syntax (e.g., db.posts.find({userId: '123'})), and the application already used MongoDB, then Azure Cosmos DB for MongoDB API would be the correct choice.
A question asking for an API to store and query large volumes of structured, non-relational data (e.g., sensor readings) with fast point lookups by partition key and row key, and where SQL-like queries are not required. The Table API would be correct for simple key-value access with O(1) latency.
An application requires a distributed, high-write-throughput database for time-series data with a schema that can be modeled as wide-column rows, and the team prefers using CQL for queries.
Why candidates pick the wrong answer
Candidates may confuse JSON document storage with MongoDB, assuming MongoDB is the only option for JSON documents, or they may not realize that Cosmos DB's Core API also supports JSON documents with SQL-like queries.
Candidates may confuse the Table API's support for querying by partition key and row key with the ability to query on arbitrary fields, or they may think 'Table' implies general-purpose querying similar to SQL tables.
Candidates may confuse Cassandra's CQL with SQL-like syntax, or assume that any NoSQL API in Cosmos DB supports similar querying capabilities for JSON documents.