Exam DP-420 topic 2 question 3 discussion

Answer is NYN. 1. N: The readings container does not need to be read to return all deviceids associated with a particular emailaddress, so only one query is needed: SELECT * FROM devices d WHERE d.owner.emailaddress = ________ 2. Y: measures is an array, and to filter or project fields from an array, you need to use join to create a cross join with the array. SELECT r.deviceid, r.ownerid, r.timestamp, m.value FROM readings r JOIN m IN r.measures WHERE m.metricid = ________ 3. N: owner is an object, not an array, so you can directly access the properties. deviceid, ownerid, emailaddress and model all exist in the devices container, so the readings container is not needed. SELECT d.deviceid, d.owner.ownerid, d.owner.emailaddress, d.model FROM devices d

Who ever wrote the suggested responses has no idea of what a JOIN does in a document database... it has nothing to do with joining documents, but is an inner-document join. It's NYN if the first questions means returning only devices, and none of the readings. It's YYN otherwise, because readings are in a separate container, and should be fetched using a second query.

1. No. Given the structure of your Cosmos DB containers and the documents, if you want to return all devices owned by a specific emailaddress, you might be able to do this with a single query depending on the structure and indexing of your Cosmos DB. However, if you're joining data across the readings and devices containers, and if the data is not normalized or if specific data exists in one container but not the other, you may need to execute multiple queries 2. No. Since all the required data (deviceid, ownerid, timestamp, and value for a specific metricid) is located within the same container (readings), there is no need to perform a join across multiple containers. You can retrieve all this data with a single query on the readings container. 3. No. All the requried data (deviceid, ownerid, emailaddress, and model) is located within the same container (device), there is no need to perform a join across multiple containers. You can retrieve all this data with a single query on the readings container.

N - Device container has all required info. N - In Reading container we have everything. Y - Need to join 2 containers.

Answer YYN

Y - requires 2 requests to get from 2 containers Y - need join to get 'value' in measures array Y - need join to get 'ownerid' and 'emailaddress' in owner array https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/join

No. Yes. Yes. ==================== First: No SELECT * FROM d WHERE d.emailaddress = "To return (?) for all devices ...". Sounds like the word "metrics" might be missing. If so, the answer is Yes and a second query must be added. =========== SELECT d.deviceid FROM d WHERE d.emailaddress = ? SELECT * FROM m WHERE m.deviceid = @deviceid ==================== Second: Yes SELECT m.deviceid FROM m WHERE m.metricid = ? SELECT d.deviceid, o.ownerid, d.timestamp FROM d JOIN o IN d.owner WHERE d.deviceid = @deviceid ==================== Third: Yes SELECT d.deviceid, o.ownerid, d.emailaddress, d.model FROM d JOIN o IN d.owner

The correct answer is YYN - Yes: you need 2 requests, because the data is in 2 different containers. - Yes: you need join to get the data from Array - No: No joins required