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

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

  1. Take a moment to inspect a playlist document in the Classic Editor

    1. Click on Documents on the left side, locate the couchmusic2 bucket and select the Documents link associated.

    2. Select the Classic Editor link in the top right of the browser page.

    3. Use filter to specify a startkey of playlist

    4. Notice the structure of the document and especially note the owner object referenced and the username sub-element.

  2. Think about how this query would be specified if you knew what the value of username was.

Tasks

  1. Switch to the Query Editor by clicking on the Query link on the left side of the Couchbase Web interface.

  2. It might be a good idea until you are completely sure what index to define to simply create a primary index for the couchmusic2 bucket

    create primary index IDX_primary_couchmusic2 on couchmusic2

  3. 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.

  4. 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.

  5. Don’t be surprised to find that the query executes slowly. Remember that the performance will improve once you have created an index.

  6. The correct query should return exactly 5 documents. You should see one playlist having the name Playlist # 5 for Morgan

  7. 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.

  8. 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

  9. 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 'track::'||trackId FOR trackId IN playlists.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

  1. 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.

  2. Take a moment to define a secondary index for the playlist id attribute.

  3. Rerun the query and verify that the query performance has significantly increased.

  4. 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"

  5. 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 tracks
FROM couchmusic2 AS playlists
INNER JOIN couchmusic2 AS tracks ON KEYS ARRAY "track::"||trackId FOR trackId IN playlists.tracks END
WHERE playlists.id = $playlistId
    AND playlists.type= "playlist";

Implementing the queries in your Couchify Application

In this final section of the lab, you will take the queries you have defined and tested in the Query Dashboard and use them in the Couchify application using the Couchbase SDK.

Setup

Take a moment to notice the nature of the documents returned by the queries you have defined. 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.

 {
    "couchmusic2": {
      "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 the documents returned in this way, it will not be possible to map the results to their associated domain objects. There is a way to remove this prefix so that the list of results is merely a set of the documents. If you prefix the SELECT portion of the query with the bucket or alias, as select couchmusic2.*, you will see that the results have been simplified to something like the following.

 {
    "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

Processing the Playlist query

  1. Open the PlaylistRepository.cs class and locate the FindPlaylistsByUsername() method. Define a query string definition using the query you defined and adapted in the prior steps to return the playlists for a user.

  2. Implement a query on the Cluster object that takes the string query definition and the setting of the named parameter to ensure you return only the playlists for the defined username.

  3. Convert the result to a List of Playlists and return

  4. Be sure to handle anticipated exceptions as well.

Processing the Tracks query

  1. In the same PlaylistRepository.cs class, locate the _FindTracksForPlaylist() method.

  2. Using a similar approach to the prior step, define a query string that will be executed.

  3. Implement the query itself with the query string defined and the parameter passed in.

  4. Convert the result to a List of Tracks and return it

  5. Be sure to handle anticipated exceptions as well.

Verifying your implementation

  1. Run the Lab06 unit tests to verify you have correctly implemented your solutions.

  2. Be sure both tests pass

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.

  1. You had the chance to invoke a simple query based on one point of evaluation and one document type.

  2. You had a chance to invoke a more complex query involving joins.

  3. You also defined several indexes to improve query performance

  4. Finally, you had a chance to integrate these queries into the .NET application using the Couchbase SDK.

Upon completion of this lab, you have gained experience with the foundations of developing .NET applications using the Couchbase SDK. One final section will help you propel your applications to being production-ready.

results matching ""

    No results matching ""