Describe considerations for working with non-relational data on Azure →mediumMultiple ChoiceObjective-mapped
Which Cosmos DB API Should You Use for JSON Documents with SQL Queries?
A gaming application stores player profiles as JSON documents. Each profile has standard fields like playerId, username, and email, but also optional fields such as achievements and gamePreferences. The application needs to query profiles by playerId with low latency and also run SQL-like queries to find players with specific achievements. Which Azure Cosmos DB API should they choose?
Quick Answer
The answer is the SQL (Core) API. This API is the correct choice because it is Azure Cosmos DB’s native interface for working with JSON documents, directly supporting flexible schemas that include optional fields like achievements and gamePreferences without requiring schema changes. It also delivers low-latency point reads by playerId when you use the id field as the partition key, and it allows you to run SQL-like queries—such as SELECT * FROM c WHERE ARRAY_CONTAINS(c.achievements, 'specific_achievement')—without needing a separate translation layer. On the DP-900 exam, this scenario tests your understanding that the SQL API is the default, schema-agnostic option for JSON document workloads, while other APIs (like MongoDB or Cassandra) impose specific data models or query languages. A common trap is assuming any NoSQL API works the same, but the SQL API is the only one that combines native JSON support with standard SQL query syntax. Memory tip: “SQL = Schema-Less Queries for JSON.”
⚠ Common exam trap
A common mix-up: candidates confuse the MongoDB API's JSON document support with SQL-like query capability, but the question specifically requires SQL-like queries, which only the SQL (Core) API provides natively.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
C: SQL (Core) API
The SQL (Core) API is the best choice because it natively supports JSON documents with flexible schemas (including optional fields like achievements and gamePreferences), provides low-latency point reads by playerId using the id field as the partition key, and enables SQL-like queries (e.g., SELECT * FROM c WHERE ARRAY_CONTAINS(c.achievements, 'specific_achievement')) without requiring a separate indexing or translation layer.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A: Table API
Why it's wrong here
The Table API is designed for key-value data with a flat schema (Entity, Property, and Value), not for flexible JSON documents with SQL-like queries.
When this WOULD be correct
When the application stores simple key-value data (e.g., user preferences) with a single partition key and requires OData queries, but does not need to query nested JSON properties or use SQL syntax.
- ✗
B: MongoDB API
Why it's wrong here
While the MongoDB API supports JSON documents and querying, it uses the MongoDB query language, not native SQL-like syntax. The scenario specifically mentions SQL-like queries, making the SQL API a better fit.
- ✓
C: SQL (Core) API
Why this is correct
The SQL API provides native support for JSON documents, low-latency point reads by partition key (playerId), and the ability to run SQL-like queries on document fields such as achievements.
- ✗
D: Cassandra API
Why it's wrong here
The Cassandra API is based on the Cassandra Query Language (CQL) and is optimized for wide-column data models, not for flexible JSON documents with varying fields.
When this WOULD be correct
A question where the application requires a globally distributed, horizontally scalable database for time-series data or IoT telemetry with high write throughput and uses CQL for queries, and does not need JSON documents or SQL-like queries.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓C: SQL (Core) APICorrect answer▾
Why this is correct
The SQL API provides native support for JSON documents, low-latency point reads by partition key (playerId), and the ability to run SQL-like queries on document fields such as achievements.
✗A: Table APIWrong answer — click to see why▾
Why this is wrong here
The Table API uses a key-value store with a schema-less design but lacks native support for JSON documents with nested structures and SQL-like querying for nested fields like achievements.
★ When this WOULD be the correct answer
When the application stores simple key-value data (e.g., user preferences) with a single partition key and requires OData queries, but does not need to query nested JSON properties or use SQL syntax.
Why candidates choose this
Candidates may confuse the Table API's schema-less nature with JSON document support, or assume it supports SQL-like queries because of its OData query capabilities.
✗D: Cassandra APIWrong answer — click to see why▾
Why this is wrong here
The Cassandra API does not support SQL-like queries or JSON documents with flexible schemas; it is optimized for wide-column stores and uses CQL (Cassandra Query Language), not SQL.
★ When this WOULD be the correct answer
A question where the application requires a globally distributed, horizontally scalable database for time-series data or IoT telemetry with high write throughput and uses CQL for queries, and does not need JSON documents or SQL-like queries.
Why candidates choose this
Candidates may confuse Cassandra's wide-column model with document databases, or think its CQL is similar enough to SQL to support the required queries, overlooking the need for JSON and SQL syntax.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Partition key
A partition key is a value used by database systems to distribute data across multiple storage partitions, enabling faster queries and efficient scaling.
Key term
Cosmos DB API
Cosmos DB API is a collection of interfaces that lets applications interact with Azure Cosmos DB, a globally distributed NoSQL database, using different data models and query languages.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
6 more ways this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A healthcare application stores patient medical records as JSON documents. Each document contains a variable set of fields depending on the patient's conditions. The application needs to query records by any field and support high write throughput. Which Azure data store is most appropriate?
medium- A.Azure Blob Storage
- B.Azure Synapse Analytics
- ✓ C.Azure Cosmos DB with SQL API
- D.Azure Table Storage
Why C: Azure Cosmos DB with SQL API is the most appropriate choice because it natively supports storing and querying JSON documents with variable schemas, enabling efficient queries on any field. Its multi-model architecture and configurable indexing policies allow high write throughput while maintaining low-latency queries, which is critical for healthcare applications with dynamic patient records.
Variation 2. A social media application stores user profiles as JSON documents. Each profile has standard fields like userId, name, and email, but also optional fields such as education and work history. The application needs to query profiles by userId with low latency and also run SQL-like queries to find all profiles with a specific work history value. Which Azure Cosmos DB API should they choose?
medium- ✓ A.SQL (Core) API
- B.MongoDB API
- C.Gremlin (Graph) API
- D.Table API
Why A: The SQL (Core) API is the correct choice because it natively supports querying JSON documents with SQL-like syntax, enabling both low-latency point reads by userId and complex queries on nested fields like work history. It provides automatic indexing of all JSON properties, which ensures efficient execution of queries across optional fields without requiring schema management.
Variation 3. A social media application stores user profiles as JSON documents. Each user profile can have different attributes (e.g., some have 'education', others have 'work experience'). The application needs to query profiles by any attribute with low latency. Which Azure data store is most appropriate?
medium- A.Azure Blob Storage
- B.Azure Table Storage
- ✓ C.Azure Cosmos DB (SQL API)
- D.Azure SQL Database
Why C: Azure Cosmos DB with the SQL API is the correct choice because it natively supports schema-agnostic JSON documents, allowing each user profile to have varying attributes without requiring a fixed schema. Its indexing policies enable low-latency queries on any attribute, and it provides single-digit millisecond response times for point reads and queries, which is essential for a social media application.
Variation 4. A social media application stores user sessions as JSON documents. Each session document has fields like sessionId, userId, startTime, endTime, and a list of pageviews. The application needs to quickly retrieve a session by its sessionId and also run queries like 'find all sessions for a user in the last 24 hours' using SQL-like syntax. The data has no fixed schema; different sessions may include additional optional fields like 'deviceType' or 'promotionCode'. Which Azure data store should the company use?
easy- ✓ A.Azure Cosmos DB with SQL API
- B.Azure Table Storage
- C.Azure SQL Database
- D.Azure Blob Storage
Why A: Azure Cosmos DB with SQL API is the correct choice because it natively supports storing JSON documents with flexible schemas, allows fast point reads by sessionId using a unique identifier, and enables SQL-like queries (e.g., filtering by userId and startTime) with automatic indexing. Its schema-agnostic design handles optional fields like deviceType or promotionCode without requiring schema changes, and it provides low-latency reads essential for real-time session retrieval.
Variation 5. 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?
easy- A.A. Azure Cosmos DB for MongoDB API
- ✓ B.B. Azure Cosmos DB for NoSQL API (Core SQL API)
- C.C. Azure Cosmos DB for Table API
- D.D. Azure Cosmos DB for Apache Cassandra API
Why B: 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).
Variation 6. A social networking application stores user profiles as JSON documents in Azure Cosmos DB. Each profile includes fields such as 'userName', 'email', 'followersCount', and optional 'interests'. The application needs to perform fast point reads by 'userName' (under 10 ms) and also run queries to find all users with a 'followersCount' greater than a certain value. The development team prefers to use a query syntax similar to SQL. Which Azure Cosmos DB API should they choose?
medium- ✓ A.Azure Cosmos DB for NoSQL (SQL API)
- B.Azure Cosmos DB for MongoDB
- C.Azure Cosmos DB for Table
- D.Azure Cosmos DB for Apache Cassandra
Why A: Azure Cosmos DB for NoSQL (SQL API) is the correct choice because it natively supports SQL-like query syntax for querying JSON documents, enabling the required queries such as filtering by 'followersCount'. It also provides fast point reads (under 10 ms) by using the 'userName' field as the partition key, ensuring efficient direct access to individual documents.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.