B. Use mathematical expressions to calculate values ( - ) 2. Modify this query to subtract the birth value, in milliseconds, from the current date, in milliseconds.SELECT (CLOCK_MILLIS() - MIN(STR_TO_MILLIS(dateOfBirth))) AS age_in_ms, META() as metaFROM couchmusic1.userprofile; 3. Modify this query to calculate the (floating point) age in years, by dividing the result by 3.1536e10 (31536000000), which is one year in milliseconds.SELECT (CLOCK_MILLIS() - MIN(STR_TO_MILLIS(dateOfBirth))) / 3.1536e10 AS age_in_years,META() AS metaFROM couchmusic1.userprofile;Copyright 2016 - Couchbase Learning Services 50
C. Truncate results to return an integer value 4. Last, modify the query to truncate the final value to an integer.SELECT TRUNC((CLOCK_MILLIS() - MIN(STR_TO_MILLIS(dateOfBirth))) / 3.1536e10)AS age_of_oldest_userprofile, META() AS metaFROM couchmusic1.userprofile;End of LabCopyright 2016 - Couchbase Learning Services 51
Module 7Joining and combining documents in N1QLCopyright 2016 - Couchbase Learning Services 52
Lab 1 - Join foreign documents by surrogate andnatural key referencesObjectives A Join foreign document by modified surrogate key reference B Join foreign documents by modified surrogate key array references lC (OPTIONAL) Join documents based on an embedded natural document keyA. Join foreign document by modified surrogate key reference 1. In the Couchbase Web UI, in the Data Buckets screen, use the Documents Filter to locate and open the first Playlist document. 2. Modify the first Playlist document to add a track attribute, with the same value as the first reference in this document's tracks array. 3. Copy the track ID for use in the next step, and save your changes to this document.4. Use the Documents Filter to look up up the Track ID copied above, prefixed by the literal key prefix \"track::\" .Copyright 2016 - Couchbase Learning Services 53
5. Review the Track document structure. 6. In your query tool, select the owner and name from the Playlist document by its ID, and the artist and title from the Track document. Use the key of Playlist document modified above, to get this specific document by its key, aliasing it as p. 7. Join the related Track document, aliased as t, on the key value embedded in the new track attribute added to the Playlist, above. Use concatenation in the ON KEYS expression, to prefix the track value with the literal string \"track::\".SELECT p.owner, p.name, t.artist, t.titleFROM couchmusic1.playlist AS pUSE KEYS \"playlist::00011b74-12be-4e60-abbf-b1c8b9b40bfe\"JOIN couchmusic1.track AS tON KEYS \"track::\" || p.track; 8. Run the query. You should see results joined from the two documents.Copyright 2016 - Couchbase Learning Services 54
B. Join foreign documents by modified surrogate key array references 9. Modify the query built above to build a key array, by iterating over each key in the tracks array, as a variable named trackId. Concatenate the key pattern prefix \"track::\" to each trackId, as it is assigned to the key array.SELECT p.owner, p.name, t.artist, t.titleFROM couchmusic1.playlist AS p USE KEYS \"playlist::00011b74-12be-4e60-abbf-b1c8b9b40bfe\"JOIN couchmusic1.track AS t ON KEYS ARRAY \"track::\" || trackId FOR trackId IN p.tracks END; 10. Run the query. You should see results as above, but this time for each Track referenced in the tracks array embedded in the Playlist document.C. (OPTIONAL) Join documents based on an embedded natural document key 11. Select the title, firstName, lastName, and email, from couchmusic1.userprofile aliased as u, along with the name attribute of a country document as countryName. Use the specific document key \"userprofile::abandoninghouseclean34190\". Reference the userprofile attributes through an alias u, and country attributes through an alias c. 12. Join the couchmusic1.country documents, aliased as c, to a specific document key comprised of the literal prefix \"country::\" concatenated to the countryCode value of the address object in the relevant userprofile document (already aliased as u).SELECT u.title, u.firstName, u.lastName, u.email, c.name AS countryNameFROM couchmusic1.userprofile u USE KEYS \"userprofile::abandoninghouseclean34190\"JOIN couchmusic1.country c ON KEYS \"country::\" || u.address.countryCode; Notice that the countryCode in this example is a natural key, embedded in the userprofile document, and used with prefix as the country document key.Copyright 2016 - Couchbase Learning Services 55
13. Run the query. You should see the name value of the country document, whose document key is comprised of the countryCode in the userprofile document, prefixed with the literal string \"country::\". The results document should alias this value using the value \"countryName\".End of LabCopyright 2016 - Couchbase Learning Services 56
Lab 2 - Combine data by nesting foreign documentsObjectivesA Review document attributes in relation to nestingB NEST foreign documents by a key reference in the primary documentC Use aliasing to compensate for name collision between nested documentsD Narrow the foreign document attribute to be nestedE Compare the structure of NEST and JOIN resultsF (OPTIONAL) Query playlist and related track names as a single documentA. Review document attributes in relation to nesting1. From the student resources, open these three documents:couchmusic1-data-model.pdfcouchmusic2-data-model.pdfcouchmusic3-data-model.pdf2. In couchmusic3, review the structure of the Sub-region and Country documents, specifically noticing that:a. Document type is identified by root node in both documentsb. Both document types have a \"name\" propertyc. Sub-region documents have a countries array, comprised of countryCode valuesd. Country documents have their countryCode in their key, as well as in an attributee. Sub-region is spelled with \"-\", which requires an escape character in N1QL3. In the Web UI, open the couchmusic3 Documents, and use the Documents Filter to open and examine the first Country document and first Sub-region document.4. Write down the key of the first Sub-region document:______________________________B. NEST foreign documents by a key reference in the primary document5. In your query tool, run the following query, using the Sub-region key above.SELECT subregion, countryFROM couchmusic3.`sub-region` AS subregion USE KEYS 'sub-region::005'NEST couchmusic3.country AS country ON KEYS ARRAY 'country::' || code FOR code IN subregion.countries END; Copyright 2016 - Couchbase Learning Services 57
6. In this query, notice: a. In the FROM, sub-region is delimited with back-ticks as part of a longer reference b. In the USE KEYS, the key itself is delimited with tick-marks as a literal value 7. In the results for this query, notice that the entire sub-region document is nested along with all its related Country documents, in a single result.C. Use aliasing to compensate for name collision between nested documents 8. In the query, modify the SELECT clause to return only the sub-region.name value, in place of the entire Sub-region document:SELECT subregion.name, countryFROM couchmusic3.`sub-region` AS subregion USE KEYS 'sub-region::005'NEST couchmusic3.country AS country ON KEYS ARRAY 'country::' || code FOR code IN subregion.countries END; 9. Run the query, and notice in the results that only the sub-region name is returned with the Country documents, and not the entire Sub-region document. 10. Modify the query to select only the country.name value, rather than the entire document.SELECT subregion.name, country.nameFROM couchmusic3.`sub-region` AS subregion USE KEYS 'sub-region::005'NEST couchmusic3.country AS country ON KEYS ARRAY 'country::' || code FOR code IN subregion.countries END; 11. Run the query, and you should receive an error:[ { \"code\": 3000, \"msg\": \"Duplicate result alias name.\" }] 12. Modify the query to alias the subregion.name as subregion_name:SELECT subregion.name AS subregion_name, country.nameFROM couchmusic3.`sub-region` AS subregion USE KEYS 'sub-region::005'NEST couchmusic3.country AS country ON KEYS ARRAY 'country::' || code FOR code IN subregion.countries END;Copyright 2016 - Couchbase Learning Services 58
D. Narrow the foreign document attribute to be nested 13. Run the query, and you should receive only the subregion_name as a result, but no Country documents. Note, nested document selection cannot be modified in the SELECT clause, only in the NEST clause. The NEST clause can specify a single attribute, of whatever complexity, whether it be a top-level attribute (complete document), or a nested attribute. 14. In the SELECT clause, remove the country.name reference. 15. In the NEST clause, modify the statement to nest only the country.name value, aliased as country_name. 16. In the SELECT clause, select the aliased value from the NEST clause:SELECT subregion.name AS subregion_name, country_nameFROM couchmusic3.`sub-region` AS subregion USE KEYS 'sub-region::005'NEST couchmusic3.country.name AS country_name ON KEYS ARRAY 'country::' || code FOR code IN subregion.countries END; 17. Run the query, and you should return a single document with two attributes: subregion_name and country_name. The country_name should be an array of all country names within this sub-region.Copyright 2016 - Couchbase Learning Services 59
E. Compare the structure of NEST and JOIN results 18. Modify the query to JOIN the related documents, rather than NEST them.SELECT subregion.name AS subregion_name, country_nameFROM couchmusic3.`sub-region` AS subregion USE KEYS 'sub-region::005'JOIN couchmusic3.country.name AS country_name ON KEYS ARRAY 'country::' || code FOR code IN subregion.countries END; 19. Run the query, and consider the structural difference of NEST and JOIN results.F. (OPTIONAL) Query playlist and track names as a single document 20. (OPTIONAL) Write a query against the couchmusic1 bucket which returns Playlist names along with an array of Track titles, in a single document. If you use the following Playlist ID should see a result like the following: a. Playlist ID: playlist::00020d8a-38eb-4db0-8e77-70eebc4fc2c7End of Lab Copyright 2016 - Couchbase Learning Services 60
Lab 3 - Implement the Lookup Key pattern using JOINsyntaxObjectives A Analyze a document for alternate lookup values B Create a Lookup Key document C Retrieve a Userprofile through a Lookup Key documentA. Analyze a document for alternate lookup values 1. In the Web UI, use the Documents Filter or Document ID lookup to open this document:userprofile::unreclaimedtablespoonfuls65337 Alternately, in your query tool, open this document using this query:SELECT *, Meta() AS metadataFROM couchmusic1USE KEYS 'userprofile::unreclaimedtablespoonfuls65337'LIMIT 1; Note: the syntax of this query will be explained further elsewhere in this course. 2. Review the returned document for potential uniquely-identifying values, such as these. Note, as this is JSON, not all documents may have all these values:SELECTuserprofile.username,userprofile.email,userprofile.phones.cell,userprofile.phones.homeFROM couchmusic1USE KEYS 'userprofile::unreclaimedtablespoonfuls65337'LIMIT 1;Copyright 2016 - Couchbase Learning Services 61
B. Create a Lookup Key document 3. In the Web UI, create a lookup key document using the following: - key: [email protected] - lookup_key: userprofile::unreclaimedtablespoonfuls65337 Alternately, create this document using the following query:INSERT INTO couchmusic1(KEY, VALUE)VALUES ('[email protected]',{'lookup_key': 'userprofile::unreclaimedtablespoonfuls65337'})RETURNING *; Note: the syntax of this query will be explained further later in the course.C. Retrieve a Userprofile through a Lookup Key document 4. In your query tool, run the following query to retrieve the lookup key document:SELECT *FROM couchmusic1USE KEYS '[email protected]'; 5. In your query tool, retrieve the userprofile data via its email address, using the new lookup key document.SELECT lookup_key, profile.*, Meta() AS metadataFROM couchmusic1.lookup_keyUSE KEYS '[email protected]' JOIN couchmusic1.userprofile AS profile ON KEYS lookup_keyLIMIT 1; 6. With the instructor, discuss the merits of using the traditional JSON Lookup Key pattern, relative to creating a secondary index on userprofile.email or any other alternative uniquely-identifying attribute. What if the alternate lookup value (e.g., the email address) did not already exist in the document to be retrieved?Copyright 2016 - Couchbase Learning Services 62
Given the flexibility of JSON NoSQL, is adding lookup key documents better or worse than modifying relevant userprofiles to include new attributes or attribute values? What if only a subset of userprofiles had the alternate attribute? Would it be better to implement lookup key documents, or create a filtered secondary index?End of LabCopyright 2016 - Couchbase Learning Services 63
Lab 4 - Implement a sub-query to filter resultsObjectivesA UNNEST Track ID values by PlaylistB Implement sub-query to check array length in related documentA. UNNEST Track ID values by Playlist1. From the student resources, open this document:couchmusic1-data-model.pdf2. Review the structure of Playlist and Track documents, noticing their relationship in this data bucket, and considering:a. How does each Playlist refer to the Track documents it contains?b. How is each Track rated?c. How could you determine which Tracks have never yet been rated?3. In your query tool, unnest all Track ID values along with the Playlist name. Limit your results if using cbq, or verify the Result Limit in Query Workbench:SELECT a.playlist.name, trackIdFROM couchmusic1 AS aUNNEST a.playlist.tracks AS trackIdLIMIT 50;You should see 50 different Track ID values, varying across their respective Playlist names.B. Implement sub-query to check array length in related document4. Add a WHERE condition, which uses a sub-query to SELECT the Track for this Track ID if the length of its ratings array is zero. Use a WHERE clause for the condition.SELECT a.playlist.name, trackIdFROM couchmusic1 AS aUNNEST a.playlist.tracks AS trackIdWHERE EXISTS ( SELECT * FROM couchmusic1 AS b USE KEYS 'track::' || trackId WHERE ARRAY_LENGTH(b.track.ratings) = 0);Note, when implementing sub-queries within the same bucket, you must alias the main andsub-query buckets in the respective FROM clauses to avoid ambiguous references.Copyright 2016 - Couchbase Learning Services 64
5. Copy the first Track ID in your results. In the Web UI for the couchmusic1 Documents, use the Lookup ID field to retrieve this particular document. Remember to use the \"track::\" prefix. 6. (OPTIONAL) Write a query that uses a sub-query to test where a Track ID referenced in a Playlist has no actual corresponding Track document in the bucket.End of LabCopyright 2016 - Couchbase Learning Services 65
Lab 5 - Implement a sub-query to combine resultsObjectives A Implement a query using EXCEPT to determine unused documents B Modify the EXCEPT query to observe INTERSECT behavior C Review variant query syntaxA. Implement a query using EXCEPT to determine unused documents 7. From the student resources, open this document: couchmusic1-data-model.pdf 8. Review the structure of Playlist and Track documents, noticing their relationship in this data bucket, and considering: a. How does each Playlist refer to the Track documents it contains? b. How could you determine which tracks are not included in any playlist? 9. In your query, write the following query, to determine the total Track count:SELECT COUNT(*) AS track_countFROM couchmusic1.track; 10. Run the query. You should see a result similar to this: 11. You want to determine how many Tracks are not used in any Playlist. So, select the ID of each Track document in couchmusic1, but then remove (EXCEPT) all tracks in the tracks array of any Playlist document.Copyright 2016 - Couchbase Learning Services 66
12. In your query tool, write the following query:SELECT track.id AS trackIdFROM couchmusic1 EXCEPT SELECT trackId FROM couchmusic1 UNNEST playlists.tracks AS trackId; Recall that UNNEST surfaces each value in the tracks array as an individually selected item. Also, notice that both the track.id and playlists.tracks values are aliased as trackId. 13. Run the query. You should see a result similar to the following, indicating there are 12,053 Track documents which are not referenced in any Playlist: Note, if you are using Query Workbench, you will need to adjust your Result Limit value to more than the total number of tracks (e.g., 100,000). Note, your Result Count may vary slightly depending on work done in other Labs. Note, the selected and excepted attributes must be aliased to the same name (e.g., trackId).B. Modify the EXCEPT query to observe INTERSECT behavior 14. You want to determine how many Tracks are used at least once in a Playlist. So, modify the prior query to determine the intersection of all Track documents with all tracks that appear in the tracks array of a Playlist. 15. In your query tool, modify the prior query as shown:SELECT track.id AS trackIdFROM couchmusic1 INTERSECT SELECT trackId FROM couchmusic1 UNNEST playlist.tracks AS trackId;Copyright 2016 - Couchbase Learning Services 67
16. Run the query. You should see a result similar to the following, indicating there are 85,164 Track documents which are referenced in one or more Playlists:C. Review variant query syntax 17. Review the following query, which returns identical results to the EXCEPT query above, at effectively the same speed. Differences from the prior query are highlighted. Is the syntax more or less clear?SELECT tracks.id AS trackIdFROM couchmusic1.track AS tracks EXCEPT SELECT trackId FROM couchmusic1.playlist AS playlists UNNEST playlists.tracks AS trackId; Note, this version uses document root attribute references in the FROM clause, and aliases these for use in the corresponding SELECT and UNNEST clauses. 18. Review the following query, which returns identical results to the EXCEPT queries above, at effectively the same speed. Is the syntax more or less clear?SELECT a.track.id AS trackIdFROM couchmusic1 a EXCEPT SELECT trackId FROM couchmusic1 b UNNEST b.playlist.tracks AS trackId; Note, this version uses abstractly named aliases, to distinguish bucket references in each FROM clause. This is functionally unnecessary, but is this syntax more or less clear?End of LabCopyright 2016 - Couchbase Learning Services 68
Module 8Manipulating and migrating data using N1QLCopyright 2016 - Couchbase Learning Services 69
Lab 1 - Insert, generate, and upsert dynamicallygenerated documents from existing dataObjectivesA Verify documents created in prior LabB Insert additional Lookup Key document, and query to JOIN related UserprofileC Conditionally generate Lookup Key documents from query resultsD Upsert conditionally generated Lookup key documentsA. Verify documents created in prior Lab14. In your query tool, query whether a Lookup Key document exists from a prior lab:SELECT lookup_key, profile.*, Meta() AS metadataFROM couchmusic1.lookup_keyUSE KEYS '[email protected]' JOIN couchmusic1.userprofile AS profile ON KEYS lookup_keyLIMIT 1; 15. If the query does NOT return a Userprofile for user \"Rasmus Oja\", then run the following query, to INSERT this document. Then run the prior query to verify you can join a Userprofile by this Lookup Key:INSERT INTO couchmusic1(KEY, VALUE)VALUES ('[email protected]',{'lookup_key': 'userprofile::unreclaimedtablespoonfuls65337'})RETURNING *;B. Insert additional Lookup Key document, and query to JOIN related Userprofile16. Write a query to insert a second Lookup Key document, using these values:a. KEY: 040-026-56-24b. VALUE: (text value, not numeric) 'lookup_key': 'userprofile::unreclaimedtablespoonfuls65337' (as a JSON object)17. Write a query to select a Userprofile, based on this key and value.Note, see the prior queries in this Lab and the related Lookup Key lab, for help, if needed. Copyright 2016 - Couchbase Learning Services 70
C. Generate Lookup Key documents from query results 18. You want to generate new Lookup Key documents from existing Userprofile data, but only where a given profile is populated with the values you need. Review and run the following query:SELECT userprofile.email AS newKey, {'lookup_key': 'userprofile::' || userprofile.username} AS newValueFROM couchmusic1WHERE userprofile.username IS VALUED AND userprofile.email IS VALUEDUNION SELECT userprofile.phones.cell AS newKey, {'lookup_key': 'userprofile::' || userprofile.username} AS newValueFROM couchmusic1WHERE userprofile.username IS VALUED AND userprofile.phones.cell IS VALUEDUNION SELECT userprofile.phones.home AS newKey, {'lookup_key': 'userprofile::' || userprofile.username} AS newValueFROM couchmusic1WHERE userprofile.username IS VALUED AND userprofile.phones.home IS VALUEDORDER BY newValueLIMIT 10; You should see results similar to the following:Copyright 2016 - Couchbase Learning Services 71
D. Upsert conditionally generated Lookup key documents 19. You want to upsert (insert if new, update if existing) a set of generated Lookup Key documents, from existing Userprofile data. In your query tool, modify the prior query as shown, adding an UPSERT clause, and removing the ORDER BY and LIMIT clauses.UPSERT INTO couchmusic1 (KEY newKey, VALUE newValue)SELECT userprofile.email AS newKey, {'lookup_key': 'userprofile::' || userprofile.username} AS newValueFROM couchmusic1WHERE userprofile.username IS VALUED AND userprofile.email IS VALUEDUNION SELECT userprofile.phones.cell AS newKey, {'lookup_key': 'userprofile::' || userprofile.username} AS newValueFROM couchmusic1WHERE userprofile.username IS VALUED AND userprofile.phones.cell IS VALUEDUNION SELECT userprofile.phones.home AS newKey, {'lookup_key': 'userprofile::' || userprofile.username} AS newValueFROM couchmusic1WHERE userprofile.username IS VALUED AND userprofile.phones.home IS VALUED;ORDER BY newValueLIMIT 10; 20. Run the query. Then, examine the change in document count for couchmusic1.21. (OPTIONAL) Write a query to look up a userprofile document using one of the newly generated documents.22. With the instructor, discuss the merits of using the traditional JSON Lookup Key pattern, relative to creating a secondary index on userprofile.email or any other alternative uniquely-identifying attribute. To what use cases would this technique best apply, relative to implementing secondary indexes or covering indexes?End of Lab Copyright 2016 - Couchbase Learning Services 72
Lab 2 - Insert and delete a documentObjectivesA Insert event tracking documentB Delete event tracking documentA. Insert event tracking document1. You want to insert a document tracking a change to a Userprofile document. Insert a new document using the following values:a. KEY: event::change::username::unreclaimedtablespoonfuls65337b. VALUE: (text value, not numeric) { 'completed': false, 'created': '2015-12-29T13:11:47', 'event': 'change username', 'new': 'somethingelse99999', 'old': 'unreclaimedtablespoonfuls65337' }2. (OPTIONAL) In the Web UI, look up the inserted document by its key.B. Delete a document 3. You want to delete a document by its key. Run the following query:DELETE FROM couchmusic1 USE KEYS 'event::change::username::unreclaimedtablespoonfuls65337'; 4. (OPTIONAL) In the Web UI, verify the document can no longer be retrieved by its key.End of Lab Copyright 2016 - Couchbase Learning Services 73
Lab 3 - Update with fixed or calculated valuesObjectives A Select calculated value from document B Update and add document attributes with fixed or calculated values C Update document by unsetting an attributeA. Select calculated value from document 1. You want to calculate the average rating for a Track document, while retrieving its Document ID. Run this query:SELECT META(a).id AS track_id, AVG(r.rating) AS average_rating FROM couchmusic2 a USE KEYS 'track::00003466BBCDD2242E13D71F075D01458B8910FE' UNNEST a.ratings AS r;B. Update and add document attributes with fixed or calculated values 2. You want to update this document with new and updated attributes, returning the result: a. average_rating = 0 b. genre = \"Top 40\" c. updated = NOW_STR()UPDATE couchmusic2 USE KEYS 'track::00003466BBCDD2242E13D71F075D01458B8910FE' SET average_rating = 0, genre = \"Top 40\", updated = NOW_STR()RETURNING *;Copyright 2016 - Couchbase Learning Services 74
C. Update document by unsetting an attribute 3. You want to remove the average_rating attribute from this document, while returning the resulting document for display. Modify the prior query as shown, then run the query:UPDATE couchmusic2 USE KEYS 'track::00003466BBCDD2242E13D71F075D01458B8910FE' UNSET average_ratingRETURNING *;End of LabCopyright 2016 - Couchbase Learning Services 75
Lab 4 - Merge value into document using sub-queryObjectivesA Calculate an average rating for a specific trackB Begin MERGE statement using the averaging queryC Finish MERGE statement to assign new attribute to key-matched documentA. Calculate an average rating for a specific track1. You want to calculate the average rating for a Track document. Run this query:SELECT AVG(r.rating) AS average_rating FROM couchmusic2 USE KEYS 'track::00003466BBCDD2242E13D71F075D01458B8910FE' UNNEST ratings AS r;B. Begin MERGE statement using the averaging query2. You want this value to be updated into the source document. Modify the prior query to merge results back into the couchmusic2 bucket, using the query above. Alias the bucket references as shown, to avoid ambiguous references. Do not run the query yet.MERGE INTO couchmusic2 AS a USING ( SELECT AVG(r.rating) AS average_rating FROM couchmusic2 AS b USE KEYS 'track::00003466BBCDD2242E13D71F075D01458B8910FE' UNNEST b.ratings AS r ) AS cC. Finish MERGE statement to assign new attribute to key-matched document3. Add a reference to the key which, when matched, should cause a new attribute - to be named \"average_rating\" - to be set with the like-named value calculated above, and then updated back into the document. Ensure the resulting document is not just merged, but also returned as the result of this query.MERGE INTO couchmusic2 AS a USING ( SELECT AVG(r.rating) AS average_rating FROM couchmusic2 AS b USE KEYS 'track::00003466BBCDD2242E13D71F075D01458B8910FE' UNNEST b.ratings AS r ) AS c ON KEY 'track::00003466BBCDD2242E13D71F075D01458B8910FE' WHEN MATCHED THEN UPDATE SET a.average_rating = c.average_ratingRETURNING *;Copyright 2016 - Couchbase Learning Services 76
4. Run the query. You should see the merged record displayed as a result.5. (OPTIONAL) In the Web UI, look up this document in couchmusic2 by its ID.End of Lab Copyright 2016 - Couchbase Learning Services 77
Search
