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.

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