create primary index IDX_primary_couchmusic2 on playlist
Working with N1QL Queries and Indexes
Introduction
In this lab you will be implementing several solutions in support of additional Couchify features. In particular, you will be implementing the login scenario, where a user logs into the Couchify application and their user information is provided along with the playlists they currently own. A second scenario you will implement supports the user selecting a playlist and being able to review and play the tracks in the playlist.
What you will gain experience with
-
Defining N1QL queries to satisfy basic and more sophisticated queries
-
Defining indexes to support high-performing queries
-
Using the query workbench to develop the queries
-
Implementing your queries in the SDK to submit the queries and process results.
Estimated time to complete: 45 minutes
Instructions
If you have not already done so, start a lab session. Your starting point will be here Additionally, ensure you are logged into the Capella dashboard. You will be using the Capella dashboard to create indexes, as well as developing and testing queries.
Querying for playlists associated to a user
In this first scenario, you will be building a query to return the playlists for the user who has logged into Couchify. Imagine that the first step in logging on is to verify user credentials and then use the username value to locate the userprofile document having the specified username. This could be implemented a number of ways. For now, we’ll assume the application will use a standard Key/Value fetch using the key. Once the userprofile has been loaded, the associated playlists should also be loaded and displayed. In order to support this, you will need to write a query to return all playlists having the specified owner username.
Setup
-
Take a moment to inspect a playlist document in the Document Editor
-
Click on Documents sub-menu found under the Data Tools tab on the Database menu
-
Select the couchmusic2 bucket and the Couchify scope and the playlist collection
-
Open one of the playlist documents by clicking on the key
-
Notice the structure of the document and especially note the owner object referenced and the username sub-element.
-
-
Think about how this query would be specified if you knew what the value of username was.
Tasks
-
Switch to the Query Editor by clicking on the Query Workbench link from the Data Tools menu for the Couchify Database.
-
It might be a good idea until you are completely sure what index to define to simply create a primary index for the playlist collection in the couchmusic2 bucket in the couchify scope.
-
To do so, you can either use the full specifier for bucket, scope and collection or ensure that you have selected the bucket and scope from the pull down so you only have to reference the collection name in the N1QL statement
-
Recall that a PRIMARY INDEX is not recommended for production, but will make it possible for you to perform ad-hoc queries until you are certain what your query will look like.
-
Begin creating a query that will return all playlist documents having the username sub-element of the owner element equal to stockadeseffusing18695. It may be easiest to start by hard-coding the search value before creating a query using a named parameter.
-
Don’t be surprised to find that the query executes slowly. Remember that the performance will improve once you have created a Secondary index.
-
The correct query should return exactly 5 documents. You should see one playlist having the name Playlist # 5 for Morgan
-
Once you have a correct query, you should create a Secondary Index. This index will focus on the critical evaluation attribute from the query. You may elect to create a Composite Index to focus only on documents having the playlist type. Once created, repeat the prior query and note the significant improvement in performance. This query should return in less than 200ms.
-
Once you have the query working with a hard-coded value for username, replace with a named parameter. Add the parameter to the Run-Time Preferences with the value of stockadeseffusing18695
-
Test one more time to ensure the query works with the named parameter.
Querying for tracks associated to a playlist
In this next step, you will define a more complex query designed to return list of tracks found in the playlist document. Consider the following playlist document.
{
"created": "2014-11-21T23:03:22",
"id": "00011b74-12be-4e60-abbf-b1c8b9b40bfe",
"name": "Playlist # 5 for Morgan",
"owner": {
"created": 1423827257000,
"firstName": "Morgan",
"lastName": "Moreau",
"picture": {
"large": "https://randomuser.me/api/portraits/men/34.jpg",
"medium": "https://randomuser.me/api/portraits/med/men/34.jpg",
"thumbnail": "https://randomuser.me/api/portraits/thumb/men/34.jpg"
},
"title": "Mr",
"updated": "2015-08-25T10:27:56",
"username": "stockadeseffusing18695"
},
"tracks": [
"89B8A853A3BDB76276B9F52549EF6099920008DC",
"535BBDC871157C6873814F69DF1ED1B47A743908",
"0B358A6A3B31D957A7373D09549B3F8046D112AD",
"C9FE05D7BA77FF538D8CA2A95E0733AE3248DBFA",
"61E71BC154D0D57DA0297C50BF270A8783239291",
"FF6BC306B6FF006B6D6466161B5ADFAFB4457AD5",
"2438822DD350BD07C982A32D2BAD7341D3CFDDC7",
"DA7F081047B5452FF2B56F6E28336A54A2363B9B",
"34D520CF3CEEB131AFF1AFF00FC8E569E1E846C1",
"18DFA3B55EAC51B98B46B6E5E0B9812C281D2F3A",
"041EB9B0E8790098922F677A6A629E0B15FDCCCA",
"DA1D6746DB102E1121DDC3B3FC1FE795462501F9",
"89B8A853A3BDB76276B9F52549EF6099920008DC",
"1FDBCABD02D6DC51E0DD058728973759D707370E",
"ED3334952F4781016C9C5483E87A250B5FF83FE2"
],
"type": "playlist",
"updated": "2015-09-11T10:40:01",
"visibility": "PUBLIC"
}
Note the tracks attribute references an array of String values representing the trackId.
Setup
Recall in the course that there is a way of joining documents where the
right side of the join can be referenced using keys rather than an attribute,
which eliminates the need for an additional index.
In the course you saw an example of using the ON KEYS
element in conjunction with the
JOIN
element in a query.
The query you will be creating will work in a similar way, including using the concatenation
feature to build a list of keys based on these track ids.
The additional element ON KEYS ARRAY
provides a mechanism to iterate over an array of tracks.
The full expression will resemble the following.
ON KEYS ARRAY trackId FOR trackId IN playlist.tracks END
You will use this query in conjunction with the remainder of the JOIN query to fetch the track details for the list of trackIds held in the playlist. Recall that the other important part of the JOIN query is expressing what is on the LEFT and what is on the RIGHT side of the query. Finally, don’t forget you will need to add elements of the WHERE clause to limit the playlist on the LEFT side to only the playlist having the specified playlistId as well as only limiting document the LEFT side to playlist documents and documents on the RIGHT side to track documents.
Tasks
-
As with the prior lab step, take time to define the query using a hard-coded value for the playlist ID using 00011b74-12be-4e60-abbf-b1c8b9b40bfe. If successful, you should see 15 documents returned. You will notice that the query runs very slowly because there is an additional element being evaluated.
-
Take a moment to define a secondary index for the playlist id attribute.
-
Rerun the query and verify that the query performance has significantly increased.
-
Convert the query to use a named parameter for the playlist ID. Be sure to use the same Run-Time Parameters settings to define a new variable for the playlistId having a value of "00011b74-12be-4e60-abbf-b1c8b9b40bfe"
-
Re-run the query to ensure the correct results are returned.
Note If you get absolutely stuck on the query, the following is the solution query.
SELECT track
FROM playlist
INNER JOIN track ON KEYS ARRAY trackId FOR trackId IN playlist.tracks END
WHERE playlist.id = '00011b74-12be-4e60-abbf-b1c8b9b40bfe';
Note that you will likely find this query to be running slowly initially as well. Take a moment to analyze the query and identify what kind of index needs to be created. Create the index and re-run the query to verify the query performance improves.
Executing N1QL queries from the Couchbase SDK
In this final section of the lab, you will see how the queries you have defined and tested in the Query Workbench can be executed from the Couchbase SDK. At this phase, you will be making use of named parameters. While this topic was covered in the course material, you may find this page helpful in reviewing the use of named parameters in a query.
Setup
Take a moment to notice the nature of the documents returned by the queries you have defined in the Query Workbench. Notice that the results are an array where each document returned for the query will be represented as a single attribute with the associated document as the value.
{
"playlist": {
"created": "2014-11-21T23:03:22",
"id": "00011b74-12be-4e60-abbf-b1c8b9b40bfe",
"name": "Playlist # 5 for Morgan",
"owner": {
"created": 1423827257000,
"firstName": "Morgan",
"lastName": "Moreau",
"picture": {
"large": "https://randomuser.me/api/portraits/men/34.jpg",
"medium": "https://randomuser.me/api/portraits/med/men/34.jpg",
"thumbnail": "https://randomuser.me/api/portraits/thumb/men/34.jpg"
},
"title": "Mr",
"updated": "2015-08-25T10:27:56",
"username": "stockadeseffusing18695"
},
...
With documents returned in this way, it will be more difficult to process the results, especially if you intend to ultimately use the transcoders to convert to the results to a list of domain objects. One way to address this is to slightly alter the N1QL query by prefixing the SELECT portion of the query with the bucket or alias as shown below.
SELECT playlist.* FROM playlist WHERE ...`.
The result is that the results are returned as an list of matching documents as shown below.
{
"created": "2014-11-21T23:03:22",
"id": "00011b74-12be-4e60-abbf-b1c8b9b40bfe",
"name": "Playlist # 5 for Morgan",
"owner": {
"created": 1423827257000,
"firstName": "Morgan",
"lastName": "Moreau",
"picture": {
"large": "https://randomuser.me/api/portraits/men/34.jpg",
"medium": "https://randomuser.me/api/portraits/med/men/34.jpg",
"thumbnail": "https://randomuser.me/api/portraits/thumb/men/34.jpg"
},
"title": "Mr",
"updated": "2015-08-25T10:27:56",
"username": "stockadeseffusing18695"
},
Tasks
Return to your lab session page and locate the code examples for the N1QL Queries (Lab 5: Query 1 and Lab 5: Query 2) for your language of choice. Recall that to select a specific SDK for one of the languages, you can always select Return to SDKs button in the upper right corner of the lab environment. This will return you to the main language selection page where you can choose from one of the four covers language SDKs.
Once you have selected the language, use the lab step menu along the top to locate the Query lab examples.

-
Look at the first example, which executes the query for finding playlists for a given username. Further, locate an example for your preferred language and select the code to load into the code box.
-
Locate the query definition in the code and notice how the query is written using named parameters
-
Notice also how the parameters are provided by the query
-
Finally notice how the results are processed. Note that in this example, only two values are displayed from each row of the results returned.
-
Next, execute the code and observe the correlation between the code and the resulting output
-
Take some time to explore examples of the same query being executed via the other SDKs
-
-
Locate the next example, Lab 6: Query 2, designed to find tracks for a given playlist.
-
Notice again how the query is defined using named parameters
-
Also take time to observe the nuances of the different SDKs
-
Finally, execute one or more of the code examples and observe the results
-
Lab summary
In this lab, you had a chance to gain experience with a number of query related tasks in defining queries to return collections.
-
You had the chance to invoke a simple query based on one point of evaluation and one document type.
-
You had a chance to invoke a more complex query involving joins.
-
You also defined several indexes to improve query performance
-
Finally, you had a chance to run these queries from applications of various languages using the Couchbase SDK.
Upon completion of this lab, you have gained experience with the foundations of developing N1QL queries and executing using the Couchbase SDK.