Question
You are developing an application that will use an Azure Cosmos DB for NoSQL account as a data source.
You need to create a report that displays the top five most ordered fruits as shown in the following table.
| Name | Type | Orders |
|---|---|---|
| apple | fruit | 1,000 |
| orange | fruit | 600 |
| banana | fruit, exotic | 400 |
| plum | fruit | 300 |
| mango | fruit, exotic | 200 |
A collection that contains aggregated data already exists. A sample document is shown in the exhibit.
Which two queries can you use to retrieve data for the report? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
![Sample document: name apple, type [fruit, exotic], orders 10000](https://pub-db5ae157f8cd449c89fd971fea13df3c.r2.dev/dp-420/t1q3-1-363b9d9c0b.png)
- A. SELECT TOP 5 i.name, i.types, i.orders FROM items i WHERE EXISTS(SELECT VALUE t FROM t IN i.types WHERE t.name = 'fruit') ORDER BY i.orders, i.types
- B. SELECT TOP 5 i.name, i.types, i.orders FROM items i WHERE EXISTS(SELECT VALUE t FROM t IN i.types WHERE t.name = 'fruit') ORDER BY i.orders DESC Correct
- C. SELECT TOP 5 i.name, i.types, i.orders FROM items i WHERE EXISTS(SELECT VALUE t FROM t IN i.types WHERE t.name = 'fruit') ORDER BY i.types DESC
- D. SELECT TOP 5 i.name, i.types, i.orders FROM items i WHERE ARRAY_CONTAINS(i.types, {name: 'fruit'}) ORDER BY i.orders DESC Correct
Explanation
The report needs fruit items sorted from the most to the least ordered, limited to five rows, so the query must filter on the fruit type and use ORDER BY i.orders DESC. Option B filters with an EXISTS subquery over the types array and option D filters with ARRAY_CONTAINS on the same array; both then sort by orders descending. Option A sorts by orders ascending (and adds an unnecessary second sort on types), so it returns the least ordered fruits, and option C sorts by types instead of orders, so the ranking is wrong.
