DP-420 Question #173: Real Exam Question with Answer & Explanation

Question

Drag and Drop Question You have an Azure Cosmos DB container named owners. Each item in owners represents an owner and their pets. The following is a sample of the JSON You are writing a query that will retrieve the average age of each pet owned by each owner, if the pet is older than two years. How should you complete the query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Answer:

Explanation

The question requires completing an Azure Cosmos DB SQL query to calculate the average age of pets older than two years for each owner by correctly using 'VALUE' for projection and 'IN' for iterating through an array.

Approach. The correct interaction is to drag 'VALUE' to the first blank target and 'IN' to the second blank target.

1. The subquery FROM p [BLANK 2] c.pets is designed to iterate over the pets array within each owner's document (c). In Azure Cosmos DB SQL, the IN keyword is used to flatten an array, treating each element as a distinct item that can be aliased (in this case, as p). Therefore, 'IN' correctly completes FROM p IN c.pets.

2. The subquery's SELECT [BLANK 1] (p.age) aims to project the age of each pet (p.age) that satisfies the WHERE p.age > 2 condition. When you want to project a raw value (like p.age) directly without wrapping it in a JSON object, SELECT VALUE is the appropriate syntax. This creates a simple array of age values that can then be aggregated by the outer AVG(PetAges) function. Therefore, 'VALUE' correctly completes SELECT VALUE (p.age).

Common mistakes.

• common_mistake. Using 'JOIN' for the second blank ('FROM p [BLANK 2] c.pets') is incorrect because 'JOIN' is used to combine documents from different collections or to join a self-referencing document, not to iterate over elements within an array of a single document. Using 'COUNT', 'SUM', or 'AS' for the first blank ('SELECT [BLANK 1] (p.age)') would also be incorrect. 'COUNT' and 'SUM' are aggregate functions that would return a single number, not a collection of individual pet ages needed for the outer AVG function. 'AS' is used for aliasing properties, and while SELECT p.age AS PetAge is valid, it would return an object { 'PetAge': <age_value> } for each pet, making it unsuitable for direct averaging as AVG() expects a numerical value, and the outer AVG(PetAges) suggests PetAges is an array of values, not an array of objects.

Concept tested. Azure Cosmos DB SQL API querying, specifically involving subqueries for array iteration, the IN keyword for flattening arrays, and the SELECT VALUE keyword for projecting raw scalar values for aggregation.

No community discussion yet for this question.